asked on

Update a text value in another table

Hi every one, I have this code below works good if the value is a number (Not text), But I need it to except a Text character as well,

Works good
DoCmd.RunSQL "Update tbl_Stock_Actv Set Addrs3 = 9999 "

This is what I did but I' m having error
DoCmd.RunSQL "Update tbl_Stock_Actv Set Addrs3 ="AA99" "

Shums Faruk

Hi Ronald,

It depends on the data type of your column and variable. You need to build an SQL string which contains appropriate delimiters (quotes for strings, hashes for dates).

So if AA99 (and myVar) are numeric, use

DoCmd.RunSQL "Update tbl_Stock_Actv (AA99) VALUES (" & myVar & ")"

But if AA99 is text (and myvar a string), use

DoCmd.RunSQL "Update tbl_Stock_Actv (AA99) VALUES (""" & myVar & """)"

And if AA99 is a date (and myvar a string), use

DoCmd.RunSQL "Update tbl_Stock_Actv (AA99) VALUES (#" & myVar & "#)"

Enjoy,

SOLUTION

Tusitala

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ronald Malk

ASKER

I couldn't work it out, the value is up to 12 digits numbers and text, and it's bound to a text field in the table
I have used this but didn't work

Private Sub RwcT_DblClick(Cancel As Integer)
Dim myVar As String
DoCmd.RunSQL "Update tbl_Stock_Actv (AA99) VALUES (""" & myVar & """)"
End Sub

SOLUTION

Ronald Malk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shums Faruk

Ronald,

You need to wrap your initial variable in single quote
DoCmd.RunSQL "Update tbl_Stock_Actv Set Addrs3 ="AA99" "
becomes:
DoCmd.RunSQL "Update tbl_Stock_Actv Set Addrs3 ="'AA99"'"

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ronald Malk

ASKER

Thank, I got it working thank you both for the help, ( appreciated )

Shums Faruk

Ronald,

Have you tried my last revision?

Ronald Malk

ASKER

Thank, I got it working thank you both for the help, ( appreciated )

Shums Faruk

You're Welcome Ronald! Glad I was able to help.