I'm having a little problem with this Update statement and have been
toiling away to no good.

Could anyone please correct my syntax. Here's the code:

CurrentDb.Execute "UPDATE JVTable SET JVTable.INV_DATE = " &
DMY(Me.Txt_Date) & ", " _
"JVTable.RcdFm_PdTo = '"
& nz(me.txt_RcdFm_PdTo) & "'" _
"JVTable.Chq_No = '" &
nz(me.txt_Chq_No) & "'" _
"JVTable.Chq_Date = '" &
DMY(Me.Txt_Date) & ", " _
"JVTable.Bank = '" &
nz(me.txt_Bank) & "'" _
"JVTable.Settled_Bill_No
= '" & nz(me.txt_Settled_Bill_No) & "'"

Best Rgds,
Prakash

The string you need will depend on the data types of your various fields.
For a Date/Time field, you need to delimit the value with #. For Text
fields, use quotes. For number fields, you don't need any delimiter.

Additionally, the Nz() is not adequate here. You need the word Null in the
string if the field is null (and so you are trying to insert the Null
value.)

The literal date in the SQL string needs to be in mdy format (not your own
local format.)

It will be easier of you build a string, because you can then examine the
contents to see if it looks right.

The following example demonstrates the different data type, on the
assumption that:
INV_DATE is a Date/Time field, and
RcdFm_PdTo is a Number field, and
Chq_No is a Text field.
Adjust and complete the string to suit.

The example prints the string to the Immediate Window. If it fails, press
Ctrl+G and look at the string. Compare it with a query that does work, and
see where you went wrong.

Here's the incomplete example that illustrates the different data types:

Dim strSql As String
Const strcJetDate "\#mm\/dd\/yyyy\#"
strSql = "UPDATE JVTable SET INV_DATE = " & _
IIf(IsNull(Me.txt_Date), "Null", Format(Me.txt_Date, strcJetDate)) & _
", RcdFm_PdTo = " & _
IIf(IsNull(Me.txt_RcdFm_PdTo), "Null", Me.txt_RcdFm_PdTo) & _
", Chq_No = " & _
IIf(IsNull(Me.txt_Chq_No), "Null", """" & Me.txt_Chq_No & """") & _
....

Debug.Print strSql
CurrentDb.Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<prakashwadhwani (AT) gmail (DOT) com> wrote

On Feb 26, 2:52 pm, "Allen Browne" <AllenBro... (AT) SeeSig (DOT) Invalid> wrote:


Thx Allen !! You've really covered almost all sides here.

Firstly, besides the 2 dates ... they are all text fields (I should've
specified that at the outset ...Sorry !!)
Secondly, my DMY function takes care of the dates by delimiting with
#, etc.

Two questions: (pls excuse my ignorance)
1) You've used the word "Null" in the iif statement. Does that
actually input the string "Null" into the field or just the NULL
value ?
2) What does the dbFailOnError do ?

I toyed around with your example & tailored it to suit my needs. It
seems to work. Here's my code ...

StrSql = "UPDATE JVTable SET JVTable.INV_DATE = " & _
IIf(IsNull(Me.Txt_Date),
"Null", DMY(Me.Txt_Date)) & ", " & _
"JVTable.RcdFm_PdTo = " & _

