Working with dates in SQL strings

Hi there,

I wonder if you could help, I am trying to extract records from my database which are BETWEEN two dates. The date field in my databse contains the dates in Short date format, like dd/mm/yyyy.
The SQL string I am using is as follows, but it just does not seem to work! It doesnt give any errors, but just doesnt extract any records (which definitely exist).

...FROM Advertisers WHERE Paid = 'yes' AND (dateAdded BETWEEN " & formatDateTime("01/01/05",2) & " AND " & formatDateTime("01/01/06",2) & ")"

I am new to working with dates so there is obviously something I am doing wrong (
Your help would be so much appreciated, manyyyy thanksss

Try doing a Response.Write(...) of your SQL statement and check that the FormatDateTime(...) has given the expected date format

Hi there, thank you so much for replying.

Yeah I did try that, and that seems fine to me... It writes the following :

SELECT SUM(Fee) FROM Advertisers WHERE Paid = 'yes' AND (dateAdded BETWEEN 01/01/2005 AND 01/01/2006)

Is there something I am maybe missing?

Thank you again

I think I have seen something.... I am basing it on the field dateAdded but on a SUM function... could this be the problem? If it is, how would I get around this? Would I have to manually code the summing up as I loop through the records, or can I still apply the SUM function somehow?

Thank you again

The portion of the SQL before the WHERE is what you are asking it to return. The portion after the WHERE is the selection criteria. The two can be completely unique. It should be possible to do what you are doing

You could try changing your code to produce the SQL statement like this:

SELECT SUM(Fee) FROM Advertisers WHERE Paid = 'yes' AND (dateAdded BETWEEN '01/01/2005' AND '01/01/2006')

You just need to add the ' characters

Thanks again I have tried what you suggested but it still does not seem to return ANY records at all!! I have tried it with so many different combinations...

...WHERE Paid = 'yes' AND (dateAdded BETWEEN " & formatDateTime("01/01/2004",2) & " AND " & formatDateTime("01/01/2005",2) & ")"
...WHERE Paid = 'yes' AND (dateAdded BETWEEN '01/01/2004' AND '01/01/2005')"
...WHERE Paid = 'yes' AND (dateAdded BETWEEN 01/01/2004 AND 01/01/2005)"
...WHERE Paid = 'yes' AND (dateAdded BETWEEN '01-01-2004' AND '01-01-2005')"

And it just will not seem to work... like I say the dateAdded field is in an MS Access database and is formatted as Short date

ANy suggestions would be greatly appreciated. And thanks for the help with the SUM bit, as you can see I am quite new to this
Thank you again.

dates passed to Access need to be surrounded by # symbols.

Sorry - I presumed this was SQL Server due to the forum it was posted in...

Thank you SO MUCH!!! As you can imagine it now works fine!! I am ever so grateful, I would have been there for many more hours wondering why it did not work!! So for MS Sql databases I containt the date in ' and for Ms Access # ?

Thank you again!!