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

Data type mismatch

P: n/a
N J
Hi,

CurrentDb.Execute "Update tblDelayedOrders Set DELAYED = True Where ID = " &
Me.txtOrderNumber & ";", dbfailonerror
Me.StatusListBox.AddItem "ORDER # " & Me.txtOrderNumber & " MARKED AS
DELAYED " & "" & "", 0

When I change Where ID to Where TRACKING (another text field) I get the
error Data Type Mismatch in expression critera, or something like that

Help :(
ta
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
By assigning txtOrderNumer to field TRACKING which is text.. could that
possibly be messing it up?

Nov 13 '05 #2

P: n/a
N J
I don't think this could be the reason? it is just a name.

"Beacher" <be*****@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
By assigning txtOrderNumer to field TRACKING which is text.. could that
possibly be messing it up?

Nov 13 '05 #3

P: n/a
Beacher is on the right track. The syntax needs to change to handle a value
assigned to a Text data type field.

CurrentDb.Execute "Update tblDelayedOrders Set DELAYED = True Where TRACKING
= '" &
Me.txtOrderNumber & "';", dbfailonerror

Note the addition of the single quotes to delimit the value
Me.txtOrderNumber. Even though this may be a number, you are assigning it to
a text field and it will need to be treated as text. If the value can have
apostrophes in it, the syntax will need to be modified more.

--
Wayne Morgan
MS Access MVP
"N J" <bl*************@blueyonder.co.uk> wrote in message
news:VT*******************@fe3.news.blueyonder.co. uk...
Hi,

CurrentDb.Execute "Update tblDelayedOrders Set DELAYED = True Where ID = "
& Me.txtOrderNumber & ";", dbfailonerror
Me.StatusListBox.AddItem "ORDER # " & Me.txtOrderNumber & " MARKED AS
DELAYED " & "" & "", 0

When I change Where ID to Where TRACKING (another text field) I get the
error Data Type Mismatch in expression critera, or something like that

Help :(
ta

Nov 13 '05 #4

P: n/a
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:iz****************@newssvr11.news.prodigy.com ...
Beacher is on the right track. The syntax needs to change to handle a
value assigned to a Text data type field.

CurrentDb.Execute "Update tblDelayedOrders Set DELAYED = True Where
TRACKING = '" &
Me.txtOrderNumber & "';", dbfailonerror

Note the addition of the single quotes to delimit the value
Me.txtOrderNumber. Even though this may be a number, you are assigning it
to a text field and it will need to be treated as text. If the value can
have apostrophes in it, the syntax will need to be modified more.

--
Wayne Morgan
MS Access MVP
"N J" <bl*************@blueyonder.co.uk> wrote in message
news:VT*******************@fe3.news.blueyonder.co. uk...
Hi,

CurrentDb.Execute "Update tblDelayedOrders Set DELAYED = True Where ID =
" & Me.txtOrderNumber & ";", dbfailonerror
Me.StatusListBox.AddItem "ORDER # " & Me.txtOrderNumber & " MARKED AS
DELAYED " & "" & "", 0

When I change Where ID to Where TRACKING (another text field) I get the
error Data Type Mismatch in expression critera, or something like that

Help :(
ta

In addition to the comments you've already received, you also need to wary
of null values. In fact, I would take my time to dimension a strSQL
variable and build up the string before trying to execute it.

Dim strValue As String
Dim strSQL As String

strValue=Nz(Me.txtMyTextField ,"")

If Len(strSQL)>0 Then
strValue=AddQuotes(strValue, DoubleQuote)
strSQL="Update tblDelayedOrders Set DELAYED = True Where MyTextField=" &
strValue
...
Else
...
End If

And if adding those extra lines doesn't seem too bad you could also add
variables for dbs (Currentdb) so you can check for dbs.RecordsAffected,
which I gues should be one.
'Put the following in a separate module called modQuotes or similar:

Public Enum QuoteType
NoQuote
SingleQuote
DoubleQuote
End Enum

Public Function AddQuotes(strValue, Q As QuoteType) As String

Dim strReturn As String

Select Case Q

Case QuoteType.SingleQuote
strReturn = Replace(strValue, "'", "''")
strReturn = "'" & strReturn & "'"

Case QuoteType.DoubleQuote
strReturn = Replace(strValue, """", """""")
strReturn = """" & strReturn & """"

Case Else
strReturn = strValue

End Select

AddQuotes = strReturn

End Function





Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.