How to View Report with Date Range Criteria

[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_EXTEND ED) grabs Information from the following tables: "SERVICE_JO B", "SERVICE_NA ME", "PARTS_VEND OR".

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

The Report's control source is the Query "SERVICE_EXTEND ED"

The Form (frm_BILLING_CO NTROL_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 miscommunicatio ns 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_EXTENDE D 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]

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).

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]

Plus fyi when I originally inserted != access changed it to =! dunno why though

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.