lulu50
asked on
date range
Hi,
this is VB access database
I can open the report but it display all the data right now but what I want is to prompt the user with a date range. so, the report will display only the date range that the user entered.
DoCmd.OpenReport "Report_1_MTD_Daily_Contai nment", acViewPreview, , ""
Thanks,
Lulu
this is VB access database
I can open the report but it display all the data right now but what I want is to prompt the user with a date range. so, the report will display only the date range that the user entered.
DoCmd.OpenReport "Report_1_MTD_Daily_Contai
Thanks,
Lulu
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
lulu,
Prompting isn't enough. You also have to include a where clause in the OpenReport method.
Prompting isn't enough. You also have to include a where clause in the OpenReport method.
pass the value entered in the input box
Dim DateRange As Date: DateRange = InputBox("Please enter a date range!", "From", Date - 1)
if Daterange & ""<>"" then
DoCmd.OpenReport "Report_1_MTD_Daily_Contai nment", acViewPreview, , "[DateField]=#" & dateRange & "#"
end if
Dim DateRange As Date: DateRange = InputBox("Please enter a date range!", "From", Date - 1)
if Daterange & ""<>"" then
DoCmd.OpenReport "Report_1_MTD_Daily_Contai
end if
<< something like this but I don't know access much >>
Which is why Pat is attempting to show you the 'best practices' for this, to make things easier on you as the developer and to provide a better user interface.
The form interface she is describing is pretty standard.
The date range input you are asking for is not. What you are asking for would either involve two parameter prompts for the start and end of the range (annoying from a user interface perspective), more complex code to parse a date range with a single prompt as you are describing, or would limit the range to only one entry (ie: your 'fixed' date()-1)
The form interface is a simple design, with simple code and is an easy to follow standard for most users.
Which is why Pat is attempting to show you the 'best practices' for this, to make things easier on you as the developer and to provide a better user interface.
The form interface she is describing is pretty standard.
The date range input you are asking for is not. What you are asking for would either involve two parameter prompts for the start and end of the range (annoying from a user interface perspective), more complex code to parse a date range with a single prompt as you are describing, or would limit the range to only one entry (ie: your 'fixed' date()-1)
The form interface is a simple design, with simple code and is an easy to follow standard for most users.
ASKER
Rey,
Thank you all for your help.
I entered 2/10/2014 than I entered 2/13/2014, I did not get any data but I do have data in that date range.
Dim DateRange As Date: DateRange = InputBox("Please enter a date range!", "From", Date - 1)
If DateRange & "" <> "" Then
DoCmd.OpenReport "Report_1_MTD_Daily_Contai nment", acViewPreview, , "[DateField]=#" & DateRange & "#"
End If
Thank you all for your help.
I entered 2/10/2014 than I entered 2/13/2014, I did not get any data but I do have data in that date range.
Dim DateRange As Date: DateRange = InputBox("Please enter a date range!", "From", Date - 1)
If DateRange & "" <> "" Then
DoCmd.OpenReport "Report_1_MTD_Daily_Contai
End If
Let me expand my example to include a range:
If you define the form controls as dates, Access will restrict them to valid dates so you only have to verify their presence. If you use unformatted text boxes, they could be anything and so you would need to verify that both dates are valid.
If you use prompts, you have to take it on faith that the user entered a valid date. Prompts are NOT the way to go. If the user doesn't enter a valid date range, THEN you would prompt.
If you define the form controls as dates, Access will restrict them to valid dates so you only have to verify their presence. If you use unformatted text boxes, they could be anything and so you would need to verify that both dates are valid.
If you use prompts, you have to take it on faith that the user entered a valid date. Prompts are NOT the way to go. If the user doesn't enter a valid date range, THEN you would prompt.
If IsDate(Me.StartDate) AND IsDate(Me.EndDate) Then
If Me.EndDate > Me.StartDate Then
Else
Msgbox "End Date must be greater than start date.",vbOKOnly
Me.EndDate.SetFocus
Exit Sub
End If
Else
Msgbox "A valid date range is required to run this report.",vbOKOnly
Me.StartDate.SetFocus
Exit Sub
End If
DoCmd.OpenReport "Report_1_MTD_Daily_Containment", acViewPreview, , "[DateField] Between #" & Me.StartDate & "# AND #" & Me.EndDate & "#"
ASKER
PartHartman
Compile error
It says Method or data member not found
Compile error
It says Method or data member not found
ASKER
it doesnt know what Me.StartDate
@lulu
<I entered 2/10/2014 than I entered 2/13/2014, I did not get any data but I do have data in that date >
if you don't want to use a form for the date entry range,
you need two inputs
Dim DateFrom As Date, DateTo As Date
DateFrom = InputBox("Please enter a date range!", "From")
DateTo = InputBox("Please enter a date range!", "To")
If DateFrom & "" <> "" And DateTo & "" <> "" Then
DoCmd.OpenReport "Report_1_MTD_Daily_Contai nment", acViewPreview, , "[DateField]>=#" & DateFrom & "# And [DateField]<=#" & DateTo & "#"
End If
<I entered 2/10/2014 than I entered 2/13/2014, I did not get any data but I do have data in that date >
if you don't want to use a form for the date entry range,
you need two inputs
Dim DateFrom As Date, DateTo As Date
DateFrom = InputBox("Please enter a date range!", "From")
DateTo = InputBox("Please enter a date range!", "To")
If DateFrom & "" <> "" And DateTo & "" <> "" Then
DoCmd.OpenReport "Report_1_MTD_Daily_Contai
End If
it doesnt know what Me.StartDateDid you define a control on the form and name it StartDate? Don't forget to specify a date format. Short Date is what most people use.
ASKER
This is what I want, but I get three input box for the date I just need two.
Dim DateFrom As Date, DateTo As Date
DateFrom = InputBox("Please enter a date range!", "From")
DateTo = InputBox("Please enter a date range!", "To")
If IsDate(DateFrom) And IsDate(DateTo) Then
If DateTo > DateFrom Then
Else
MsgBox "End Date must be greater than start date.", vbOKOnly
DateTo.SetFocus //this generate error
Exit Sub
End If
Else
MsgBox "A valid date range is required to run this report.", vbOKOnly
'DateFrom.SetFocus //this generate error
Exit Sub
End If
DoCmd.OpenReport "Report_1_MTD_Daily_Contai nment", acViewPreview, , "[DateField] Between #" & DateFrom & "# AND #" & DateTo & "#"
Dim DateFrom As Date, DateTo As Date
DateFrom = InputBox("Please enter a date range!", "From")
DateTo = InputBox("Please enter a date range!", "To")
If IsDate(DateFrom) And IsDate(DateTo) Then
If DateTo > DateFrom Then
Else
MsgBox "End Date must be greater than start date.", vbOKOnly
DateTo.SetFocus //this generate error
Exit Sub
End If
Else
MsgBox "A valid date range is required to run this report.", vbOKOnly
'DateFrom.SetFocus //this generate error
Exit Sub
End If
DoCmd.OpenReport "Report_1_MTD_Daily_Contai
not sure if you will like this, but try
Dim DateFrom As Date, DateTo As Date
Do
DateFrom = InputBox("Please enter a date range!", "From")
DateTo = InputBox("Please enter a date range!", "To")
Loop Until IsDate(DateFrom) And IsDate(DateTo) And DateTo > DateFrom
DoCmd.OpenReport "Report_1_MTD_Daily_Contai nment", acViewPreview, , "[DateField] Between #" & DateFrom & "# AND #" & DateTo & "#"
Dim DateFrom As Date, DateTo As Date
Do
DateFrom = InputBox("Please enter a date range!", "From")
DateTo = InputBox("Please enter a date range!", "To")
Loop Until IsDate(DateFrom) And IsDate(DateTo) And DateTo > DateFrom
DoCmd.OpenReport "Report_1_MTD_Daily_Contai
I'm done.
Lulu,
No points for this comment please.
What you are asking for is certainly possible, but it is NOT optimal, as you can see with the multiple parameter prompts.
Pat Hartman has gone to great lengths to show you the *best* way to accomplish this both from a user's perspective and a developer's perspective.
This sample, a 5-minute effort, includes that form-based approach, using the code she posted. It is hands-down the best way to accomplish what you are asking for, and is the approach that almost all of the regular Experts here (including Rey) would probably use in their own clients' databases.
TestDateRange.mdb
No points for this comment please.
What you are asking for is certainly possible, but it is NOT optimal, as you can see with the multiple parameter prompts.
Pat Hartman has gone to great lengths to show you the *best* way to accomplish this both from a user's perspective and a developer's perspective.
This sample, a 5-minute effort, includes that form-based approach, using the code she posted. It is hands-down the best way to accomplish what you are asking for, and is the approach that almost all of the regular Experts here (including Rey) would probably use in their own clients' databases.
TestDateRange.mdb
ASKER
I've requested that this question be closed as follows:
Accepted answer: 250 points for PatHartman's comment #a39859124
Assisted answer: 0 points for lulu50's comment #a39859108
Assisted answer: 250 points for capricorn1's comment #a39859470
for the following reason:
I am sorry, for all this confusion.
Thank you all for all your help.
Accepted answer: 250 points for PatHartman's comment #a39859124
Assisted answer: 0 points for lulu50's comment #a39859108
Assisted answer: 250 points for capricorn1's comment #a39859470
for the following reason:
I am sorry, for all this confusion.
Thank you all for all your help.
ASKER
Thank you
ASKER
once they enter the date range the report should take the value and generate the report based on the input date from the user.
something like this but I don't know access much
Dim DateRange As Date: DateRange = InputBox("Please enter a date range!", "From", Date - 1)
DoCmd.OpenReport "Report_1_MTD_Daily_Contai