new to db, cant figure query critera for between dates

[sumhungl0]

im tryin to get this query to get data from current weeks inputs. ive googled and tried everything with no luck so far. so here i am. can someone tell me what im doing wrong? keep in mind i dont know what im doing.....

heres what i put for criteria under my date column.

Between Date() And Date()-7

doesnt work.... anybody? thanks in advance...

 

[RuralGuy]

What you have should work. What version of Access are you using? Are you sure you have records that meet that criteria?

 

[sumhungl0]

im using 03 and i have changed the number from a little to alot and i still cant get any results. i know that the criteria would find the range of records i have, about a months worth.

 

[sumhungl0]

just tried this with no results.....

<=Date()-3
and
>=Date()-3

 

[RuralGuy]

Do you have any other criteria specified that might be eliminating records? Your field *is* a DateTime field, right? Do you use USA format dates or one of the international date formats?

 

[sumhungl0]

its in this format...

dd-mmm-yy or 01-Nov-07

 

[RuralGuy]

I use USA dates so my test just works. Yours is different so you may need to make some adjustments recommended in this link to MVP Allen Borwne's site.

 

[sumhungl0]

i only put that format for the input form, so does that mean the table doesnt use it as just a date?

 

[RuralGuy]

Unfortunately, when you have your Windows Regional Date Setting to other than USA, Access can get the compare of dates incorrect.

 

[sumhungl0]

i got it

thank you very much for all the help. heres what my problem was....

my table had the date format as text, so i changed it to medium date format and put my query back to Between Date() And Date()-7 and everything works great.

thanks again everybody..
i feel like suck an idiot..

 

[RuralGuy]

I'll bet that will not trip you up again. There is so much more to learn from your mistakes than your successes. Glad you got it sorted.