Switchboard button event procedure

S

Steve

Hi,
Have a report that I would like to count 2 different parameters, both date
fields within a given date range. I can get it to work for one parameter but
when adding the second with an eithe AND, or OR neither work.
would appreciate your help.
Thanks,

Private Sub cmd_UrFasttrack_Click()
On Error GoTo Err_cmd_UrFasttrack_Click
Dim stDocName As String
Dim stCriterion As String
Dim stStartDate As Date
Dim stEndDate As Date

stCriterion = ""

If IsNull(Forms!frmRptsbyDate!StartDate) Then
MsgBox "No Start Date, using All Dates"
GoTo Print_Report
End If
stStartDate = Forms!frmRptsbyDate!StartDate
If IsNull(Forms!frmRptsbyDate!EndDate) Then
MsgBox "No End Date, using All Dates"
GoTo Print_Report
End If

stEndDate = Forms!frmRptsbyDate!EndDate
stCriterion = "(RequestDate between #" & stStartDate & "# AND #" &
stEndDate & "#)" And stCriterion = "(FastTrack between #" & stStartDate & "#
AND #" & stEndDate & "#)"


stDocName = "rptURandFastTrack2"
DoCmd.OpenReport stDocName, acPreview, , stCriterion
Print_Report:

stDocName = "rptURandFastTrack2"
DoCmd.OpenReport stDocName, acPreview, , stCriterion

Exit_cmd_UrFasttrack_Click:
Exit Sub
 
J

John Spencer

Perhaps what you want is the following:

stCriterion = "RequestDate between #" & stStartDate & "# AND #" &
stEndDate & "# AND FastTrack between #" & stStartDate & "#
AND #" & stEndDate & "#"

I would rewrite the sub

Private Sub cmd_UrFasttrack_Click()
On Error GoTo Err_cmd_UrFasttrack_Click
Dim stDocName As String
Dim stCriterion As String
Dim stStartDate As Date
Dim stEndDate As Date

stCriterion = ""
'Set criteria based on value of StartDate and EndDate
If IsNull(Forms!frmRptsbyDate!StartDate) Then
MsgBox "No Start Date, using All Dates"
ElseIf IsNull(Forms!frmRptsbyDate!EndDate) Then
MsgBox "No End Date, using All Dates"
Else
stStartDate = Forms!frmRptsbyDate!StartDate
stEndDate = Forms!frmRptsbyDate!EndDate
stCriterion = "RequestDate between #" & stStartDate & "# AND #" & _
stEndDate & "# And FastTrack between #" & stStartDate & _
"# AND #" & stEndDate & "#"
End IF

'Print the report
stDocName = "rptURandFastTrack2"
DoCmd.OpenReport stDocName, acPreview, , stCriterion

Exit_cmd_UrFasttrack_Click:

Exit Sub
Err_cmd_UrFasttrack_Click:
'Your error handling routine here
End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
S

Steve

Hi,
Thanks for the input but it delivers #error

the query behind the report is

