UPDATE Query syntax 1

[RonMcIntire]

All:

I'm trying to update a table with two predefined variables but I keep getting a compile error. This is how I'm doing it.

strSQL = "UPDATE tblSVOrdersChanges " _
strSQL = strSQL & "SET tblSVOrdersChanges.UserCode = " & nUsercode _
strSQL = strSQL & "tblSVOrdersChanges.ChangeReason = " & strChangeReason _
strSQL = strSQL & "WHERE tblSVOrdersChanges.ChangeDate = " & nChangeDate & ";"

What am I doing wrong?

Thanks,

Ron

 

[Ed2020]

Assuming UserCode is numeric, ChangeReason is a text field and nChangeDate is a date/time field try:

strSQL = "UPDATE tblSVOrdersChanges " _
     strSQL = strSQL & "SET tblSVOrdersChanges.UserCode = " & nUsercode _
     strSQL = strSQL & "tblSVOrdersChanges.ChangeReason = " & """" & strChangeReason & """"_
     strSQL = strSQL & " WHERE tblSVOrdersChanges.ChangeDate = #" & nChangeDate & "#;"

Ed Metcalfe.


Please do not feed the trolls.....

 

[HarleyQuinn]

You need spaces in there and you can do away with the continaution characters

strSQL = "UPDATE tblSVOrdersChanges "
strSQL = strSQL & "SET tblSVOrdersChanges.UserCode = " & nUsercode
strSQL = strSQL & " tblSVOrdersChanges.ChangeReason = " & strChangeReason
strSQL = strSQL & " WHERE tblSVOrdersChanges.ChangeDate = " & nChangeDate & ";"

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Ed2020]

I missed the continuation characters and spaces. Was about to post back but HarleyQuinn beat me to it.

Ed Metcalfe.

Please do not feed the trolls.....

 

[HarleyQuinn]

And I missed the type qualifiers so a combination of our posts should do the trick

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[RonMcIntire]

All:

Thanks for your help. I think I'm getting closer.

However, I'm still getting a "Syntax Error in SVOrdersChanges.ChangeReason."

BTW: all variables are string.

Should there be comma after nUserCode and in & nUsercode & ","

Not sure I understand about the type qualifier but seems like the question goes away if all variables are string.

Ron

 

[Ed2020]

Different data types have different data type qualifiers (the character that appears before and after the value).

Strings have a quote before and after them.

Date/Time values have a # either side of them.

Numeric values have no type qualifier.

BTW: all variables are string.
[/QUOTE

Why? Are you sure they should be? The field names suggest they shouldn't be. What about the fields in the table? What data types are they?

Ed Metcalfe.

Please do not feed the trolls.....

 

[HarleyQuinn]

The type qualifier is to do with the type of field you are trying to update. You have to qualify values in a certain way for particular table data types. You effectivly 'wrap' the value you are using e.g. #19/10/1981#. A few examples (that could be relevant) are:

DataType  Qualifier Example

Text      "" or '   "Hello" or 'Hello'
Number    No qualifier required
Date      #         #19/10/1981#

You could need to use (and yes, you're right, you do need comma's in there, both Ed and I seem to have missed that :

strSQL = "UPDATE tblSVOrdersChanges "
strSQL = strSQL & "SET tblSVOrdersChanges.UserCode = " & nUsercode & ","
strSQL = strSQL & " tblSVOrdersChanges.ChangeReason = '" & strChangeReason & "',"
strSQL = strSQL & " WHERE tblSVOrdersChanges.ChangeDate = #" & nChangeDate & "#;"

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[RonMcIntire]

All:

I'm still getting a runtime error (missing operator) in the ChangeReason statement for some reason. But with what you've told me, I think my solution is near.

You've both helped me a great deal in understanding what I need to do.

Thanks again.

Ron

 

[HarleyQuinn]

Could you show us the query you've currently got?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[RonMcIntire]

If I understand correctly, here are the important parts of the query I built my SQL statement from:

UPDATE tblSVOrdersChanges SET tblSVOrdersChanges.UserCode = "nUserCode", tblSVOrdersChanges.ChangeReason = "nChangeReason"
WHERE (((tblSVOrdersChanges.ChangeDate)="nChangeDate"));

SVOrdersChanges has three fields in question:
UserCode - 8 character TEXT String
ChangeDate - (Date)
ChangeReason - 255 Character TEXT String

nUserCode, strChangeReason and nChangeDate - TEXT box controlls on my form.

I suspect there is an out-of-place character somewhere.

Ron

 

[PHV]

What about this ?

strSQL = "UPDATE tblSVOrdersChanges"
strSQL = strSQL & " SET UserCode='" & Me!nUsercode & "'"
strSQL = strSQL & ",ChangeReason='" & Replace(Me!strChangeReason, "'", "''") & "'"
strSQL = strSQL & " WHERE ChangeDate=#" & Format(Me!nChangeDate, "yyyy-mm-dd") & "#"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886