By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,293 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

Why?: Data type mismatch in ctiteria expression

P: 24
Trying to run this SQL and keep getting the message above...

Both fields: oversea order and TNT are text format!!
Expand|Select|Wrap|Line Numbers
  1. strSQL10 = "UPDATE [Orders] SET [Orders].[oversea Order] = 'YES' WHERE [Orders].[tnt] = " & Me![tnt]
Mar 20 '08 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,597
Trying to run this SQL and keep getting the message above...

Both fields: oversea order and TNT are text format!!

strSQL10 = "UPDATE [Orders] SET [Orders].[oversea Order] = 'YES' WHERE [Orders].[tnt] = " & Me![tnt]
Expand|Select|Wrap|Line Numbers
  1. strSQL10 = "UPDATE [Orders] SET [Orders].[oversea Order] = 'YES' WHERE [Orders].[tnt] = '" & Me![tnt] & "'"
Mar 20 '08 #2

P: 24
Expand|Select|Wrap|Line Numbers
  1. strSQL10 = "UPDATE [Orders] SET [Orders].[oversea Order] = 'YES' WHERE [Orders].[tnt] = '" & Me![tnt] & "'"

Thanks for the reply....but can you explain why the additional code is needed??
Mar 20 '08 #3

missinglinq
Expert 2.5K+
P: 3,532
It's a matter of correct syntax. In "Where" or similar Criteria expressions, you have to tell Access what datatype you're dealing with.
Expand|Select|Wrap|Line Numbers
  1. [Orders].[tnt] = " & Me![tnt]
tells Access that [tnt] is a Numeric datatype, while
Expand|Select|Wrap|Line Numbers
  1. [Orders].[tnt] = '" & Me![tnt] & "'"
tells Access that [tnt] is a Text datatype.

The same thing holds true for Criteria expressions used in functions such as DLookup(), DCount(), DMax(), etc.

Welcome to TheScripts!

Linq ;0)>
Mar 20 '08 #4

P: 24
Trying to run this SQL and keep getting the message above...

Both fields: oversea order and TNT are text format!!
Expand|Select|Wrap|Line Numbers
  1. strSQL10 = "UPDATE [Orders] SET [Orders].[oversea Order] = 'YES' WHERE [Orders].[tnt] = " & Me![tnt]
Thanks, Linq, but I'm still stuck.....

Let me clarify a few things. The oversea order field has a yes/no format. I am tying that field to a checkbox on a form..tnt has a text format.

Here is the code I have:
Expand|Select|Wrap|Line Numbers
  1. strSQL10 = "UPDATE [orders] SET [orders].[oversea order] = True WHERE [orders].[tnt] = " & Me![tnt] & "'"
Wnen run, I get this error: syntax error in string in query expression '[orders].[tnt] = 0001'

If I change the code to this:
Expand|Select|Wrap|Line Numbers
  1. strSQL10 = "UPDATE [orders] SET [orders].[oversea order] = True WHERE [orders].[tnt] = Me![tnt]
then it prompts me for a parameter value (tnt)..If I input 0001, it works!! What am I missing??
Mar 20 '08 #5

P: 24
OK...I'm going in a different direction....I would like to use the results of a query to set the value of a yes/no field...Here is the code I have, but it still asks for a parameter value...If I type in a correct value, it will update the yes/no box...but why is it not autmatically updating???
Expand|Select|Wrap|Line Numbers
  1. strSQL10 = "UPDATE [order] SET [order].[oversea order] = True WHERE [order].[order ID] = [Find duplicates for order].[order ID Field]"
Mar 20 '08 #6

NeoPa
Expert Mod 15k+
P: 31,186

NeoPa
Expert Mod 15k+
P: 31,186
Thanks, Linq, but I'm still stuck.....
...
Here is the code I have:
Expand|Select|Wrap|Line Numbers
  1. strSQL10 = "UPDATE [orders] SET [orders].[oversea order] = True WHERE [orders].[tnt] = " & Me![tnt] & "'"
Wnen run, I get this error: syntax error in string in query expression '[orders].[tnt] = 0001'
...
This is a perfect illustration of why we try to insist that everyone uses [ CODE ] tags on the forums.

With the tags it can clearly be seen that this SQL is missing a single-quote (') before the double-quote (") after "[tnt] = ".

I've added in all the missing [CODE ] tags now so if you revisit post #2 you'll see and more easily understand what you should have used for your SQL.
Mar 26 '08 #8

NeoPa
Expert Mod 15k+
P: 31,186
Of course, now I check back, I find that ADezii's post #2 was the only one that WAS correctly [ CODE ] tagged.

Anyway, if you follow his code your SQL will work.

PS. In case you're wondering why it's so important, one of the main reasons is that it is easy to differentiate between quotes (') and double-quotes (") within [ CODE ] tags as the font used is unambiguous, so although it's generally ok to allow single lines of code without the tags - it's not a good idea when different quotes are interspersed, or when a single line of code is displayed as wrapped on the page.
Mar 26 '08 #9

Post your reply

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