Return data between two dates

[Wildster]

Hi,

I'm trying to return all data that has a bDate between two dates. i've wrote teh following query below but it returns nothing, any ideas why?

SELECT * FROM tblA
WHERE bDate Between 1/4/09 And 31/3/10;

If I put ' ' round the dates it returns 'data type mismatch'

Thanks,

 

[pbaldy]

Try

SELECT * FROM tblA
WHERE bDate Between #1/4/09# And #31/3/10#;

 

[Rabbie]

You need # as date delimeters so try

SELECT * FROM tblA
WHERE bDate Between #1/4/09# And #31/3/10#;

Ouch! You type too fast for me Paul!. At least I got the same answer

 

[Wildster]

No luck I'm affraid guys, can't seem to get it to work

 

[Rabbie]

Because of american date formats used by JET I would try

SELECT * FROM tblA
WHERE bDate Between #4/1/2009# And #3/31/2010#;

 

[jsv2002]

Where have you put this? usually you would enter dates via a form control so would look something like:

Dim strSQL As String

strSQL = "SELECT * FROM tbla WHERE bDate Between #" & strDate & "# And #" & EndDate & "# "

Then do what you want with strSQL.

EDIT:

Faster code slingers

 

[Wildster]

It was the american date format, thanks, can't believe it was something as simple as that!!

Just a follow up question: -

The query now works fine but the date range is a specified range as it's hard coded into the query, now I realise that I could make this a parameter query and use the input for date1 and date2 from a form. But basically the query is returning data for a financial year, therefore rather then the user having to keep entering 2 dates (start and end of financial year) I would like them to be able to instead just make one selection instead, i.e. a combo box containing a list of years i.e. 2008, 2009, 2010 etc. where the user selects a year and the query ammends accordingly to return all records between those two dates.

Therefore date1 would always be 1st April and date2 would always be 31st March, just the year's would need changing depending on the parameter chosen from the combo box. Obviousley the year for date2 would always be 1 more year than date1, so if 2008 was selected from the combo box then it would return date1 = 1st April 2008 and date2=31st March 2009.

I'm just struggling on how I would get just the year to be a parameter rather than the whole date, any suggestions would be greatly appreciated.

Thanks for your help

 

[pbaldy]

Check out the DateSerial function. You can hardcode the month and day arguments and get the year from your combo.

 

[Wildster]

Thanks, but is there a way of doing it which will work in Access and also in SQL Server? I've heard that DateSerial doesn't work in SQL server, and just in case I ever upscale the DB to SQL server I would ideally like to code it so it works in both.

Thanks

 

[gemma-the-husky]

these date functions will work in both - you still need an access front end, whatever backend you use

 

[Wildster]

Thanks guys, the DateSerial function worked a treat.

Much appreciated for the advice