A query based upon a date

[Guest_imported]

Hi,

Can someone please help before I pull all my hair out... I've been trying (for the the past week) to have a query based upon a date. There's just one table with this particular field Indexed and with Duplicates allowed. The purpose of this is then to allow me to formulat various reports based solely upon the "between... and..." of a particular date or between one date and another.

I've tried just about every form of expression I can think of but I either get nothing or I get everything and the thing's driving me bonkers.

I await to shower my sincere thanks upon you...etc, etc.

 

[allanon]

Hmmm.... without you actually posting the query there is little that I can suggest other than ensure that your dates are surrounded by "#". This lets access know that it is dealing with dates.

 

[BanditLV]

I concur... If you're writing a query, you need to use the following expression in the criteria:

Between #01/01/02# and #05/16/02#

- or -

> #01/01/02#

- or -

Between [BegDate] and [EndDate]

 

[Guest_imported]

Hi and thanks for your reply...

I've tried it with and without the "#". I'm trying to creat a query from from a single table and a single field within that table. I want to creat a query that will enable me to create reports based on, say: Between [Start Date] And [End Date] or perhaps just one date... (Understanding that I will, of course, have to create another query for this.)

Is it possible to do this or should I shoot myself?

Thanks...

 

[Guest_imported]

Many thanks for taking the trouble to reply,

I have tried all of these suggestions, however, I still get all or nothing by way of the results of my query.

I just tried the following: Between Forms![TestForm]![FromDate] And Forms![TestForm]![ToDate] Or Forms![TestForm]![ToDate] Is Null

If I enter no date in the form then I get get all data listed in the query, if I enter any "FromDate" "ToDate" I get nothing.

I'm at a loss as to why.

 

[allanon]

What version of Access. Post the query.

In the open event of the report I would do something like this:

DIM strSQL as string

strSQL = "SELECT * FROM TABLE "

IF Forms![TestForm]![ToDate] Is NOT Null THEN
strSQL = "WHERE TABLE.DATE BETWEEN #"
strSQL = strSQL & Forms![TestForm]![FromDate] & "# AND #"
strSQL = strSQL & Forms![TestForm]![ToDate] & "#"
END IF

me.recordsource = strSQL

This should work or be very close. Let me know how it goes.

 

[Guest_imported]

You're a star... You will certainly be if it works. I'll give it a bash.

I'm using Access 2002

I'll let you know how it goes.

 

[Guest_imported]

I am already in your debt... But where do I put the SQL command (and do I use all of it: DIM right through to me.recordsource=strSQL ?)

Thanks...

 

[allanon2]

Go to the report's design time properties (right click top left corner of report at design time) Click on the event tab and then select the On Open event. Press the '...' at the right and then select Code Builder. Place the code that I gave you in there. (Yup all of it) You will have to change the table name and field name but the rest should work fine.

 

[Guest_imported]

Hi and thanks for that... Did just as you advised. Now, when I attempt to open the report I get a "Run-time Error '424' Object Required".

I've checked the names of fields and reports, ok. Also checked that the control button in the field runs the SQL you sent me. (In fact I tried it before and after changing the control button details.) Silly question really but am I stupid? It's a fairly large database and is complete all but this little problem. I just can't get my head around this. I've also read (searched) the Access 2002 Bible and Access 2002 inside-out, both cover to cover. Whilst I always try to steer well clear of writing raw SQL I just cannot get my head around this problem. I said above that I have been trying to sort this problem now for the past week. That is true... from start till finnish - 08:00 to 23:00 for the past seven days. (Now that's not being very productive!!) You probably know the problem; haven't got the time to take a back seat, working on one particular problem so intensely for such a long period of time, sometimes you can't see the wood for the trees. I've actually started to wake-up 4 or 5 times a night thinking of this problem. heLP!!!

I'm really very grateful for all your help so far and would like to know where I can sign-up!

Many thanks, again.

 

[Guest_imported]

I've tried everything all over again and it still doesn't work...

heLP

 

[allanon2]

I need you to post the code in the button click event on the form and the code in the open event for the report. The error that you are getting is basically saying that the field (object) that you are trying to use is not known at that time.

 

[Guest_imported]

Hi and here we go,

The TestForm Button: Named 'PreView': On Click Event = TestMacro.

The TestMacro: Action = Open Report, Report Name = Engineer's Job Sheet Report, View = Preview, Filter Name = TestQuery, Where Condition = Blank, Window Mode = Normal.

The TestQuery: The correct field from the correct table. Sort = Accending, Show = Checked, Criteria = Between Forms![TestForm]![FromDate] And Forms![TestForm]![ToDate] Or Forms![TestForm]![ToDate] Is Null (The 'Is Null' part of the expression spills over to the next column once the query is saved.)

The 'Engineer Job Sheet Report'. On Open Event= Blank

I've tested this on the above and the Macro running just the query to bring up a data sheet. If I enter no dates in either of the date fields in the test form (FromDate & ToDate) I recieve all records. If I enter just the 'FromDate' or both the 'FromDate' & 'ToDate' I get no records at all.

I thought it might be a problem with the ' at the end of engineer's report, but the above does not work on any of the other five (six in total) reports either.

The error I mentioned before was using the code you kindly Posted for me.

Nearly taking the red pill on this one... or is it the blue pill. Anyways...

 

[Guest_imported]

Also, as a postscript to my last. I've tried a few things to attempt to identify this problem/error. Any other query I have created pulls exactly the information I need from the database and in the correct order... absolutely no problem there. However, I can not, not for the life of me, get to the bottom of this problem.

The need for queries and or reports based upon a date and only a date is key within this database. There’s no other method I can use.

I’m at a complete and total loss.

 

[67peshawar294]

DiZZY,
Start from scratch. I'm working access97 but i think all applies.
Select "Queries","New","Simple query wizard".Choose the table you want the data from.
Select the fields you want on the left side and either dblclick or press the arrow in the middle to move the desired fields the the right side. If there are no "numeric" fields click finish else if you want totals choose the summary options.
After you choose finish the query should open and all the fields with all the data should be visible.
Open the query in design view and in "YOUR DATE FIELD", under "Criteria" enter
Between [beg date] and [end date]
When you open the query a "enter parameter box will appear for "beg date" and "end date" Enter the dates you desire and the info should be there.
Jim

 

[Guest_imported]

I feel so stupid… Hopefully you appreciate my honesty. The problem has been that I’ve been using a ‘.’ as a date separator instead of a ‘/’. I just can’t believe just how stupid I’ve been. Thank you to those of you who have attempted to assist me.

Now I’m going to take to lead pill.

Thanks again, and sorry for wasting your time and I will never darken your doorstep again.
Mark.

 

[Guest_imported]

I feel so stupid… Hopefully you appreciate my honesty. The problem has been that I’ve been using a ‘.’ as a date separator instead of a ‘/’. I just can’t believe just how stupid I’ve been. Thank you to those of you who have attempted to assist me.

Now I’m going to take to lead pill.

Thanks again, and sorry for wasting your time and I will never darken your doorstep again.
Mark.