Date Query Help

[sysop470]

Hi..

I an a newbie and i have a problem regarding a particular query . I need to show records those that are till 12th July of every year.

What function do i have to use???

Thanks alot
Sysop470

 

[SammyJ]

Are you saying that you want to see records from January 1st until July 12th regardless of the year?

 

[sysop470]

@SammyJ

Yes my friend!!!

 

[SammyJ]

Well then, one way would be to add two fields to your query. The first would be something like M: Month([DateField]) and the other D: Day([DateField]).

This obviously assumes your dates are in a field called DateField.

Run that to see the results these additions give you, so you have an idea what is going on.

Then, back in design mode add the following critieria for these fields:
In one row, under M, add <7. That's all.
In the next, under M, add 7 and under D add <=12.
(These rows are criteria rows at the bottom of the grid. If you already have other criteria these need to be on both rows.)

There are a few other ways I can think of to do this. This is probably the most instructive.

Once it is working you can hide the two new fields by clicking off the Show check boxes. If you want to see them then give them better names than M and D.

Sam.

 

[sysop470]

Thanks a lot but its now working properly . The result of the query is that if for example the date is 13/01/1987 it wont display, because the Day is <=12. I want to dispaly all dates that are below or equal to 12/07(any year)


Thanks
Sysop470

 

[SammyJ]

Yeah I get it. That's why you test for M<7 on a separate criteria line. So any date in a month earlier than July will always be returned, as well as any date in July which is up to the 12th, from the second criteria line.

There's also the format function which returns a day's position in the year, which would be useful, though July 12th's position in the year changes on leap years.

Or you could simply put the following criteria in for the date field:
<=CDate("July 12 " & Year(DateField))

That's probably the best actually.