IIf(IsNull(Me.txt_RcdFm_PdTo), "Null", """" & Me.txt_RcdFm_PdTo &
"""") & ", " & _
"JVTable.Chq_No = " & _
IIf(IsNull(Me.txt_Chq_No),
"Null", """" & Me.txt_Chq_No & """") & ", " & _
"JVTable.Chq_Date = " & _

IIf(IsNull(Me.txt_Chq_Date), "Null", DMY(Me.txt_Chq_Date)) & ", " & _
"JVTable.Bank = " & _
IIf(IsNull(Me.txt_Bank),
"Null", """" & Me.txt_Bank & """") & ", " & _
"JVTable.Settled_Bill_No = " & _

IIf(IsNull(Me.txt_Settled_Bill_Nos), "Null", """" &
Me.txt_Settled_Bill_Nos & """")

If I have any further difficulty, I'll post back.

Thank you very, very, much.

Best Rgds,
Prakash.

Replies in-line

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<prakashwadhwani (AT) gmail (DOT) com> wrote

If you want to update a field to Null, you use the literal word Null in the
SQL statement. You can see this for yourself, if you create an Update query
(in query design, and type the word Null into the Update row under one of
the field, and run the query. (Make sure you do this in a table where you
don't need the data!)

Of couse, you could just omit the field from the SQL statement altogether
(assuming it doesn't have a default value), if you found that easier.

dbFailOnError stops the execution of the query and notifies you if there is
an error. There could be lots of reasons for the error, e.g. if you try to
insert Null into a Required field, if a value violates a unique index, or if
someone else is editing a record while you are trying to update it. Without
the dbFailOnError, you would not know that a problem had occurred.

To be really safe, you might want to use a tranaction. There's an example of
that here:
Archive: Move Records to Another Table
at:
http://allenbrowne.com/ser-37.html

No worries.

On Feb 26, 3:58 pm, prakashwadhw... (AT) gmail (DOT) com wrote:



If I leave txt_Chq_Date empty on the form i get the message "Invalid
Use of Null"

After I fill all the fields and try to run the sql i get: "Too few
Parameters. Expected 1."

Can anyone please point out where I'm going wrong ?

Best Rgds,
Prakash.

Post the SQL statement, i.e. results you get from:
Debug.Print strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<prakashwadhwani (AT) gmail (DOT) com> wrote in message

prakashwadhwani (AT) gmail (DOT) com wrote:
If you have something like
Debug.Print strSQL
and it fails, one thing you can do is to create a new query
(Query/New/Design/Close. You now have a blank Query builder.

Now go into the Debug/Immediate window. You'll see the offending strSQL
line. Copy it to the clipboard. Go back the the Query builder and
select View/SQL and paste the code into the query window. Now run it
either from that window or the Design window. This might help track
down the error.

On Feb 26, 4:47 pm, "Allen Browne" <AllenBro... (AT) SeeSig (DOT) Invalid> wrote:

Thank you so very much for all the hand-holding & support. Here's the
result of my SQL from the Debug.Print StrSql window :

UPDATE JVTable SET JVTable.INV_DATE = #2/25/2008#, JVTable.RcdFm_PdTo
= "Mr. Henry Dias", JVTable.Chq_No = "AS123456", JVTable.Chq_Date =
#2/26/2008#, JVTable.Bank = "HSBC", JVTable.Settled_Bill_No = "123,
456, 789"

Nothing seems obvious to me. I hope you can find the offending code.

Best Rgds,
Prakash.

On Feb 26, 4:57 pm, Salad <o... (AT) vinegar (DOT) com> wrote:

Allen, I owe you a BIG Thank You !! I followed your instructions to
the "T" & true enough, the Query Builder highlighted my offending
code.

This was the SQL code:

UPDATE JVTable SET JVTable.INV_DATE = #2/25/2008#, JVTable.RcdFm_PdTo
= "Mr. Henry Dias", JVTable.Chq_No = "AS123456", JVTable.Chq_Date =
#2/26/2008#, JVTable.Bank = "HSBC", JVTable.Settled_Bill_No = "123,
456, 789"

and the problem was:
JVTable.Settled_Bill_No should have been: JVTable.Settled_Bill_Nos

I misses the "s" at the end.

Once again, thank you so very much.
Best Rgds,
Prakash.

On Feb 26, 4:57 pm, Salad <o... (AT) vinegar (DOT) com> wrote:

Sorry Salad ... didn't read properly ... thought that Allen had
answered this thread. Thank you very much. Your suggestion was spot-on
& worked like a charm.

One tiny thing bothering me though ...
If I leave txt_Chq_Date empty on the form, i get the message
"Invalid Use of Null"

How do I correct this ? It's very frustrating.

Thx again.
Best Rgds,
Prakash.