Using date ranges to call query

[xaic67]

I am trying to set up a form, in which the user can input specific date ranges to call data from a query. The form is set, and accepts the date inputs however it is not returning any data from the query. Here is the code I used to build the form:
[Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "qryDateRange1"
strField = "Date"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere]
"2 txt boxes in header, code below"
[=Forms.frmWhatDates.txtStartDate
=Forms.frmWhatDates.txtEndDate]

If I leave the "Debug.Print" statement I get an error telling me the "qryDateRange1" is misspellled or does not exist. If i take the statement out, nothing happens.
I appreciate any suggestions, thank you in advance for any help!!!

 

[randy700]

It's probably not as simple as this, but the first thing I'd try is...

1. Change Const conDateFormat = "\#mm\/dd\/yyyy\#"
to Const conDateFormat = "mm/dd/yyyy"

2. Change Forms.frmWhatDates.txtStartDate
to Forms!frmWhatDates!txtStartDate


Randy

 

[PHV]

You really have a report named qryDateRange1 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xaic67]

thanks Randy I appreciate the reply. Unfortunately the suggestion weilded no results.

 

[xaic67]

[Dim strQuery as string]
[strQuery= "qryDateRange1"]
I pulled the code form another site, and it said it could be used for a query or a report. I have tried the code both ways, neither gives results.

 

[PHV]

it said it could be used for a query or a report
The following line is for opening a Report and nothing else:
DoCmd.OpenReport strReport, acViewPreview, , strWhere

ie, strReport should contain a valid Report name.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xaic67]

PVH,
Is there a way to get the code to work for a form. i have changed everything that references "Report" to Query. In the last staement after acViewPreview I keep getting a compile error, it does not like the "strWhere". If I remove it I get a return from the query of all data in the Db, not between the date ranges. Thank you.

 

[PHV]

Is there a way to get the code to work for a form
In the VBA help have a look at the DoCmd.OpenForm method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[randy700]

As I see it, you haven't yet built your report. Why not let the report wizard help you build one, based on the query you all ready have. After getting that far, you can make any necessary design changes. Then, you should be able to open the report with the code you were trying to use....
DoCmd.OpenReport strReport, acViewPreview, , strWhere
....because you will now have a valid name to use for the strReport variable.


Randy

 

[xaic67]

Thank you gents, I was finally able to get it to run the way it is coded. I was forgetting to add the start and end date criteria to the query. So now it's all tied to my switchboard and running smooth, I appreciate all the feedback from those of you who posted. Thanks again, I am sure I will be back with yet another brain teaser here shortly.
Peace