How to View Report with Date Range Criteria

[jhonnyboy]

Hello everyone I currently have a form w/ two text boxes so that the user may enter the dates they wish to display in the report.

The report consists of multiple clients per page and the date that they purchased an order.

What I have under the button sub procedure is the following:

Dim rptName as String
Dim Criteria as String

rptName = "Billing"
Criteria = "[DATE_JOB] BETWEEN "& Me.tbStartingDate & "AND" & Me.tbEndingDate
[B]
Do.Cmd OpenReport rptName, acViewPreview, , Criteria[/B]

Error comes from bold line when the user clicks the button. Runtime 30025 SQL Error. I'm guessing this is SQL code and not access? This table is not linked with any SQL Db. Can anyone help me out with this and get a better understanding or translate the above code to Vb Access? Thanks!

Quick Notes:
DATE_JOB: reffers to date text field in report
tbStaringDate: text box in the current form
tbEndingDate: text box in the current form

[yarbrough40]

try putting spaces in your criteria string surrounding "AND" like so...

Criteria = "[DATE_JOB] BETWEEN "& Me.tbStartingDate & " AND " & Me.tbEndingDate

the error you are getting is telling you that your sql string is not correctly formatted. this criteria that you wrote gets translated into sql code by the Access Jet engine. read up and learn how to write simple sql statements - it will vault you forward.

[TheSmileyCoder]

When you pass date literals like that, they must be enclosed in date signs # in the same way you enclose string literals with double quotes.

Criteria = "[DATE_JOB] BETWEEN #"& Me.tbStartingDate & "# AND #" & Me.tbEndingDate & "#"

I also noticed your criteria string actually has no spaces in the "AND" (should be " AND "). Its a mistake I make myself some times. The best way to debug something like this is to
A) Put a debug.print Criteria
B) OR put a msgBox Criteria
in the line before the docmd.

[jhonnyboy]

Thank you all for your replies. I have used the syntax TheSmileyOne has provided and still get the runtime error. Runtime Error 30025: Invalid SQL Statement. Check filter form record source.

It highlights the same line
Do.Cmd OpenReport rptName, acViewPreview, , Criteria

I have no clue as to what to do here. Any help would be much appreciated. Thanks again everyone!

[TheSmileyCoder]

The best way to debug something like this is to
A) Put a debug.print Criteria
B) OR put a msgBox Criteria
in the line before the docmd.

Then tell us what the debug.print statement tells you.

[jhonnyboy]

I placed debug.print before the Do.cmd but I don't get any popups or anything different. I still get the same run time error and highlighted syntax.

[TheSmileyCoder]

Debug.print will print a statement to your Immediate window (which is part of the VBA window). If you do not see it, press Ctrl-G to open it. It is extremely usefull when debugging stuff.

[jhonnyboy]

Don't see anything :/ Pressed Control+G the Immediate Window opened empty.

[TheSmileyCoder]

Dim rptName as String
Dim Criteria as String

rptName = "Billing"
Criteria = "[DATE_JOB] BETWEEN "& Me.tbStartingDate & "AND" & Me.tbEndingDate
debug.print Criteria
Do.Cmd OpenReport rptName, acViewPreview, , Criteria

Then run the code, and view the immediate window.

[jhonnyboy]

Immediate Print: [DATE_JOB] BETWEEN 01/01/10 AND 03/10/10

Date Job is the field on the report which displays the dates.

[TheSmileyCoder]

So its quite clear then that you did NOT use the date literal delimiters # like I told you to in post 3.

Again....

What your telling access to do is to compare dates and doubles.
01/01/10=0.1

To make sure Access understands that 01/01/10 is a date, you must enclose it in # such as: #01/01/10#
To make it happen in your code you would do (as allready displayed in post 3)

Criteria = "[DATE_JOB] BETWEEN #" & Me.tbStartingDate & "# AND #" & Me.tbEndingDate & "#"

[NeoPa]

Smiley is right on the button.

There is a little more to it though for perfectly portable code. See Literal DateTimes and Their Delimiters (#) for full explanations. Dates should be formatted explicitly in SQL format if no other steps have been taken to ensure there is no possibilty of ambiguity. This is particularly true of areas outside of the USA (where the default short date format is the same as the SQL standard format).

[jhonnyboy]

When i entered your syntax I get the exact same error only now with the # signs:

Immediate Print: [DATE_JOB] BETWEEN #01/01/10# AND #03/10/10#

[TheSmileyCoder]

Guess I was so focused on the errors your were making in the date syntax I didn't notice you were writing:

Do.Cmd OpenReport rptName, acViewPreview, , Criteria

Which should be:

DoCmd.OpenReport rptName, acViewPreview, , Criteria

[jhonnyboy]

Updated to DoCmd.Open Report yet still get the same error. Thanks again for all the help guys I really appreciate it. S.O.S. lol Below is the full current code in the program:

Private Sub cmdBillingPreview_Click()

Dim rptName as String
Dim Criteria as String

rptName = "rpt_Billing_Control"
Criteria = "[DATE_JOB] BETWEEN #" & Me.tbStartingDate & "# AND #" & Me.tbEndingDate & "#"

Debug.Print Criteria

[B]DoCmd.OpenReport rptName, acViewPreview, , Criteria[/B]

DoCmd.Maximize