How can I get this parameter queryVBA code to prompt for another optio 2

[Technyc2003]

I've got a preview report button on my form that runs a query that asks me to Enter a Start and End Date, however, I'd also like it to ask me to enter a Team Number but I don't know how to write the code in my code below.

Private Sub PreviewRpt_Click()
Dim strWhere As String
strWhere = "1 = 1 "
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " And [PsychoSocialDate] >=#" & _
Me.txtStartDate & "# "
End If

If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " And [PsychoSocialDate] <#" & _
Me.txtEndDate & "# "
End If

'other controls
DoCmd.OpenReport "rptPsychoServDates", acViewPreview, , strWhere
End Sub

 

[JeremyNYC]

First off, nice use of the 1 = 1 so that there's always something in your WHERE clause. I usually put " AND " at the start of each element that may end up in the WHERE clause, chop off the first five characters, and then only use the WHERE clause if there's anything left. Your way is a lot less work, though it probably slows down the sql, as each record has to be tested. In any case, it's always good to learn a new method, so thanks.

Next, why not just put a text box on your form for the Team Number? Then the code would be the same as for the other text boxes, except you wouldn't need the #s. If you con't want to do that, you could just use an input box to gather the data. Check the help files for how to use one--it's just about the same as a message box, but you use it to gather data from the user.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Technyc2003]

This is what I had before and it didn't work but I'm going to try and remove the #'s.

If Not IsNull(Me.txtTeam) Then
strWhere = strWhere & " And [Team] >=#" & _
Me.txtTeam & "# "
End If

 

[vbajock]

If Not IsNull(Me.txtTeam) Then
strWhere = strWhere & " And [Team] >=#" & _
Me.txtTeam & "# "
End If
should be
If Not IsNull(Me.txtTeam) Then
strWhere = strWhere & " And [Team] >= '" & _
Me.txtTeam & "' "
End If

 

[vbajock]

If team number is a numeric value, don't put in the single quotes

 

[Technyc2003]

Here's the entire code. The team field in the table is a number field. However on the pop-up form I created a text field called Enter Team #: along with Start Date: and End Date:

-----
Private Sub PreviewRpt_Click()
Dim strWhere As String

If Not IsNull(Me.txtTeam) Then
strWhere = strWhere & " And [Team] >= " & _
Me.txtTeam & " "
End If

strWhere = "1 = 1 "
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " And [PsychoSocialDate] >=#" & _
Me.txtStartDate & "# "
End If

If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " And [PsychoSocialDate] <#" & _
Me.txtEndDate & "# "
End If

'other controls
DoCmd.OpenReport "rptPsychoServDates", acViewPreview, , strWhere
End Sub

--------
The report pops up but the problem is that when I preview the report in my team colum it's showing all the teams instead of the number that I inputted in the txtform field on my pop-up reports form. All I wanted it to do is allow me to enter a team number the start & end date and there's my report. MS Access can sure grow more grays in my hair.

 

[vbajock]

Dim strWhere As String
dim teamno integer
dim bdate as date
dim edate as date

teamno=forms!myform!TeamnoField
bdate=forms!myform!bdate
edate=forms!myform!edate

if isnull(forms!myform!TeamnoField) then
msgbox "You didn't enter blah blah
Exit sub
else
teamno=forms!myform!TeamnoField

endif

