SQL Date Compare 1

[timb94]

I didn't know if this should be posted in the VBA, forms or SQL forums so if I need to move it please let me know.

I'm having a problem with a SQL select statement in an Access form. The table is a linked SQL table.

Below is my code.

Dim strSQL As String
Dim strDate As Date
strDate = "1/1/2008"
strsql = "SELECT parcelid FROM dbo_master_transfer_table "
strsql = strsql & "where saledate >= '" & Format(strDate, "mm/dd/yyyy") & "'"

Dim rsResidential As Recordset
Set rsResidential = CurrentDB.OpenRecordSet(strSQl)

The problem is when I execute SET statement it returns an error "Data type mismatch in criteria expression."

I have tried "where saledate >= '1/1/2008'" and got the same message.

The saledate field in the table is a date field.

I coded a message box right after the select and the code looks good. If I run the code "SELECT parcelid FROM dbo_master_transfer_table WHERE saledate >= '1/1/2008'" in SQL it runs with no problem.

I know it must be something simple but for some reason I can't see it.

Any assistance would be appreciated.

Thank you.

 

[SkipVought]

Hi,

DATES are not STRINGS!

Dates are NUMBERS like right now in North Texas, the Date/Time value is 40248.30994.

Dim strDate As String
strDate = "2008/1/1"
strsql = "SELECT parcelid FROM dbo_master_transfer_table "
strsql = strsql & "where saledate >= #" & strDate & "#"

The # delimiters coerse a STRING to DATE conversion.

The yyyy/mm/dd is an unambiguous date structure, where m/d/yyyy is not.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[timb94]

Thanks Skip.

That did it. For some reason I always have a problem switching from Access to SQL when it comes to dates.

A STAR for you.