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
 
Do.Cmd OpenReport rptName, acViewPreview, , Criteria

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
 
 DoCmd.OpenReport rptName, acViewPreview, , Criteria
 
DoCmd.Maximize

[TheSmileyCoder]

What is the controlsource of the report? If its a query, please provide the SQL.
Do.Maximize
should also be:
Docmd.Maximize

[jhonnyboy]

Here is how these forms are connected.

Table (SERVICE_JOB) -> Form(frm_BILLING_CONTROL_RANGE) -> Report(rpt_Billing_Control)

Table has all fields for report including DATE_JOB

I hope this helps.

[NeoPa]

@jhonnyboy
In your posted code (good idea posting that by the way) your line #9 prints the Criteria value. Could you post this value too please.

[NeoPa]

@jhonnyboy
It doesn't work like that I'm afraid. The form only invokes the report. There is no data connection between the two objects. Your report is bound to a data source just as your form is. Please post this value.

[jhonnyboy]

We'll by the looks of it the report is gathering all the information from the Table SERVICE_JOB.

Line #9 results in: [DATE_JOB] BETWEEN #01/01/10# AND #03/10/10#

[NeoPa]

@jhonnyboy
If you're not sure, look at the value in the RecordSource property of the report. There should be no need for by the looks of it answers.
@jhonnyboy
That's fine. That would cover the dates 1st Jan 2010 to 10th Mar 2010. Just so it's clear. Syntax is fine though.

[jhonnyboy]

Ok Found the Report Control Source.

Control Source is "SERVICE_EXTENDED" a query which grabs the info from the table "SERVICE_JOB" as said before.

[topher23]

Wow, I've been reading this post to see if I could help at all and I'm just as confused as Smiley and NeoPa as to what your problem could be. Is there any way you could post a copy of your database for evaluation?

[jhonnyboy]

Not that I know of bud, but I'll take any and all suggestions :)

[TheSmileyCoder]

As allready asked once before post the SQL of the reports Recordsource.

Open the Query itself, and switch to SQL view, copy the SQL string, and paste it here.

[jhonnyboy]

This is what I got from going to the QUERY > VIEW > SHOW PANES > SQL TABLE VIEW

SELECT     TOP 100 PERCENT dbo.SERVICE_JOB.SERVICE_ID, dbo.SERVICE_JOB.SERVICE_NO, dbo.SERVICE_JOB.JOB_REPORT_NO, 
                      dbo.SERVICE_JOB.SERVICE_NAME_ID, dbo.SERVICE_NAME.SERVICE_NAME, dbo.SERVICE_JOB.DATE_JOB, dbo.SERVICE_JOB.ACCOUNTNO, 
                      dbo.SERVICE_JOB.CUSTOMER_NAME, dbo.SERVICE_JOB.ADDRESS1, dbo.SERVICE_JOB.ADDRESS2, dbo.SERVICE_JOB.ADDRESS3, 
                      dbo.SERVICE_JOB.SHIPTO1, dbo.SERVICE_JOB.SHIPTO2, dbo.SERVICE_JOB.SHIPTO3, dbo.SERVICE_JOB.SHIPTO4, dbo.SERVICE_JOB.FAX, 
                      dbo.SERVICE_JOB.PHONE, dbo.SERVICE_JOB.TYPE_AGREEMENT, dbo.SERVICE_JOB.REQUISITION_NO, dbo.SERVICE_JOB.PO, 
                      dbo.SERVICE_JOB.BID_CONTRACT_NO, dbo.SERVICE_JOB.LABOR_HOURS, dbo.SERVICE_JOB.LABOR_HOURS_WARRANTY, 
                      dbo.SERVICE_JOB.LABOR_RATE, dbo.SERVICE_JOB.ESTIMATE_HOURS, dbo.SERVICE_JOB.ESTIMATE_RATE, dbo.SERVICE_JOB.TRAVEL_TIME, 
                      dbo.SERVICE_JOB.FREIGHT_CHARGE_DESC, dbo.SERVICE_JOB.FREIGHT_CHARGE_AMOUNT, dbo.SERVICE_JOB.EQUIP_BRAND, 
                      dbo.PARTS_VENDOR.VENDORNAME, dbo.SERVICE_JOB.EQUIP_MODEL, dbo.SERVICE_JOB.EQUIP_SERIAL_NO, 
                      dbo.SERVICE_JOB.CONTACT_PERSON, dbo.SERVICE_JOB.CONTACT_DEAR, dbo.SERVICE_JOB.TYPE_PAYMENT, 
                      dbo.SERVICE_JOB.SERVICE_AGREEMENT_EXP_DATE, dbo.SERVICE_JOB.SALESMAN, dbo.SERVICE_JOB.AUTH_PERSON_NAME, 
                      dbo.SERVICE_JOB.AUTH_PERSON_POSITION, dbo.SERVICE_JOB.WARRANTY_CLAIM_NO, dbo.SERVICE_JOB.TAXABLE, 
                      dbo.SERVICE_JOB.TAX_EXEMPT_NO, dbo.SERVICE_JOB.SPECIAL_INSTRUCCIONS_BILLING, dbo.SERVICE_JOB.CUSTOMER_PO_NO, 
                      dbo.SERVICE_JOB.BILLING_CONTROLL, dbo.SERVICE_JOB.INVOICE_DATE, dbo.SERVICE_JOB.INVOICE_NO, dbo.SERVICE_JOB.INVOICE_AMOUNT, 
                      dbo.SERVICE_JOB.REPAIR_STATUS, dbo.PARTS_VENDOR.SHORTNAME, dbo.SERVICE_JOB.PARTS_PO
