Data Type mismatch error (1 Viewer)

[peskywinnets]

Can anyone offer up a suggestion as to why I'm getting a "data type mismatch in criteria expression" error with the following....


Set rst = CurrentDb.OpenRecordset("Select * from EbayOrders WHERE EbaySalesRecordNo = '" & EbaySalesRecordNo & "'")


....EbaySalesRecordNo in my Access table (called EbayOrders) is a Long Int & the variable EbaySalesRecordNo is also a Long Int.

Puzzled!

 

[Minty]

Your putting single quotes around the criteria so it's treating it like a string, which it itsn't.

 

[peskywinnets]

Your putting single quotes around the criteria so it's treating it like a string, which it itsn't.

Wow that was fast.....I swear that's the format I've been using in many other bits of code (& all I usually do is copy my previous code that worked!), but but removing the single quotes has worked - thanks! (been on with this for a while this morning!!)

 

[Minty]

Glad it was simple. I've just noticed my appalling use of Your - instead of You're. Someone please shoot me...

 

[Uncle Gizmo]

Glad it was simple. I've just noticed my appalling use of Your - instead of You're. Someone please shoot me...

Your liable to be shot by the grammar police!

Sent from my SM-G925F using Tapatalk

 

[peskywinnets]

Ok, I'm struggling again....my code barfed because one of my customer names has a single quote in it (e.g. O'loughlin), so I now need a way of handling the INSERT INTO command to cater for such a scenario.


the way I'm inserting into my table looks something like this (it's ugly as I have to go straight from parsed XML direct to the Access table)...

strSQL = "INSERT INTO Test(BuyerLastName)VALUES('" & objxmldoc.selectSingleNode("//ebay:Order[" & i & "]/ebay:TransactionArray/ebay:Transaction/ebay:Buyer/ebay:UserLastName").text & "')"
DoCmd.RunSQL strSQL

(the above command works fine until the customer name has a single quote embedded within)

Here's the error I'm seeing when a customer name has a single quote within ..



Top tips warmly received!

 

[bentheimmigrant]

I think that's a job for double quote marks. Try:

"""" & BuyerLastName & """"

 

[Frothingslosh]

I think a better option would be to turn it into a parameter query.

Just create and save the query, but in place of objxmldoc.selectSingleNode("//ebay:Order[" & i & "]/ebay:TransactionArray/ebay:Transaction/ebay:Buyer/ebay:UserLastName").text put [UserLastName]. At the top of the screen in the query builder, click 'Parameters' (in the design tab), and enter [UserLastName] and Text.

Then in your code, you can do the following:

Dim qdf As DAO.QueryDef
 
    Set qdf = CurrentDb.QueryDefs("YourQueryName")
    qdf.Parameters("UserLastName") = objxmldoc.SelectSingleNode("//ebay:Order[" & i & "]/ebay:TransactionArray/ebay:Transaction/ebay:Buyer/ebay:UserLastName").Text
    qdf.Execute

That will run the code correctly regardless of what is entered, and it will also help against SQL injection (see for reference 'Little Bobby Tables').

 

[peskywinnets]

I think that's a job for double quote marks. Try:

"""" & BuyerLastName & """"

Perhaps I have the deployment wrong, but the VBA editor protested at the construction of the line.

The & BuyerLastName & bit in my usage is actually...

& objxmldoc.selectSingleNode("//ebay:Order[" & i & "]/ebay:TransactionArray/ebay:Transaction/ebay:Buyer/ebay:UserLastName").text &

So what do I change in the full entry ...

('" & objxmldoc.selectSingleNode("//ebay:Order[" & i & "]/ebay:TransactionArray/ebay:Transaction/ebay:Buyer/ebay:UserLastName").text & "')

?? (the killer is the counter variable in the middle of the command [" & i & "]...it has quotes too!)

I think a better option would be to turn it into a parameter query.

Just create and save the query

I'm not using any queries, this is VBA parsed XML going straight into an Access table (& I can't use any VBA string variables because the XML occasionally has Russian characters which aren't stored by VBA strings correctly - I had a thread about this yesterday -http://www.access-programmers.co.uk/forums/showthread.php?t=290158 )

I'm out my depth here!

 

[peskywinnets]

Ok, since the syntax & situation is overwhelming, let's strip it right back for testing...

BuyerLastName = "O'Leary"
strSQL = "INSERT INTO Test(BuyerLastName)VALUES('" & BuyerLastName & "')"
DoCmd.RunSQL strSQL

the above barfs because of the single apostrophe in the name...so how can I construct the Insert Into command? (bearing in mind I have no control over the actual BuyLastName ...plus I don't know when a name will arrive with a single apostrophe embedded in it!)

 

[peskywinnets]

Ok, I think 've sussed it, since any customer name containing a single apostrophe (e.g. O'Leary) needs to be escaped with another single apostrophe (i.e. when using the Insert Into), I've pressed the Replace command into play within the overall Insert Into command (i.e. to replace any occurrence of a single apostrophe ' with two single apostrophes '')...

strSQL = "INSERT INTO Test(BuyerLastName)VALUES('" & Replace(objxmldoc.selectSingleNode("//ebay:Order[" & O & "]/ebay:TransactionArray/ebay:Transaction/ebay:Buyer/ebay:UserLastName").text, "'", "''") & "')"
DoCmd.RunSQL strSQL

