Using Dates in a query

[DasT]

I've never used queries before so forgive me if this sound confusing

I have a table set up with several differn entries (too many to list) one of which is an 'action due by date' and another is 'Primary Contact' I need a query set up to check first the contact and then if the date is both between the dates of the following week (the reports will be pulled off on a friday) or anything less than this i.e. the due by date is anything earlier than the Friday of next week (something like < today + 7)

Then once its found all of these it needs to show all the different entries that correspond so these reports can be printed.

Hope thats been explained well enough, if it not let me know

thanks for your help

 

[mdmilner]

You've got the right idea, try fiddling in the QBE wizard.
One hint, put a # character on either side of the date you type in to search.

Also, peek at Now()



Mike

 

[jsi]

Hi
The following criteria set in your date field will return all dates that fall between todays date and the coming friday.

Between Date() And DateAdd("d",6-Weekday(Date()),Date())

HTH
Jim

 

[DasT]

I've tried entering this into the critera section in my query (whilst in design view in the first criteria section under the date column, right? ) but it didn't return any results : / did you mean for me to enter some information into the formula before the query was run?

p.s. the date column in the table is set to date format incase you are wondering :D

 

[DasT]

Never mind, its working now, it musn't have saved after i entered the dates or something like that, but it works great, thanks for your help

 

[Norie]

Between Date() And DateAdd("d",6-Weekday(Date()),Date())

Will return dates between 06/10/04 and 08/10/04 (today anyway), is that what you want?

 

[DasT]

Scratch that, its not working right, can you explain what all of that formula means? if you don't mind could the fact that all the information i enter into my table is done through a form have any effect on the query?

thanks for your help

 

[DasT]

Ah I see now (didn't see your post Norie) and no its not what i want, preferbly i'd like something that will show all dates earlier than next week, i.e. any date before a week from the day the query is ran if possible

sorry if it wasn't explained well, and the overegarness to respond

 

[Norie]

Perhaps change this

Between Date() And DateAdd("d",6-Weekday(Date()),Date())

to

<DateAdd("ww",1,Date())

That should return all dates less than (ie earlier) than 1 week from today.

 

[DasT]

er, it looks like you may have missed a line out of your post