Search between two dates

  • Thread starter Thread starter Dennis Villareal
  • Start date Start date
D

Dennis Villareal

i have a database that pulls from our system.

the date is displayed 20081118 yearmonthday on everything

i would like to set up a query that shows only the items from 60 days - 89
days old

then another query that shows 90-119 days old

then another query that shows everything 120 days and over

i will make 3 seprate queries that i can put to a switchboard and just click
each one when needed.

is there a way to set something up that tells it to look there?

i know i can convert the date to display as 18 Oct 08 by putting the
following in the query

Due Date:
Format((DateSerial(Left([ORD_DUE_DT],4),Mid([ORD_DUE_DT],5,2),Right([ORD_DUE_DT],2))),"dd mmm yy")

would it be easier to set up something to show me like that or can i work
with what i have somehow from my system?
 
Hi Dennis,

Only if you want to start doing your own data arithmetic. Uugh!
Assuming that you have no control over how the data is stored and that it is
stored as a string and that you are linking to external data rather than
importing, I would recommend creating a master query that does the conversion
from string to date; essentially what you have, only not reformatted back to
the string (i.e. drop the Format() function). Then use that query as the
source of your three other queries. In them use the between operator along
with the DateAdd() function to get the records that you desire.

Clifford Bass
 
i have a database that pulls from our system.

the date is displayed 20081118 yearmonthday on everything

i would like to set up a query that shows only the items from 60 days - 89
days old

then another query that shows 90-119 days old

then another query that shows everything 120 days and over

i will make 3 seprate queries that i can put to a switchboard and just click
each one when needed.

is there a way to set something up that tells it to look there?

i know i can convert the date to display as 18 Oct 08 by putting the
following in the query

Due Date:
Format((DateSerial(Left([ORD_DUE_DT],4),Mid([ORD_DUE_DT],5,2),Right([ORD_DUE_DT],2))),"dd mmm yy")

would it be easier to set up something to show me like that or can i work
with what i have somehow from my system?
Click to expand...

What's the datatype of the field? Text or Number? Assuming that it's Text, you
can use a criterion such as

< Format(DateAdd("d", -60, Date()), "yyyymmdd") AND >= Format(DateAdd("d",
-89, Date())

for the first,

< Format(DateAdd("d", -90, Date()), "yyyymmdd") AND >= Format(DateAdd("d",
-120, Date())

for the second, and

< Format(DateAdd("d", -120, Date()), "yyyymmdd")

for the third. If you use the date/time field Due Date just use the DateAdd
expressions directly, without the Format().
 
Back
Top