SELECT tblRequest.Facility, tblRequest.FastTrack, tblRequest.RequestDate,
tblRequest.CertificationDate, IIf([FastTrack],1,0) AS CountFastTrack,
IIf([RequestDate],1,0) AS CountReqDate
FROM tblRequest
WHERE (((tblRequest.Facility) Is Not Null) AND ((tblRequest.FastTrack)
Between #1/1/2009# And #6/30/2009#)) OR (((tblRequest.RequestDate) Between
#1/1/2009# And #6/30/2009#))
ORDER BY tblRequest.Facility;

In the report I use count[FastTrack] for one column and count[RequestDate]
for the other
 
J

John Spencer

The query must return RequestDate and FastTrack as fields in the SELECT
clause. If it does not return those fields, then the strCriterion cannot
filter against them. You do not return FastTrack as a field in the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi,
Thanks for the input but it delivers #error

the query behind the report is

SELECT tblRequest.Facility, tblRequest.FastTrack, tblRequest.RequestDate,
tblRequest.CertificationDate, IIf([FastTrack],1,0) AS CountFastTrack,
IIf([RequestDate],1,0) AS CountReqDate
FROM tblRequest
WHERE (((tblRequest.Facility) Is Not Null) AND ((tblRequest.FastTrack)
Between #1/1/2009# And #6/30/2009#)) OR (((tblRequest.RequestDate) Between
#1/1/2009# And #6/30/2009#))
ORDER BY tblRequest.Facility;

In the report I use count[FastTrack] for one column and count[RequestDate]
for the other

John Spencer said:
Perhaps what you want is the following:

stCriterion = "RequestDate between #" & stStartDate & "# AND #" &
stEndDate & "# AND FastTrack between #" & stStartDate & "#
AND #" & stEndDate & "#"

I would rewrite the sub

Private Sub cmd_UrFasttrack_Click()
On Error GoTo Err_cmd_UrFasttrack_Click
Dim stDocName As String
Dim stCriterion As String
Dim stStartDate As Date
Dim stEndDate As Date

stCriterion = ""
'Set criteria based on value of StartDate and EndDate
If IsNull(Forms!frmRptsbyDate!StartDate) Then
MsgBox "No Start Date, using All Dates"
ElseIf IsNull(Forms!frmRptsbyDate!EndDate) Then
MsgBox "No End Date, using All Dates"
Else
stStartDate = Forms!frmRptsbyDate!StartDate
stEndDate = Forms!frmRptsbyDate!EndDate
stCriterion = "RequestDate between #" & stStartDate & "# AND #" & _
stEndDate & "# And FastTrack between #" & stStartDate & _
"# AND #" & stEndDate & "#"
End IF

'Print the report
stDocName = "rptURandFastTrack2"
DoCmd.OpenReport stDocName, acPreview, , stCriterion

Exit_cmd_UrFasttrack_Click:

Exit Sub
Err_cmd_UrFasttrack_Click:
'Your error handling routine here
End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
S

Steve

Thanks,

The first sentence of the query is:
SELECT tblRequest.Facility, tblRequest.FastTrack, tblRequest.RequestDate,
Click to expand...

should I write it another way?

John Spencer said:
The query must return RequestDate and FastTrack as fields in the SELECT
clause. If it does not return those fields, then the strCriterion cannot
filter against them. You do not return FastTrack as a field in the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi,
Thanks for the input but it delivers #error

the query behind the report is

SELECT tblRequest.Facility, tblRequest.FastTrack, tblRequest.RequestDate,
tblRequest.CertificationDate, IIf([FastTrack],1,0) AS CountFastTrack,
IIf([RequestDate],1,0) AS CountReqDate
FROM tblRequest
WHERE (((tblRequest.Facility) Is Not Null) AND ((tblRequest.FastTrack)
Between #1/1/2009# And #6/30/2009#)) OR (((tblRequest.RequestDate) Between
#1/1/2009# And #6/30/2009#))
ORDER BY tblRequest.Facility;

In the report I use count[FastTrack] for one column and count[RequestDate]
for the other

John Spencer said:
Perhaps what you want is the following:

stCriterion = "RequestDate between #" & stStartDate & "# AND #" &
stEndDate & "# AND FastTrack between #" & stStartDate & "#
AND #" & stEndDate & "#"

I would rewrite the sub

Private Sub cmd_UrFasttrack_Click()
On Error GoTo Err_cmd_UrFasttrack_Click
Dim stDocName As String
Dim stCriterion As String
Dim stStartDate As Date
Dim stEndDate As Date

stCriterion = ""
'Set criteria based on value of StartDate and EndDate
If IsNull(Forms!frmRptsbyDate!StartDate) Then
MsgBox "No Start Date, using All Dates"
ElseIf IsNull(Forms!frmRptsbyDate!EndDate) Then
MsgBox "No End Date, using All Dates"
Else
stStartDate = Forms!frmRptsbyDate!StartDate
stEndDate = Forms!frmRptsbyDate!EndDate
stCriterion = "RequestDate between #" & stStartDate & "# AND #" & _
stEndDate & "# And FastTrack between #" & stStartDate & _
"# AND #" & stEndDate & "#"
End IF

'Print the report
stDocName = "rptURandFastTrack2"
DoCmd.OpenReport stDocName, acPreview, , stCriterion

Exit_cmd_UrFasttrack_Click:

Exit Sub
Err_cmd_UrFasttrack_Click:
'Your error handling routine here
End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Steve wrote:
Hi,
Have a report that I would like to count 2 different parameters, both date
fields within a given date range. I can get it to work for one parameter but
when adding the second with an eithe AND, or OR neither work.
would appreciate your help.
Thanks,

Private Sub cmd_UrFasttrack_Click()
On Error GoTo Err_cmd_UrFasttrack_Click
Dim stDocName As String
Dim stCriterion As String
Dim stStartDate As Date
Dim stEndDate As Date

stCriterion = ""

If IsNull(Forms!frmRptsbyDate!StartDate) Then
MsgBox "No Start Date, using All Dates"
GoTo Print_Report
End If
stStartDate = Forms!frmRptsbyDate!StartDate
If IsNull(Forms!frmRptsbyDate!EndDate) Then
MsgBox "No End Date, using All Dates"
GoTo Print_Report
End If

stEndDate = Forms!frmRptsbyDate!EndDate
stCriterion = "(RequestDate between #" & stStartDate & "# AND #" &
stEndDate & "#)" And stCriterion = "(FastTrack between #" & stStartDate & "#
AND #" & stEndDate & "#)"


stDocName = "rptURandFastTrack2"
DoCmd.OpenReport stDocName, acPreview, , stCriterion
Print_Report:

stDocName = "rptURandFastTrack2"
DoCmd.OpenReport stDocName, acPreview, , stCriterion

Exit_cmd_UrFasttrack_Click:
Exit Sub
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

No, you don't need to rewrite it; I should get glasses though.

Is FastTrack a date field?

I think you want to use OR and not AND

stCriterion = "RequestDate between #" & stStartDate & "# AND #" & _
stEndDate & "# OR FastTrack between #" & stStartDate & _
"# AND #" & stEndDate & "#"

If you put that all on one line, remove the _ characters

stCriterion = "RequestDate between #" & stStartDate & "# AND #" & stEndDate &
"# OR FastTrack between #" & stStartDate & "# AND #" & stEndDate & "#"

Other than those guesses, I am stuck. Do you get any specific error messages?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

variable not defined error 2
Else Without If 8
OpenReport Where Condition with date range 6
Worked in Windows 2000 but not XP 0
Changed query, Form went Blank 2
Report VB Code 1
Cancel Open Report from a parameter form 14
OpenReport with <>WhereCondition 2

Top