if isnull(forms!myform!bdate then
msgbox "You didn't enter blah blah
Exit Sub
else
bdate=form!myform!bdate

endif

if isnull(forms!myform!edate then
msgbox "You didn't enter blah blah
exit sub
else
edate=form!myform!edate
endif

strwhere ="[TEAM]="+str$(teamno)+" AND [PsychoSocialDate]Between #"+format(Me.txtStartDate,"SHORT DATE")+"# AND #"+format(Me.txtStartDate,"SHORT DATE")+"#"

DoCmd.OpenReport "rptPsychoServDates", acViewPreview, , strWhere

Also, you can work up the Where clause of your sql on the query grid, view the resulting SQL, then cut and paste it into your code.

 

[Technyc2003]

Okay, I'm a little lost. I decided to use your code after careful editing and when I run the program I see my report but there's no information in it. I'm not sure what exactly I'm suppose to look at in either the code or the SQL of the query. Here's the modified code for the Preview Report button:

--
Private Sub PreviewRpt_Click()
Dim strWhere As String
Dim txtTeam As Integer
Dim txtStartDate As Date
Dim txtEndDate As Date

txtTeam = (Forms!frmGetDates!txtTeam)
txtStartDate = Forms!frmGetDates!txtStartDate
txtEndDate = Forms!frmGetDates!txtEndDate

If IsNull(Forms!frmGetDates!txtTeam) Then
MsgBox "You didn't enter a team number, try again"
Exit Sub
Else
txtTeam = Forms!frmGetDates!txtTeam

End If

If IsNull(Forms!frmGetDates!txtStartDate) Then
MsgBox "You didn't enter a start date, try again"
Exit Sub
Else
txtStartDate = Forms!frmGetDates!txtStartDate

End If

If IsNull(Forms!frmGetDates!txtEndDate) Then
MsgBox "You didn't enter an end date, try again"
Exit Sub
Else
txtEndDate = Forms!frmGetDates!txtEndDate
End If

strWhere = "[Team]=" + Str$(txtTeam) + " AND [PsychoSocialDate]Between #" + Format(Me.txtStartDate, "SHORT DATE") + "# AND #" + Format(Me.txtStartDate, "SHORT DATE") + "#"
DoCmd.OpenReport "rptPsychoServDates", acViewPreview, , strWhere

End Sub

--------
Here's the SQL of query that runs the report:

SELECT TblMain.PatientID, TblMain.LastName, TblMain.FirstName, TblMain.AdmissionDate, TblMain.PsychoSocialDate, TblMain.ServicePlanDate, TblMain.AssistantDirector, TblMain.Team
FROM TblMain
ORDER BY TblMain.LastName;



Thanks for all your help.

 

[JeremyNYC]

strWhere = "[Team]=" + Str$(txtTeam) + " AND [PsychoSocialDate]Between #" + Format(Me.txtStartDate, "SHORT DATE") + "# AND #" + Format(Me.txtStartDate, "SHORT DATE") + "#"

Put a space before the word "between" and get rid of the format commands. Then, copy the sql for the report into the sql window of a new query, and then copy this at the end of that. Test to see if you get any records. If not, switch to design view and see what needs to be tweaked.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[vbajock]

Between #" + Format(Me.txtStartDate, "SHORT DATE") + "# AND #" + Format(Me.txtStartDate, "SHORT DATE") + "#"

Should be

Between #" + Format(Me.txtStartDate, "SHORT DATE") + "# AND #" + Format(Me.txtEndDate, "SHORT DATE") + "#"

If you want to see how the computer is interpreting your sql string:


strWhere = "[Team]=" + Str$(txtTeam) + " AND [PsychoSocialDate]Between #" + Format(Me.txtStartDate, "SHORT DATE") + "# AND #" + Format(Me.txtStartDate, "SHORT DATE") + "#"
STOP

DoCmd.OpenReport "rptPsychoServDates", acViewPreview, , strWhere
when it hits the stop, open the immediate window and type
? strWhere

When you are finished debugging, remove the stop

 

[Technyc2003]

Hey thanks a lot guys, I've got the code working and I see the data I'm suppose to be getting. I even created this code on a more complex report and I got it working. Now I'm just going to go ahead and create my actualy print report buttons and take it from here. I don't know how to give stars but I think this thread deserves it because folks like me in the non-profit field need these types of reports. Thanks again. Give yourselves a hand of applause.

Rob

 

[Technyc2003]

Sorry, I had to bring this thread up but this project is near completion but ran into a program with the code below. Supposedly, if I don't enter a Team# in it's field and enter the dates I'm supposed to get the message of, "You didn't enter a Team No, try again." Instead I get the message of Run-time Error 94, Invalid use of null. It prompts to click End or Debug. Once I click Debug the following line is highlited yellow. Can this be fixed?

---------------------------------
TeamNo = Forms!FrmGetDates!TeamNo
---------------------------------

Here's the final code:

Private Sub Command41_Click()
Dim strWhere As String
Dim TeamNo As Integer
Dim txtFrom As Date
Dim txtTo As Date

TeamNo = Forms!FrmGetDates!TeamNo
txtFrom = Forms!FrmGetDates!txtFrom
txtTo = Forms!FrmGetDates!txtTo

If IsNull(Forms!FrmGetDates!TeamNo) Then
MsgBox "You didn't enter a team number, try again"
Exit Sub
Else
TeamNo = Forms!FrmGetDates!TeamNo

End If

If IsNull(Forms!FrmGetDates!txtFrom) Then
MsgBox "You didn't enter a start date, try again"
Exit Sub
Else
txtFrom = Forms!FrmGetDates!txtFrom

End If

If IsNull(Forms!FrmGetDates!txtTo) Then
MsgBox "You didn't enter an end date, try again"
Exit Sub
Else
txtTo = Forms!FrmGetDates!txtTo
End If

strWhere = "[Team]=" + Str$(TeamNo) + " AND [YNextDue] Between #" + Format(Me.txtFrom, "SHORT DATE") + "# AND #" + Format(Me.txtTo, "SHORT DATE") + "#"
DoCmd.OpenReport "rptGetDueDates", acViewPreview, , strWhere
DoCmd.Close acForm, Me.Name

End Sub

 

[Technyc2003]

I figured it out, I changed the Dim TeamNo As Integer

to Dim TeamNo As Variant

and now it works the way it should. Thanks to Microsoft Online Help. Thanks again peeps.