Using Criteria As Filter In Query (date), Access 2003

[aldeb]

I am wanting to use criteria in a query that will allow me to filter data based on a date range like: >25 days but <50 day. I possibly would like to see data that is more than 25 days old but less than 50 or more than 50 and less than 75, etc. This would be based on a "StartWaterDate" field. This field would have a date that water was started. Thanks for help.

 

[bob fitz]

Perhaps something like:
Between DateAdd("d",-25,Date()) And DateAdd("d",-75,Date())

 

[bob fitz]

If the user needs to input the number of days from and to, use:
Between DateAdd("d",-[From: Days Ago],Date()) And DateAdd("d",-[To: Days Ago],Date())

 

[aldeb]

Thanks everyone, I will give this a try.

 

[bob fitz]

Thanks everyone, I will give this a try.

OK. Let us know how you get on.

 

[aldeb]

If the user needs to input the number of days from and to, use:
Between DateAdd("d",-[From: Days Ago],Date()) And DateAdd("d",-[To: Days Ago],Date())

I have tried the above and no matter what numbers I put in it brings up no data. From: 25 To: 50 is an example of numbers I am putting in. I have put this in the criteria field of my PlantStartDate field. Please advise!

 

[aldeb]

Here are the 3 helps I have received for this:
This one works:
Between Date()-75 And Date()-50

This one works:
DateAdd("d",-25,Date()) And DateAdd("d",-75,Date())

This one returns no data but I would love to have the versatility of putting in my days on the fly. Hope you all can tell me why this one brings no data up.

This brings up no data when I fill in the two numbers.
DateAdd("d",-[From: Days Ago],Date()) And DateAdd("d",-[To: Days Ago],Date())

 

[aldeb]

I experimented around and just added "Between" before the string and it worked. Thanks for all of your help.

 

[bob fitz]

I experimented around and just added "Between" before the string and it worked. Thanks for all of your help.

So, was the solution I offered in post#3 any different

 

[Brianwarnock]

Love to know how this one works

DateAdd("d",-25,Date()) And DateAdd("d",-75,Date())

It worries me that the poster does not realise that the function is
Between ........ And ......

It never fails to amaze me how often I have wondered down the years if the posters we are helping really read and study what is written.

Brian

 

[aldeb]

So, was the solution I offered in post#3 any different

They all three worked but the one allows me to type in days on the fly!

I want to thank everyone for their help!

 

[Brianwarnock]

But how did this one work without the Between?

DateAdd("d",-25,Date()) And DateAdd("d",-75,Date())

Aldeb can I respectfully suggest that you read posts carefully and proof read what you write, we all make typos. Bob's original on the fly had a Between your copy of it did not, which is why it did not work.

Computers are idiots , it is up to us to get things correct so care is essential.

Best wishes for the future


Brian

 

[aldeb]

But how did this one work without the Between?

DateAdd("d",-25,Date()) And DateAdd("d",-75,Date())

Aldeb can I respectfully suggest that you read posts carefully and proof read what you write, we all make typos. Bob's original on the fly had a Between your copy of it did not, which is why it did not work.

Computers are idiots , it is up to us to get things correct so care is essential.

Best wishes for the future


Brian

Brian, Blessings to you and I thank everyone for their help. I did miss the word Between when I copied and pasted into my query. My Bad.

It seems you missed something earlier when I posted earlier about the 3 solutions I had received. The one you are asking about I had said earlier worked. Again, I thank everyone for their help.