same day, different year

[Guest]

I want to run a query to pick out dates occuring before 30th March (any
year). Can anyone tell me how to format the date field SQL in the query to
always look before the 30th March with the searcher inputting only the year
manually. Much obliged if you can help........

 

[Rick Brandt]

I want to run a query to pick out dates occuring before 30th March (any
year). Can anyone tell me how to format the date field SQL in the query to
always look before the 30th March with the searcher inputting only the year
manually. Much obliged if you can help........

Your criteria entry would be...

BETWEEN DateSerial([Enter the Year], 1,1) AND DateSerial([Enter the Year], 3,
30)

The above assumes all DateTimes have midnight as the time.
If that is not the case change the "3, 30" to "4, 1"

Make sure you enter the two parameter strings exactly identical and you will
only be prompted once even though the query will use the value twice.

 

[Guest]

Thanks for being so quick but, as usual I didn't make myself totally clear!.
I need the criteria to search back to 1st April of the previous year, ie a
full 365 days. Do you think I can just put in "-365" on the second criteria
input?

I want to run a query to pick out dates occuring before 30th March (any
year). Can anyone tell me how to format the date field SQL in the query to
always look before the 30th March with the searcher inputting only the year
manually. Much obliged if you can help........

Your criteria entry would be...

BETWEEN DateSerial([Enter the Year], 1,1) AND DateSerial([Enter the Year], 3,
30)

The above assumes all DateTimes have midnight as the time.
If that is not the case change the "3, 30" to "4, 1"

Make sure you enter the two parameter strings exactly identical and you will
only be prompted once even though the query will use the value twice.

 

[Tom Collins\(Home\)]

| I want to run a query to pick out dates occuring before 30th March
(any
| year). Can anyone tell me how to format the date field SQL in the
query to
| always look before the 30th March with the searcher inputting only
the year
| manually. Much obliged if you can help........

Try the Day, Month, & Year functions

SELECT *
FROM Table1
WHERE (((Month([Date_Field]))<3) AND
((Year([Date_Field]))=[Input_Year]))
OR (((Month([Date_Field]))=3) AND ((Year([Date_Field]))=[Input_Year])
AND ((Day([Date_Field]))<30));



Tom Collins

 

[Rick Brandt]

Thanks for being so quick but, as usual I didn't make myself totally clear!.
I need the criteria to search back to 1st April of the previous year, ie a
full 365 days. Do you think I can just put in "-365" on the second criteria
input?

This is better (accounts for leap years).

BETWEEN DateSerial([Enter the Year]-1, 4,1) AND DateSerial([Enter the Year], 3,
30)

 

[Guest]

Thanks very much Rick, thats done the job. thanks also to Tom for answering.

denthemen

Thanks for being so quick but, as usual I didn't make myself totally clear!.
I need the criteria to search back to 1st April of the previous year, ie a
full 365 days. Do you think I can just put in "-365" on the second criteria
input?

This is better (accounts for leap years).

BETWEEN DateSerial([Enter the Year]-1, 4,1) AND DateSerial([Enter the Year], 3,
30)

 

[Tom Collins]

Rick, your solution was much cleaner.

denthemen, March has 31 days, not 30.

Thanks very much Rick, thats done the job. thanks also to Tom for answering.

denthemen

Thanks for being so quick but, as usual I didn't make myself totally clear!.
I need the criteria to search back to 1st April of the previous year, ie a
full 365 days. Do you think I can just put in "-365" on the second criteria
input?

This is better (accounts for leap years).

BETWEEN DateSerial([Enter the Year]-1, 4,1) AND DateSerial([Enter the Year], 3,
30)