Date range 7 days either side of today

[Allan]

Hi

In a query I am trying to return all dates (birth dates) within seven days either side of the current date. The properties of the field and the query are set to Medium Date and the criteria I am using in the query is:

Between Date()-7 And Date()+7

and returns nothing.

I have tried several options suggested in other threads without success.
Access 2002 and Australian date format - 14-Sep-07

Any advice would be appreciated.

Tks
Allan

 

[boblarson]

Try:

Between DateAdd("d", -7, Date()) And DateAdd("d", 7, Date())

 

[Allan]

Thanks Bob but no luck - However I just had the "a hah" light come on.

The record needs to be there before you can retrieve it.

I am trying to produce a list of everyone who is having/had a birthday in the past seven or the next seven days. The reason it is not producing any results is that no-one in the database was born during that time period!!

I now need to rethink how to search on the day and months only - can that be done?

Apologies for not thinking it through properly before posting.

 

[boblarson]

Try doing this:

1. Create a column in your query like

DayMonth:Format([YourBirthdateFieldHere], "mm/dd")

2. And in the criteria use this:

Between Format(DateAdd("d", -7, Date()),"mm/dd") And Format(DateAdd("d", 7, Date()), "mm/dd")

This is untested, but theoretically (unless I'm spacing, which is possible) it should work.

 

[Allan]

Excellent. Thank you very much Bob. Works fine.

Regards

Allan

 

[boblarson]

Glad to hear it's working for you.

 

[bluenose76]

Bob,

Could this solution work for what i am trying to do? I have posted a question in the Modules and VBA Section.

Regards
Bluenose

 

[RuralGuy]

Bob,

Could this solution work for what i am trying to do? I have posted a question in the Modules and VBA Section.

Regards
Bluenose

The short answer Bluenose is YES.

 

[bluenose76]

Erm.....????

The short answer Bluenose is YES.

Rural Guy,

This is good news, however i have just tried this and my pea for a brain is not getting me to a working solution?

I have a query based upon a table that looks at the field DOB which is formatted as dd/mm/yyyy I have placed the two different types of code in "Criteria" field of the query design view, However when i do this it gives me no results whatsoever? Please advise me as to where i am going wrong????

 

[bluenose76]

Rural Guy,

This is good news, however i have just tried this and my pea for a brain is not getting me to a working solution?

I have a query based upon a table that looks at the field DOB which is formatted as dd/mm/yyyy I have placed the two different types of code in "Criteria" field of the query design view, However when i do this it gives me no results whatsoever? Please advise me as to where i am going wrong????

OK,

I have sussed it, and in doing so have highlighted the age old statement of "RTFQ" Read The ........ Question...!

Amazing as it is, when i re read what had previously been posted and digested it, i surprisingly managed to get it working.

Thank you once again.

 

[RuralGuy]

Glad you got it working!

 

[ruenells]

Date Range

I am trying to get a query to pull between two days of the week and from two specific times

Example

12/13/07 11:00 am to 12/18/07 3:00 pm.

I do not want to have to input the information.

What I would like is it to run from Tuesday to Thursday at those specific times.

So that when the query runs it already knows to look into that range and report the results. It will always be the current week information I need to be able to run this query twice a week.

Tuesday to Thursday and Thursday to Tuesday.

Any help would be appreciated and I need it really quickly.

Thank you so much!

Rue