...which seems to work (though this is now getting Fugly becuase it's not just customer names that can have potentially single apostrophes, but address lines too...this will surely be the longest ever 'insert to' command!)

thanks for hearing me out - & your input

 

[jdraw]

You will find it easier to debug and manipulate if you build your SQL string in pieces, and you can always do a DEBUG.PRINT SQL before attempting to execute it. This will show you ho Access has understood and will render your SQL.

Dim SQL as string
Sql = "INSERT INTO Test(BuyerLastName)VALUES('"
SQL = SQL & ...

I would also suggest you use

currentdb.execute SQL, dbFailonError

Good luck

 

[Minty]

@Jdraw - He can't do that because of Russian characters in the original string, putting them in a str variable changes them to ???? .

 

[peskywinnets]

Thanks chaps...I still consider myself first & foremost a kludger (& a relative beginner when it comes to programming, hence finding myself out my depth very quickly when I come up against obstacles), so all comments/tips are very welcome.

For the sake of completeness, here's my final code (that works for parsing incoming XML, that may contain either Russian or apostrophes!)...

strSQL = "INSERT INTO strSQL = "INSERT INTO EbayOrders(ExtRef,OrderDateTime,Subsource,OrderStatus,EbayUserID,ShippingCharge,CurrencyCode,VATRegion,BuyerCheckoutMessage,ShippingServiceID," & _
"PaymentDate,ExternalTransactionID,BuyerMessage,BillingFirstName,BillingLastName,Email,ShippingName,ShippingAddress1,ShippingAddress2,ShippingRegion,ShippingCity," & _
"ShippingPostcode,ShippingCountry,EbaySalesRecordNo,SKU,Quantity,ItemPrice,EbayShippingClass)VALUES" & _
"('" & ExtRef & "','" & OrderDateTime & "','" & SubSource & "','" & OrderStatus & "','" & EbayUserID & "','" & ShippingCharge & "','" & CurrencyCode & "'" & _
",'" & VATRegion & "','" & BuyercheckoutMessage & "','" & ShippingServiceID & "','" & PaymentDate & "','" & ExternalTransactionID & " ', '" & BuyerMessage & " '" & _
",'" & Replace(objxmldoc.selectSingleNode("//ebay:Order[" & O & "]/ebay:TransactionArray/ebay:Transaction/ebay:Buyer/ebay:UserFirstName").text, "'", "''") & "'" & _
",'" & Replace(objxmldoc.selectSingleNode("//ebay:Order[" & O & "]/ebay:TransactionArray/ebay:Transaction/ebay:Buyer/ebay:UserLastName").text, "'", "''") & "'" & _
",'" & objxmldoc.selectSingleNode("//ebay:Order[" & O & "]/ebay:TransactionArray/ebay:Transaction/ebay:Buyer/ebay:Email").text & "'" & _
",'" & Replace(objxmldoc.selectSingleNode("//ebay:Order[" & O & "]/ebay:ShippingAddress/ebay:Name").text, "'", "''") & "'" & _
",'" & Replace(objxmldoc.selectSingleNode("//ebay:Order[" & O & "]/ebay:ShippingAddress/ebay:Street1").text, "'", "''") & "'" & _
",'" & Replace(objxmldoc.selectSingleNode("//ebay:Order[" & O & "]/ebay:ShippingAddress/ebay:Street2").text, "'", "''") & "'" & _
",'" & Replace(objxmldoc.selectSingleNode("//ebay:Order[" & O & "]/ebay:ShippingAddress/ebay:StateOrProvince").text, "'", "''") & "'" & _
",'" & Replace(objxmldoc.selectSingleNode("//ebay:Order[" & O & "]/ebay:ShippingAddress/ebay:CityName").text, "'", "''") & "'" & _
",'" & Replace(objxmldoc.selectSingleNode("//ebay:Order[" & O & "]/ebay:ShippingAddress/ebay:PostalCode").text, "'", "''") & "'" & _
",'" & Replace(objxmldoc.selectSingleNode("//ebay:Order[" & O & "]/ebay:ShippingAddress/ebay:Country").text, "'", "''") & "'" & _
",'" & EbaySalesRecordNo & "','" & SKU & "','" & Quantity & "','" & ItemPrice & "','" & ChannelShippingClass & "')"
DoCmd.RunSQL strSQL

(I did say it was going to be a meaty command!)

I'm beginning to think that I've chosen the wrong language to approach this...a VBA string variable not being able to store the likes of Russian text is a killer.

 

[sneuberg]

In the future if you'd like to make your code more concise I suggest a function for escaping the single quotes. That would be:

Public Function ESQ(fld As String) As String

ESQ = Replace(fld, "'", "''")

End Function

Then for example instead of

 Replace(objxmldoc.selectSingleNode("//ebay:Order[" & O & "]/ebay:ShippingAddress/ebay:Name").text, "'", "''")

it would be

 ESQ(objxmldoc.selectSingleNode("//ebay:Order[" & O & "]/ebay:ShippingAddress/ebay:Name").text)

 

[peskywinnets]

That's a nice tip, but in this situation I can't use it, as it would involve using a VBA string variable to pass the text to your proposed function ......& unfortunately a 'VBA string variable' corrupts any Cyrillic font (Russian character) ...so I had to go longhand to move the XML text direct from the XML to the target Access table! (you can read more of the related woes here - http://www.access-programmers.co.uk/forums/showthread.php?t=290158 )

 

[NauticalGent]

Very informative thread!