probelm with query using dates

[shopoholic]

I am having an annoying problem that I can't understand. I have the following query:

SELECT Bookinfx.Key, Bookinfx.[Park Name], Bookinfx.[Start Date], Bookinfx.[Accom Type]
FROM Bookinfx
WHERE (((Bookinfx.[Park Name])="TORBAY") AND ((Bookinfx.[Start Date])>=#01/01/2005#) AND ((Bookinfx.[Start Date])<=#31/12/2005#));

Which I am using to find out bookings for a specific holiday park for a specific year. However, if I try to alter the parameters to find monthly figures, only some months work and others (feb, april, june, sept, nov) do not. When I try and use these months it tells me that there is a syntax error in the date in the query expression.

Any help gratefully received!

ps maybe the answer would be a different query that returned the total number of bookings for each month for a particular year and park? But if this is the case I'm not sure how to do that!

 

[namliam]

Try using MM/DD/YYY format instead of DD/MM/YYYY format

To make it easy on your self you can use datevalue(Year,month,date) instead. This never fails, while its easy to understand....

 

[Bat17]

As the months that dont work have less than 31 days are you sure that you are adding the correct dates?

Peter

 

[shopoholic]

No luck as yet

Thank you for the suggestions. Tried using MM/DD/YY and no joy and also tried ensuring date was up to 30th where appropriate, still no luck.

Tried datevalue but not sure that I have used it correctly?:
SELECT Bookinfx.Key, Bookinfx.[Park Name], Bookinfx.[Start Date], Bookinfx.[Accom Type]
FROM Bookinfx
WHERE (((Bookinfx.[Park Name])="TORBAY") AND ((Bookinfx.[Start Date])>=datevalue(02/01/2005) AND ((Bookinfx.[Start Date])<=datevalue(02/30/2005)));

Is that wrong?

 

[Bat17]

datevalue(02/30/2005)

Invalid date!! the is no 30th of Feb in any year!

Peter

 

[Rich]

WHERE (((Bookinfx.[Park Name])="TORBAY") AND ((Bookinfx.[Start Date])>=(Format(#01/01/2005#,"MM/DD/YYYY")) AND etc

 

[namliam]

SELECT Bookinfx.Key, Bookinfx.[Park Name], Bookinfx.[Start Date], Bookinfx.[Accom Type]
FROM Bookinfx
WHERE (((Bookinfx.[Park Name])="TORBAY") AND ((Bookinfx.[Start Date])>=dateserial(2005,2,1) AND ((Bookinfx.[Start Date])<=dateserial(2005,3,0)));

Sorry I meant Dateserial, not datevalue. Also using this you can easy add a parameter and allways query a month. Using day 0 will make the function jump back to the last day of the previous month. If it be a 28, 29,30 or 31 day does not matter!!! Access will do the thinking for you

 

[shopoholic]

Problem Solved

Thanks Bat17 you spotted the problem...the problem being me! Being a bit of a dizzy blonde today. Also it is friday and have my weekend brain in.

Thanks for the help and suggestions everyone