FROM         dbo.SERVICE_JOB LEFT OUTER JOIN
                      dbo.SERVICE_NAME ON dbo.SERVICE_JOB.SERVICE_NAME_ID = dbo.SERVICE_NAME.SERVICE_NAME_ID LEFT OUTER JOIN
                      dbo.PARTS_VENDOR ON dbo.SERVICE_JOB.EQUIP_BRAND = dbo.PARTS_VENDOR.VENDORID
ORDER BY dbo.SERVICE_JOB.BILLING_CONTROLL DESC

[TheSmileyCoder]

Im shooting in the dark here, but what happens if you change the query from saying
SELECT TOP 100 PERCENT ..........
to simply saying
SELECT .......

And then run the code?

Are you able to open and print the report in question normally?

[NeoPa]

@jhonnyboy
Is this latest SQL what you're referring to? Because if this is equivalent to [SERVICE_JOB] in your mind then you're likely to have problems.

This looks very much like the SQL you would get created automatically from SQL Server. Do you have any information as to where it came from?

As a first step, I'd suggest trying to run the query exactly as it is. Not by running the report. Just running the query on its own. Tell us what happens when you try that and we may be able to make some progress with you.

[jhonnyboy]

@NeoPa

When I run the query it makes a new table called SERVICE_JOB with all the contents inside the query that it's grabbing from other tables.

As for the SQL view this database doesn't have one as for my knowledge. Below is the Query shown w/ the SQL view missing, the query run and the error produced from the frm_BILLING_CONTROL_RANGE form and the report control source.

SERVICE_EXTENDED Query

SERVICE_EXTENDED Query RUN

frm_BILLING_CONTROL_RANGE

rpt_Billing_Control Control Source

[jhonnyboy]

I just finished trying to program the criteria into the query but it gives me the following error.

Hope all this info helps.

[NeoPa]

JhonnyBoy,

I'm really struggling to piece your responses together into a thread that makes sense. Some of your posts are clearly thought out and effort has obviously gone into getting the response to show as much informatoin as possible, yet the answers don't seem to match the questions. Some of the info is useful anyway of course, but it's a hard job trying to work out where I am when I have no direct access to what you're talking about and your answers don't match the questions.

