Updating null fields after import

[jsqrd]

Need help with subject issue for a MS Access (2003) module.
Specifically the following code will not execute.

Public Function UDBlankXYZ()
DoCmd.RunSQL "Update tblSales, Set tblSales.Cust =
IIf(IsNull([Cust]), "XYZ", [Cust]"
End Function

Error ("Expect end of statement.") trips on the parenthesis around
XYZ.
What is the proper syntax to specify the string I wish to insert?

 

[jsqrd]

Need help with subject issue for a MS Access (2003) module.
Specifically the following code will not execute.

Public Function UDBlankXYZ()
DoCmd.RunSQL "Update tblSales, Set tblSales.Cust =
IIf(IsNull([Cust]), "XYZ", [Cust]"
End Function

Error ("Expect end of statement.") trips on the parenthesis around
XYZ.
What is the proper syntax to specify the string I wish to insert?

My bad...

....trips on the APOSTROPHES around XYZ.

 

[Douglas J. Steele]

DoCmd.RunSQL "Update tblSales " & _
"Set tblSales.Cust =IIf(IsNull([Cust]), ""XYZ"", [Cust])"

although I think it makes more sense to use

DoCmd.RunSQL "Update tblSales " & _
"Set tblSales.Cust = ""XYZ"" " & _
"WHERE Cust IS NULL"

Note the two double quotes in a row: that's how you put a single double
quote into a string. Alternatively, you could use:

DoCmd.RunSQL "Update tblSales " & _
"Set tblSales.Cust = 'XYZ' " & _
"WHERE Cust IS NULL"

Finally, if you want to avoid to "You are about to update n records" pop-up
(and if you want a trappable error generated if something goes wrong), use

CurrentDb.Execute "Update tblSales " & _
"Set tblSales.Cust = 'XYZ' " & _
"WHERE Cust IS NULL", dbFailOnError

 

[jsqrd]

DoCmd.RunSQL "Update tblSales " & _
"Set tblSales.Cust =IIf(IsNull([Cust]), ""XYZ"", [Cust])"

although I think it makes more sense to use

DoCmd.RunSQL "Update tblSales " & _
"Set tblSales.Cust = ""XYZ"" " & _
"WHERE Cust IS NULL"

Note the two double quotes in a row: that's how you put a single double
quote into a string. Alternatively, you could use:

DoCmd.RunSQL "Update tblSales " & _
"Set tblSales.Cust = 'XYZ' " & _
"WHERE Cust IS NULL"

Finally, if you want to avoid to "You are about to update n records" pop-up
(and if you want a trappable error generated if something goes wrong), use

CurrentDb.Execute "Update tblSales " & _
"Set tblSales.Cust = 'XYZ' " & _
"WHERE Cust IS NULL", dbFailOnError

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Need help with subject issue for a MS Access (2003) module.
Specifically the following code will not execute.

Public Function UDBlankXYZ()
DoCmd.RunSQL "Update tblSales, Set tblSales.Cust =
IIf(IsNull([Cust]), "XYZ", [Cust]"
End Function

Error ("Expect end of statement.") trips on the parenthesis around
XYZ.
What is the proper syntax to specify the string I wish to insert?

Thanks for your timely and expert help!

 

[Douglas J. Steele]

Need help with subject issue for a MS Access (2003) module.
Specifically the following code will not execute.

Public Function UDBlankXYZ()
DoCmd.RunSQL "Update tblSales, Set tblSales.Cust =
IIf(IsNull([Cust]), "XYZ", [Cust]"
End Function

Error ("Expect end of statement.") trips on the parenthesis around
XYZ.
What is the proper syntax to specify the string I wish to insert?

My bad...

...trips on the APOSTROPHES around XYZ.

That wasn't your only mistake. The comma between tblSales and Set is wrong,
and you're missing the closing parenthesis for the IIf statement.

 

[jsqrd]

DoCmd.RunSQL "Update tblSales " & _
"Set tblSales.Cust =IIf(IsNull([Cust]), ""XYZ"", [Cust])"

although I think it makes more sense to use

DoCmd.RunSQL "Update tblSales " & _
"Set tblSales.Cust = ""XYZ"" " & _
"WHERE Cust IS NULL"

Note the two double quotes in a row: that's how you put a single double
quote into a string. Alternatively, you could use:

DoCmd.RunSQL "Update tblSales " & _
"Set tblSales.Cust = 'XYZ' " & _
"WHERE Cust IS NULL"

Finally, if you want to avoid to "You are about to update n records" pop-up
(and if you want a trappable error generated if something goes wrong), use

CurrentDb.Execute "Update tblSales " & _
"Set tblSales.Cust = 'XYZ' " & _
"WHERE Cust IS NULL", dbFailOnError

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Need help with subject issue for a MS Access (2003) module.
Specifically the following code will not execute.

Public Function UDBlankXYZ()
DoCmd.RunSQL "Update tblSales, Set tblSales.Cust =
IIf(IsNull([Cust]), "XYZ", [Cust]"
End Function

Error ("Expect end of statement.") trips on the parenthesis around
XYZ.
What is the proper syntax to specify the string I wish to insert?

Can ou provide insight to what the syntax would be if one wanted to
use an array as opposed to a leteral?
Specificall see "aFName[intArrayCtr" in the following.

CurrentDb.Execute "Update tblSales " & _
"Set tblSales.Cust = aFName(intArrayCtr) " & _
"WHERE Cust IS NULL", dbFailOnError

 

[Douglas J. Steele]

When running queries, Access knows nothing about variables. The reference to
the variable must be put outside of the quotes. Assuming that Cust is a text
field, try:

CurrentDb.Execute "Update tblSales " & _
"Set tblSales.Cust = " & Chr(34) & aFName(intArrayCtr) & Chr(34) & _
"WHERE Cust IS NULL", dbFailOnError

If it's numeric, just use

CurrentDb.Execute "Update tblSales " & _
"Set tblSales.Cust = " & aFName(intArrayCtr) & _
"WHERE Cust IS NULL", dbFailOnError