Report date range form query

G

Guest

I have read multiple posts and tried various tutorials, but have been unable
to get my form named "Report Date Range" to complete the query to rerieve the
report. I can open the report by running the query, but run in to problems
with code for form. I copied this format from Allen Browne's website. I get
error message "Runtime error '3075': Syntaxerror (missing operator) in query
expression '(closing date Between #10/01/2005# and #11/01/2005#)'.
When I click on Debug the "DoCmd.OpenReport..." line is highlighted. HELP!

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 = "Contingency Report"
strField = "Closing 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
 
G

Guest

If the field name contain two names like "closing date" you need to put it in
square brackets, try this

strField = "[Closing Date]"
 
G

Guest

That corrected that problem, but when I click on the search command button
after entering my dates I get a query parameter box with the title of the
first column in my report. I don't have any query set up for this column so
why am I getting this?

Ofer said:
If the field name contain two names like "closing date" you need to put it in
square brackets, try this

strField = "[Closing Date]"

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


Dcbrown428 said:
I have read multiple posts and tried various tutorials, but have been unable
to get my form named "Report Date Range" to complete the query to rerieve the
report. I can open the report by running the query, but run in to problems
with code for form. I copied this format from Allen Browne's website. I get
error message "Runtime error '3075': Syntaxerror (missing operator) in query
expression '(closing date Between #10/01/2005# and #11/01/2005#)'.
When I click on Debug the "DoCmd.OpenReport..." line is highlighted. HELP!

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 = "Contingency Report"
strField = "Closing 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
Click to expand...
Click to expand...
 
G

Guest

What is the code behind the search button?

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


Dcbrown428 said:
That corrected that problem, but when I click on the search command button
after entering my dates I get a query parameter box with the title of the
first column in my report. I don't have any query set up for this column so
why am I getting this?

Ofer said:
If the field name contain two names like "closing date" you need to put it in
square brackets, try this

strField = "[Closing Date]"

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


Dcbrown428 said:
I have read multiple posts and tried various tutorials, but have been unable
to get my form named "Report Date Range" to complete the query to rerieve the
report. I can open the report by running the query, but run in to problems
with code for form. I copied this format from Allen Browne's website. I get
error message "Runtime error '3075': Syntaxerror (missing operator) in query
expression '(closing date Between #10/01/2005# and #11/01/2005#)'.
When I click on Debug the "DoCmd.OpenReport..." line is highlighted. HELP!

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 = "Contingency Report"
strField = "Closing 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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It's all the information I posted in this original post. You helped me
correct part of the problem regarding the code.

Ofer said:
What is the code behind the search button?

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


Dcbrown428 said:
That corrected that problem, but when I click on the search command button
after entering my dates I get a query parameter box with the title of the
first column in my report. I don't have any query set up for this column so
why am I getting this?

Ofer said:
If the field name contain two names like "closing date" you need to put it in
square brackets, try this

strField = "[Closing Date]"

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


:

I have read multiple posts and tried various tutorials, but have been unable
to get my form named "Report Date Range" to complete the query to rerieve the
report. I can open the report by running the query, but run in to problems
with code for form. I copied this format from Allen Browne's website. I get
error message "Runtime error '3075': Syntaxerror (missing operator) in query
expression '(closing date Between #10/01/2005# and #11/01/2005#)'.
When I click on Debug the "DoCmd.OpenReport..." line is highlighted. HELP!

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 = "Contingency Report"
strField = "Closing 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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

If the field in the report bound (control source) to a field that doesn't
exist in the query, it will propmpt you with this kind of message.

Run the Sql of the report, to see if you propmpt with any message, and then
check the field in the report if it exist in the SQL, if it doesn't either
remove it from the control source of the field, or add this field to the
record source of the report.

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


Dcbrown428 said:
It's all the information I posted in this original post. You helped me
correct part of the problem regarding the code.

Ofer said:
What is the code behind the search button?

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


Dcbrown428 said:
That corrected that problem, but when I click on the search command button
after entering my dates I get a query parameter box with the title of the
first column in my report. I don't have any query set up for this column so
why am I getting this?

:

If the field name contain two names like "closing date" you need to put it in
square brackets, try this

strField = "[Closing Date]"

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck


:

I have read multiple posts and tried various tutorials, but have been unable
to get my form named "Report Date Range" to complete the query to rerieve the
report. I can open the report by running the query, but run in to problems
with code for form. I copied this format from Allen Browne's website. I get
error message "Runtime error '3075': Syntaxerror (missing operator) in query
expression '(closing date Between #10/01/2005# and #11/01/2005#)'.
When I click on Debug the "DoCmd.OpenReport..." line is highlighted. HELP!

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 = "Contingency Report"
strField = "Closing 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
Click to expand...
Click to expand...
Click to expand...
 

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

Please help w/form collecting parameters for Report 1
Filter Report by date from Form 2
Code not working 5
Filter Specific Date in Access. 0
Print a multiple reports from a single button, can it be done 2
Running 2 reports in a form together 3
Startdate, Enddate 2
Adding where command or filter into code?? 8

Top