You say when you run the query it produces data in a table (SERVICE_JOB). As you can probably see, the query itself (whose SQL you posted in post #26) is a simple SELECT query, so creating, or even adding data into, a table is not something it can do without your doing something to change it.

Let's take a step back and focus on the actual questions asked. Conversations such as these are inherently delicate, by which I mean that it is very easy to introduce great confusion if the proper flow is compromised in any way. I need to know that what you're saying can be understood within the defined context, bearing in mind I have no opportunity to do any sanity-checking on the data (no access to your project). I hope this makes sense.

[jhonnyboy]

Neopa,

Here is a simple structure of how this program is printing the reports. My apologies for any confusion. I am a new Network Administrator in a company with very little to no documentation and no one to ask. Let's start on a fresh page.

Program Objective: Make a form with two text boxes in date forms so when the report outputs with these date ranges only.

---------------------------------------------------

Below is what i know about this program (this has changed as i have learned new things about the program and have progressed since day one).

The Query(SERVICE_EXTENDED) grabs Information from the following tables: "SERVICE_JOB", "SERVICE_NAME", "PARTS_VENDOR".

When this query runs as you can see from post #29 it displays a table(named SERVICE_EXTENDED) with all the information grabbed from the other tables.

The Report's control source is the Query "SERVICE_EXTENDED"

The Form (frm_BILLING_CONTROL_RANGE) has two text boxes and a button. The users enters the dates, clicks the button and the report comes up but only with the date ranges the user entered. Inside the button's event procedure is the vb code we have been working on. The current code can be seen in post #29 third picture attached.

Note: I have also tried to enter criteria directly into the query as seen in post #30 yet this didn't seem to work so I deleted the code.

With the knowledge I have gained with you guys in the past couple of days I have made a quick sketch of how I think this program works.

Thanks again for ALL your hard work. I know this is a mess, but I also see it that way. I am asking for help at the same time I am learning how this all works and not knowing access syntax doesn't help much either. Thank you again, I really appreciate this!

[NeoPa]

Jhonny,

In view of the obvious effort you've put into this I will certainly try to give more help. A couple of notes though for future reference :

1. It is so important, and so much easier all round, when communicating to keep responding directly to previous communications. The most useful information in the world is rendered almost entirely useless when there is no context within which to make sense of it. I don't mean to flog a dead horse here, but it is so important that it bears repeating (It would certainly save you a lot of effort). Reviewing the thread it appears that these miscommunications may well be simply explained by the terminology being new to you. You've clearly made every effort to co-operate it seems, so I don't want you to go away thinking I'm criticising you. Just trying to offer some guidance is all.
2. A SELECT query (or view in SQL Server terms, and maybe in Access 2007 too - I don't use it so I don't know it well) can display its results in tabular format (generally does in fact). This should not be confused with a table. A table is a database object which contains data and shows it in tabular form. A View contains no data. It merely pulls data from sources and displays it. I think this may have caused some confusion earlier. I now understand what you were trying to say.
3. Viewing the SQL (and making it available to be copied if required and pasted in here) can be done quite simply by clicking on the button - SQL - found just below the obe of Adobe PDF in your screenshot labelled SERVICE_EXTENDED Query (from post #29).
4. Entirely unimportant in the project, but as an FYI Control has only a single 'L'. It looks like your recently created objects are probably spelled correctly but possibly some of the old ones weren't. I say unimportant, but actually, in as much as it may cause someone to spell an object name incorrectly, it may cause you some problems down the line.

Now I can see all the information available, I find it hard to conceive of why you're getting an error on this. It was on line #7 of your code I can see, but I don't see a problem, and I checked that the data in the field seemed (at least) to be valid date data. Did you have anything for an error message more than what you posted (Runtime 30025 SQL Error)?

[jhonnyboy]

Neopa,

Thank you for all the help and explanations. Number two of your explanations helped clear my confusion with that. The only error I am getting is the Runtime 30025 SQL Error.

[jhonnyboy]

We'll I have good news for the whole team and myself. The program is working as to specifications now. I would like to thank everyone who has not given up and has helped me to the end and a Special thanks to Neopa and Smiley thank you for all your help!

Resolution: Changed the # signs to '

After this experience I have ordered 2 Access books to learn more about this program and the syntax. Thanks again and I wish you all the best.

Best Answer given to Neopa. Thank you for never quitting and helping me to the end.

[TheSmileyCoder]

Your welcome.
Im guessing it has some thing to do with how the recordset is returned from the SQL server (if that is what you are using as backend)

I can also recommend "VBA for dummies", its a very good starting book.

[NeoPa]

Ah. This is a very important point, and indicates to me that the data you're dealing with is actually string (text) data rather than Date/Time data. It's always difficult to tell them apart unless actually checking the definition. This goes to the heart of what is in the link I posted in post #12 (Literal DateTimes and Their Delimiters (#)). Another, related, article is Quotes (') and Double-Quotes (") - Where and When to use them.

If you think about it, a date shown as mm/dd/yyyy is identical on the page to a string with characters that fit that format. "11/25/2010" is exactly the same as the text displayed for #11/25/2010#. Consider what would be seen if you tried to format the different values though :

Format("11/25/2010","d mmm yyyy") = "11/25/2010"
Format(#11/25/2010#,"d mmm yyyy") = "25 Nov 2010"

This whole issue causes even more problems in Excel, but care is still needed in Access. As a general rule of thumb, I recommend using the Date/Time field type for storing dates where possible.

I'm very pleased you were able to make progress with some help by the way. Good for you :)

[jhonnyboy]

Ok I'm back!

It's all working now, i just have to filter it out with one more thing.

In the same table that contains [DATE_JOB] there is a field named [BILLING_CONTROL].

Billing Control can either have a billing control number or "N/A"

Objective is to display all reports within the date range supplied by the user(done) and only the ones that have a billing control number.

Would really appreciate any help.

I tried adding it to the criteria like...

Do.Cmd OpenReport rptName, acViewPreview, , rptCriteria AND "Billing_Control =! N/A"

but of course that gave me an error :D

[NeoPa]

Ah. That would be a problem. You are saying to do the boolean AND (not & - which would be string concatenation) between two different strings. Quite different from concatenating the extra string to your existing criteria string.

You would want something more like :

rptCriteria = rptCriteria & " AND ([Billing_Control]<>'N/A')"
Do.Cmd OpenReport rptName, acViewPreview, , rptCriteria

[jhonnyboy]

Thanks again for the help Neopa. Worked like a charm :)

btw I forgot what the <> means do you mind explaining that to me?

[NeoPa]

It means Not Equal To. != probably works as well, but I doubt =! would.

Essentially it covers both Less Than (<) and Greater Than (>), but not Equal To (=). Not Equal To == (Less Than or Greater Than).

[jhonnyboy]

@NeoPa
Thanks that's what I thought but wanted to make sure. Plus fyi when I originally inserted != access changed it to =! dunno why though, but all is working great. Thanks again!

[NeoPa]

@jhonnyboy
Nor do I. That's very strange, but I can't be too surprised. It always inserts double-quotes (") around string literals in SQL when the standard is single-quotes ('). You just have to expect strange behaviour from Access sometimes ;)

[jhonnyboy]

Edit: Nvm Was fixed.