Operand type clash: Date inconsistent with float, data type mismatch (1 Viewer)

[Zeke]

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] _
& ";"

 

[pbaldy]

For starters, check your spacing. You have it right sometimes, but it looks like there will be no space before SELECT. Also, how/where are you executing the SQL? The backend is not as relevant as where the processing is taking place. In other words, even with a SS backend, if the tables are linked and you're using a JET query to add the data, you would use the #. You'd use the ' in a stored procedure or pass-through query, because the processing would be happening on the server.

 

[ChrisO]

Don’t know about SS but this should work for Jet: -

and tblItemReceivingHeader.OrderDate = " & Int(Me![LotReceivingDate])

The Int should hold the date in its storage format, namely a Double, but strip of any fractional days from LotReceivingDate if it exists.

Would be nice to know if it also works for SS???

 

[Zeke]

Thanks for the replies.. The SQL happens in Jet when the back end is Access and and in SQL Server when the back end is SQL Server ( the strBackend is a global variable set on startup). The DoCmd.RunSql makes it a pass through query. The code works fine for Jet. I will try the Int function to maintain the storage format and see if that works and let you know.. seems possible since the Int should occur before sending, might be able set a variable using the Int function and send that data to the sql server backend..

 

[Zeke]

The use of Int did not work.

The Lot Recieving Date is defined in the Access table and on the form as a ShortDate, in SQL Server it is defined as a Date. When I use the Int it gives the error Operand type clash: Int inconsistent with float. researching how to make the form date a float type for the pass through query... odd that I can run the following pass through query with out issue (the date fields are defined the same way as LotReceivingDate in Access and SQl Server).. do you think the Insert/select combination might be part of the issue why the error occurs?

strSQL = "Insert Into tblCertificateOfRecycling (CertificateOfRecyclingNbr,CustomerId,CustomerType,OrderDate,OrderNumber," _
& "PONumber,RecyclingMethod) Values(" & intNextCertificateOfRecyclingNbr & ",'" & Me.CustomerId & "','" & Me.CustomerType & "',"
If strBackend = "SS" Then
' Sql Server 2008 access 2007 rt version
strSQL = strSQL & "'" & Me.OrderDate & "'," & Me.OrderNumber & "," & Me.PONumber & ",'" & strRecyclingMethod & "');"
Else
' access version -
strSQL = strSQL & "#" & Format(Me![OrderDate], "MM\/DD\/YYYY") & "#" & "," & Me.OrderNumber & "," & Me.PONumber & ",'" & strRecyclingMethod & "');"
End If

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

thanks in advance...

 

[pbaldy]

Using DoCmd.RunSQL does not make it a pass-through query. Try it with the #.

 

[Zeke]

Thanks,

I tried again with "#" and it works. I made it harder than it was.. I am new to Access so I do not know all the terminology.. I understood a pass through query to be one that executed directly against the database using the SQL provided in the RunSQL command. Seems that is incorrect and a pass through must be built by the Access system.. What makes a SQL statement execute with Jet vs SQL Server? Is there a mechanism which indicates that a SQL statement run through DoCmd.RunSQL will execute through Jet or on SQL Server. The system I am working on must support both and I do not understand that the one insert required that the date be bound by single quotes and the other be bound by "#".. Could the use on 2007 Runtime cause a difference?

 

[pbaldy]

I don't understand this as well as others, but basically Jet will make an effort to have the server process the SQL all the time. With fairly simple SQL like you posted, I believe that the server would have actually processed it. However, it was Jet that was handling it initially, hence the need for the #. If you use something the server can't understand (an IIf() function for example), the client will have to process it.

A pass-through query is a specific type of query that will always be processed on the server, and would have to use syntax appropriate to the server. In versions prior to 2007, you create one by having a query in design view and selecting Query/SQL Specific/Pass-Through. In 2007 it's on the ribbon.

Clear as mud now?

 

[Zeke]

Wow...I thought when I ran into the first SQL having issues that I needed to address them all in the same fashion.. I have 104 other DoCmd.RunSql's that it now seems I have to test each of them to see where they get processed... I just hope it is consistant in its choice of where to process.... thanks for your enlightenment