Updating null fields after import

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jsqrd@comcast.net - 31 Jan 2007 14:31 GMT

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?

Reply to this Message

jsqrd@comcast.net - 31 Jan 2007 14:53 GMT

On Jan 31, 9:31 am, j...@comcast.net wrote:

> Need help with subject issue for a MS Access (2003) module.
> Specifically the following code will not execute.
[quoted text clipped - 7 lines]
> XYZ.
> What is the proper syntax to specify the string I wish to insert?

My bad...

...trips on the APOSTROPHES around XYZ.

Reply to this Message

Douglas J. Steele - 31 Jan 2007 15:12 GMT

> On Jan 31, 9:31 am, j...@comcast.net wrote:
>> Need help with subject issue for a MS Access (2003) module.
[quoted text clipped - 12 lines]
>
> ...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.

Signature

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

Reply to this Message

Douglas J. Steele - 31 Jan 2007 14:56 GMT

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

Signature

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

Reply to this Message

jsqrd@comcast.net - 31 Jan 2007 15:08 GMT

On Jan 31, 9:56 am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

> DoCmd.RunSQL "Update tblSales " & _
> "Set tblSales.Cust =IIf(IsNull([Cust]), ""XYZ"", [Cust])"
[quoted text clipped - 38 lines]
> > XYZ.
> > What is the proper syntax to specify the string I wish to insert?

Thanks for your timely and expert help!

Reply to this Message

jsqrd@comcast.net - 31 Jan 2007 21:21 GMT

On Jan 31, 9:56 am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

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

Reply to this Message

Douglas J. Steele - 31 Jan 2007 21:49 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Can ou provide insight to what the syntax would be if one wanted to
> use an array as opposed to a leteral?
[quoted text clipped - 3 lines]
> "Set tblSales.Cust = aFName(intArrayCtr) " & _
> "WHERE Cust IS NULL", dbFailOnError

Reply to this Message