Between Query 1

[Powerhouse123]

Hi,

I have a very simply Table...

ID, NAME, ISSUE DATE, EXPIRY DATE
1 Bob 01/01/2004
2 Dave 23/04/1998
3 Jim 02/01/2004
4 Scott 05/11/2007
5 Keith 06/03/1995

The expiry column is blank because this is calculated in the query...

Expiry Date: DateSerial(Year([Issue Date])+3,Month([Issue Date]),Day([Issue Date]))

This simply adds three years to the Issue Date

So the query is a simple select query selectind ID, Name, Issue Date and the Expiry Date is calculated. This works fine. The resultant query results look like..

ID NAME ISSUE DATE EXPIRY DATE
1 Bob 01/01/2004 01/01/2007
2 Dave 23/04/1998 23/04/2001
3 Jim 02/01/2004 02/01/2007
4 Scott 05/11/2007 05/11/2010
5 Keith 06/03/1995 06/03/1998

Great...but...

I want to be able to select a range of Expiry Dates say all thjose which fall in January so in the criteria field of the Expiry field in the query I have...

Between [Please enter start date] AND [Please enter end date]

This brings up the boxes fine but when selecting a range from 01/01/2007 to 31/01/2007, it brings back all the records and not just Bob and Jim like it should.

Do you have any suggestions, it should be so simple.

Many thanks in advance.

 

[Ed2020]

You need to explicitly convert your expression and your parameter values to dates:

Expiry Date: CDate(DateSerial(Year([IssueDate])+3,Month([IssueDate]),Day([IssueDate])))

Between CDate([Start Date]) And CDate([End Date])

Ed Metcalfe.

Please do not feed the trolls.....

 

[Powerhouse123]

Wow, that is brilliant thank you.

Is there a way to instead of typing two sets of dates using the between criteria, to create a query which will select all those records which expire in January?

Many thanks again.

 

[Ed2020]

Do you mean that the user just selects "January 2007" instead of having to type "01/01/2007" and "31/01/2007"?

If so you could add another expression to your query:

Expiry Month: Format(CDate(DateSerial(Year([IssueDate])+3,Month([IssueDate]),Day([IssueDate]))),"mmmm yyyy")

You can then prompt the user to enter just the month and year.

Ed Metcalfe.

Please do not feed the trolls.....

 

[Powerhouse123]

Yes that is right...

Thank you very much for your help.

 

[Ed2020]

You're welcome.

Ed Metcalfe.

Please do not feed the trolls.....