VBA Query - I just can't get it to work 100%

[Lateral]

Hi guys

I have spent the last 6 hours trying to get this VBA Query to work and it's about 98%...

Here is the code I am using (It's not pretty but it's the best I can do!):

mSerialNumber = DLookup("SerialNumber", "Workorders", "WorkorderID = " & Nz(Me.WorkorderID, 0) & "")

mSerialNumberSlash = mSerialNumber & mSeperator

mSerialNumberFinal = mSerialNumberSlash & mOrderNo


MsgBox "Your serial number is " & mSerialNumber & "."


MsgBox "Your serial number final with a space is " & mSerialNumberFinal & "."


sql = "Update Workorders SET [SerialNumber] = " & mSerialNumberFinal & " WHERE [WorkOrderID] = " & Me.WorkorderID & ";"


I am setting my variables correctly and I can see them via the Msgbox's I am displaying but I'm having stress with how to correctly code the mSerialNumberFinal variable in the SQL statement. This variable should contain some spaces but the spaces are being stripped out....

Any help is much appreciated.

Regards
Greg

 

[Minty]

I would build your Final serial number in one go, then add a debug.print sql after you create you sql string so you can see what the string is.

 

[Lateral]

Hi Minty,

You're a bloody legend!

It took me a little while to understand what you were saying (I'm a newbie) but when I added the Debug.Print code I could see that the SQL code was :

Update Workorders SET [SerialNumber] = 44444 261 WHERE [WorkOrderID] = 579;

I thought then that the variable needed to be surrounding by single quotes such as:

sql = "Update Workorders SET [SerialNumber] = '" & mSerialNumberFinal & "' WHERE [WorkOrderID] = " & Me.WorkorderID & ";"

and problem solved!!!!

Sensational!!

Thanks mate

Regards
Greg

 

[Minty]

Debug.print is very handy in these situations, sometimes what you think you should get and what you are getting are two different things
Glad to have helped.

 

[Lateral]

Yep, I'll use the debug.print much more now that I am aware of it.

Thanks again.

Regards
Greg

 

[James Deckert]

Another option which does the same thing would be for you to set a breakpoint after the sql= statement and look at the statement in debug.
not better, just different