Showing records between 2 dates (1 Viewer)

[Kaleen]

OK, I'm brand spankin' new to this forum..bear with me.

I need to create a query that will show me which employees are on "travel status". I will be running this query off a table that has their Name and their TravelStart and TravelEnd dates. I want to set this query up to find anyone's travel dates matching anything between TravelStart and TravelEnd. I also don't want to have to manually type in any dates each time (i.e. parameters).

Am I thoroughly confusing anyone yet?

Thanks in advance for any help!

 

[David R]

NO hand-keyed data?

How will you know what TravelStart and TravelEnd dates to search between then? Are you wanting to use the DateTimePicker?

 

[Kaleen]

The table will already have the dates entered in it, so I was thinking I could query using the "date()" function and something else....I just don't know what that something else should be. I need to be able to just click one button and have the query return anyone whose TravelStart THROUGH TravelEnd date matches with the current date.

What's a DateTimePicker?!?!?

 

[iangirven]

im not sure if ive got this bit right but it might help you any way.

in your query add an extra field that is set to the current date.ie Expr1: Now().

in the criteria use the expression builder to set it to return those values between you startdate and endate fields.

ie. Between ([Table1]![start]) And ([Table1]![finish])

i have checked this and it will return only those records where the current date falls between the start and finish dates.

set [table1] to the name of your table where the record is stored.
set [start] and [finish] to the name of your start and finish fields.

finally uncheck the show box for the field with the expr1:Now() in it or you will also get the current date as well.

hope this helps

ian

 

[David R]

OK, you changed the way the question was worded

Initially you said you wanted to search for a range of dates. Now you just need to know whose vacation encompasses today. Right? Or is it Date() - Date+2 weeks? (for example)

Ian's solution will do what you ask for one day. If you need a range you may have to do a bit of coding.

 

[Kaleen]

Ian--It worked!!! Thank you sooooo much
David R--Thank you for putting up with a rookie like me!

 

[David R]

No problem..

Glad you got it worked out.

(and if you want to see Rookie, you should find my first post here back in Oct. 2001.)