I have reports that only display between the dates on the "Reports Date
Range" form. How do I have a button for "Month to date" and one for "All
Records"?
fredg said:I have reports that only display between the dates on the "Reports Date
Range" form. How do I have a button for "Month to date" and one for "All
Records"?
One button is all you really need.
Code the Button's Click event:
If IsNull([StartDate]) and IsNull([EndDate]) then
DoCmd.OpenReport "ReportName",acViewPreview
Else
DoCmd.OpenReport "ReportName",acViewPreview, , "[DateField] Between
#" & Me![StartDate] & "# and #" & Me![EndDate] & "#"
End If
Enter no date at all to get all the records, or both to get the date
range wanted.
You will probably want to also check to see if just one of the dates
has been entered and give the user a chance to rectify it.
I am getting a "Compile Error: Expected: end of statement" if higlights the #
only. And what does the # do? I couldn't find anything on it.
Between"#"
fredg said:I have reports that only display between the dates on the "Reports Date
Range" form. How do I have a button for "Month to date" and one for "All
Records"?
One button is all you really need.
Code the Button's Click event:
If IsNull([StartDate]) and IsNull([EndDate]) then
DoCmd.OpenReport "ReportName",acViewPreview
Else
DoCmd.OpenReport "ReportName",acViewPreview, , "[DateField] Between
#" & Me![StartDate] & "# and #" & Me![EndDate] & "#"
End If
Enter no date at all to get all the records, or both to get the date
range wanted.
You will probably want to also check to see if just one of the dates
has been entered and give the user a chance to rectify it.
fredg said:I am getting a "Compile Error: Expected: end of statement" if higlights the #
only. And what does the # do? I couldn't find anything on it.
Between"#"
fredg said:On Tue, 28 Dec 2004 13:11:03 -0800, James wrote:
I have reports that only display between the dates on the "Reports Date
Range" form. How do I have a button for "Month to date" and one for "All
Records"?
One button is all you really need.
Code the Button's Click event:
If IsNull([StartDate]) and IsNull([EndDate]) then
DoCmd.OpenReport "ReportName",acViewPreview
Else
DoCmd.OpenReport "ReportName",acViewPreview, , "[DateField] Between
#" & Me![StartDate] & "# and #" & Me![EndDate] & "#"
End If
Enter no date at all to get all the records, or both to get the date
range wanted.
You will probably want to also check to see if just one of the dates
has been entered and give the user a chance to rectify it.
The second OpenReport line, from
"DoCmd.OpenReport ... to ... & Me![EndDate] & "#"
should all be on one line.
Some how you changed Between #" to Between"#"
The # symbol tells Access that the value is a date value, not a
string.
Douglas J. Steele said:You still have too many quotes: there should not be one in front of Between.
"[DateField] Between #" & Me![StartDate] & "# and #" & Me![EndDate] & "#"
BTW, hopefully you realize that StartDate and EndDate must be in mm/dd/yyyy
format, regardless of what you've set the short date format to on your
machine.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
James said:Thank you again!
I took the one quote out and now I hav a complie error: syntax error. Here
is the current code (all on one line):
DoCmd.OpenReport "ReportName", acViewPreview, , "[DateField] "Between #" &
Me![StartDate] & "# and #" & Me![EndDate] & "#"
What's wrong and why don't I need the # around [DateField]?
James said:Thanks for all of your help!
Now there are no errors, but the report opens up blank if there is no dates
in the [begindate] and [enddate] and shows the data if there are dates.
Below is all of the code:
----------start of code--------------
Private Sub Preview_Click()
If IsNull([BeginDate]) And IsNull([EndDate]) Then
DoCmd.OpenReport "Specific Employee", acViewPreview
Else
If [BeginDate] > [EndDate] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "BeginDate"
Else
DoCmd.OpenReport "Specific Employee", acViewPreview, ,
"[CheckInDate] Between #" & Me![BeginDate] & "# and #" & Me![EndDate] & "#"
Me.Visible = False
End If
End If
End Sub
---------end of code-----------
Douglas J. Steele said:You still have too many quotes: there should not be one in front of Between.
"[DateField] Between #" & Me![StartDate] & "# and #" & Me![EndDate] & "#"
BTW, hopefully you realize that StartDate and EndDate must be in mm/dd/yyyy
format, regardless of what you've set the short date format to on your
machine.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
James said:Thank you again!
I took the one quote out and now I hav a complie error: syntax error. Here
is the current code (all on one line):
DoCmd.OpenReport "ReportName", acViewPreview, , "[DateField] "Between #" &
Me![StartDate] & "# and #" & Me![EndDate] & "#"
What's wrong and why don't I need the # around [DateField]?