Ran into in issue trying to support a SS backend and an Access backend in one of my DoCmd.RunSQL's The SQL is listed below. The field tblItemReceivingHeader.OrderDate in the where clause is the is the one that appears to have issues. The date field is define in SS as "date". I have tried different iterations of the variable formated as a date, a string (also different formats as a string) in the where clause but I can not get it to work. The other error I get is a Data type mismatch when I use just the - 'YYYY-MM-DD' literal for the date comparision in the where clause. Any ideas on what syntax I need to use? thanks
If strBackend = "SS" Then
' Sql Server 2008 access 2007 rt version
strDate = "'" & Format(Me![LotReceivingDate], "YYYY-MM-DD") & "'"
Else
' MS Access version -
strDate = "#" & Format(Me![LotReceivingDate], "MM\/DD\/YYYY") & "#"
End If
strSQL = "INSERT INTO tblRecyclingServicesOrderHeader ( CustomerId, CustomerType,OrderDate, OrderNumber, NumberOfSkids, ShipToAddressId, OriginOfMaterial)" _
& "SELECT tblItemReceivingHeader.CustomerId, tblItemReceivingHeader.CustomerType, tblItemReceivingHeader.OrderDate, tblItemReceivingHeader.OrderNumber," _
& "tblItemReceivingHeader.NumberSkidsReceived,tblItemReceivingHeader.WarehouseId, tblItemReceivingHeader.OriginOfMaterial " _
& "FROM tblItemReceivingHeader Where tblItemReceivingHeader.CustomerId = '" & Me![CustomerId] & "' and tblItemReceivingHeader.CustomerType = '" _
& Me![CustomerType] & "' and tblItemReceivingHeader.OrderDate = " & strDate & " and tblItemReceivingHeader.OrderNumber = " & Me![OrderNumber] _
& ";"
If strBackend = "SS" Then
' Sql Server 2008 access 2007 rt version
strDate = "'" & Format(Me![LotReceivingDate], "YYYY-MM-DD") & "'"
Else
' MS Access version -
strDate = "#" & Format(Me![LotReceivingDate], "MM\/DD\/YYYY") & "#"
End If
strSQL = "INSERT INTO tblRecyclingServicesOrderHeader ( CustomerId, CustomerType,OrderDate, OrderNumber, NumberOfSkids, ShipToAddressId, OriginOfMaterial)" _
& "SELECT tblItemReceivingHeader.CustomerId, tblItemReceivingHeader.CustomerType, tblItemReceivingHeader.OrderDate, tblItemReceivingHeader.OrderNumber," _
& "tblItemReceivingHeader.NumberSkidsReceived,tblItemReceivingHeader.WarehouseId, tblItemReceivingHeader.OriginOfMaterial " _
& "FROM tblItemReceivingHeader Where tblItemReceivingHeader.CustomerId = '" & Me![CustomerId] & "' and tblItemReceivingHeader.CustomerType = '" _
& Me![CustomerType] & "' and tblItemReceivingHeader.OrderDate = " & strDate & " and tblItemReceivingHeader.OrderNumber = " & Me![OrderNumber] _
& ";"