468,510 Members | 1,733 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,510 developers. It's quick & easy.

Data type mismatch in criteria expression.

Fary4u
273 100+
using " & variable & " ...
Syntax error (missing operator) in query expression
-----------------------------------------------------
'(orderID=5) and (productID=101) and (prodcolor=Medium) and (prodsize=300x300x300)'.

Expand|Select|Wrap|Line Numbers
  1. ocon2.Execute "DELETE FROM itemsOrdered WHERE (orderID=" & intOrderID & ") and (productID=" & intProdID & ") and (prodcolor=" & stprodcolor & ") and (prodsize=" & stprodsize1 & ")"
  2.  
-----------------------------
if i use ' " & variable & " ' ...

Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
---------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. ocon2.Execute "DELETE FROM itemsOrdered WHERE (orderID='" & intOrderID & "') and (productID='" & intProdID & "') and (prodcolor='" & stprodcolor & "') and (prodsize='" & stprodsize1 & "')"
  2.  
Jan 30 '12 #1
2 3002
Fary4u
273 100+
Problem with Numeric and string value even doing the rite way but still giving the error ?

Expand|Select|Wrap|Line Numbers
  1. datafiled no: work fine| intOrderID = "7"
  2. datafiled no: work fine| intProdID = "2"
  3. datafiled Chr: work fine| stprodcolor = "Medium"
  4. datafiled Chr: data mismatch| stprodsize = "400x400x400"
  5. datafiled Chr: no value given| ncolr = "Black"
  6.  
  7.  
  8. intOrderID = "7"
  9. intOrderID = cStr(intOrderID)
  10. intProdID = "2"
  11. intProdID = cStr(intProdID)
  12. stprodcolor = "Medium"
  13. stprodsize = "400x400x400"
  14. stprodsize = cStr(stprodsize)
  15. ncolr = "Black"
  16. ncolr = cStr(ncolr)
  17.  
  18. SQL = "DELETE FROM itemsOrdered WHERE (orderID=" & intOrderID & ") and (productID=" & intProdID & ") and (prodcolor=" & stprodcolor & ") and (colour=" & ncolr & ") and (size=" & stprodsize &")"
  19.  
Jan 30 '12 #2
Nicodemas
164 Expert 100+
Do not type cast the variables. You have already made them strings by wrapping them in quotation marks.

Your SQL statement will also fail because you must wrap any string variable in single quotes and any numeric variables must not be within quotes. Check your code for this.
Feb 2 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by N J | last post: by
1 post views Thread by amitbadgi | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.