I have a table where I store names and dates etc, nad want to check if there is already an entry with a given name and date before inserting a new entry.

INSERT INTO table (Name,Date) VALUES ('Name','10/30/07')

Works as expected but

SELECT * FROM table WHERE date=10/30/07;

returns no records. If I use single quotes around the date in the SELECT I get an error.

SELECT * FROM table WHERE name='Name';

Works fine. Should I be able to use multiple WHERE conditions? ie WHERE name='Name' AND date=10/30/07.

I'm sure I'm missing something obvious...

Hi Andrew

This is one of those classic Access "gotchas" that Microsoft have left littering the application, but don't cover (in my view) in anything like enough detail in the documentation.

Literal date strings in Access SQL must be (a) in US date format (mm/dd/yyyy), and (b) delimited by the # character (hash, pound, call it what you will).

The easy answer is to write a function like this:

Public Function FormatDate(datInput as Date) as String

    FormatDate = VBA.Format(datInput, "#mm/dd/yyyy#")

End Function

And then use

    strSQL="SELECT * FROM MyTable WHERE MyDate = " & FormatDate(datDate)

Hope this helps!

Richard

Thanks Richard.

I got that the date format is US, in any case the date is pulled from a file modified date, so it does that by itself.

does this apply to all Microsoft SQL versions? I couldn't see why I could write in in OK. I was on the verge of writing it in as a string and doing all the date work in the VBA...

Your format date function didn't work...

It returns 3573mm/dd/yyyy3

I've got away with strSQL="SELECT * FROM table WHERE date=#" & date &"#;"

So I'm happy anyway.

Apologies - I was typing code from memory, always a bad idea!

Here's the function I actually use (it has a couple of other bells and whistles):-

Public Function FormatDate(datInput As Date, Optional intOffset As Integer, Optional strFormat As String) As String

    ' Formats the date based on passed parameters
    ' Default option will format it in US format for use in Jet SQL
    If strFormat = "" Then strFormat = "\#mm\/dd\/yyyy\#"
    FormatDate = VBA.Format(datInput + intOffset, strFormat)

End Function

What I forgot was the \ backslashes to escape the characters in the format string.

To answer your other question, Access/JET is the only database I know that uses this syntax, but most RDBMSs are fussy about literal date formats. DB2 is a particular pain, timestamp (= date and time together) fields have to have the time specified down to microseconds, e.g. '2007-05-31-17:06:34.123456' - not easy!

Good luck with your coding...

Richard