Update table with Null data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all

I had posted this a month or so ago and Michael Walsh was gracious enough to help. However, I just got back to this project to try it, and was still having a some problems. So forgive me for multi posting.

I have also reduced the fields to make it easier to see the changes…. For me anyway

I have a button that updates a table with data from another table and the form
I want to be able to update even if the fields on the form are Null. Right now this code works only if all fields are populated

TxtNote and txtPO are the fields that I want to be able to leave blank

vdoSql = "INSERT INTO tbOrd (Qty, ItmNum, Notes, PONumber ) values ('" & Me.txtQty.Value & "','" & rs![ItmNum] & "', '" & Me.txtNote.Value & "','" & Me.txtPO.Value & "');

Here is what Mr. Walsh suggested.

&Nz( Me.txtNote.Value, “NULLâ€

I get a validation error when using this. There are no validation rules on the field in question as far as I can tell

Again the code in full

vdoSql = "INSERT INTO tbOrd (Qty, ItmNum, Notes, PONumber ) values ('" & Me.txtQty.Value & "','" & rs![ItmNum] & "', '" &Nz( Me.txtNote.Value, “NULLâ€) & "','" &Nz(Me.txtPO.Value, “NULLâ€) & "');

Thanks all
 
A Null value doesn't need quotes around it, you are still wrapping it in
single quotes, even when the value is Null. Replacing

"Null"

with vbNullString may work.

Just got called away. I'll try something else later if this doesn't work.

--
Wayne Morgan
Microsoft Access MVP


Mark said:
Hello all,

I had posted this a month or so ago and Michael Walsh was gracious enough
Click to expand...
to help. However, I just got back to this project to try it, and was still
having a some problems. So forgive me for multi posting.
I have also reduced the fields to make it easier to see the changes.. For me anyway.

I have a button that updates a table with data from another table and the form.
I want to be able to update even if the fields on the form are Null.
Click to expand...
Right now this code works only if all fields are populated.
TxtNote and txtPO are the fields that I want to be able to leave blank.

vdoSql = "INSERT INTO tbOrd (Qty, ItmNum, Notes, PONumber ) values ('" &
Click to expand...
Me.txtQty.Value & "','" & rs![ItmNum] & "', '" & Me.txtNote.Value & "','" &
Me.txtPO.Value & "');"
Here is what Mr. Walsh suggested.

&Nz( Me.txtNote.Value, "NULL")

I get a validation error when using this. There are no validation rules
Click to expand...
on the field in question as far as I can tell.
Again the code in full:

vdoSql = "INSERT INTO tbOrd (Qty, ItmNum, Notes, PONumber ) values ('" &
Click to expand...
Me.txtQty.Value & "','" & rs![ItmNum] & "', '" &Nz( Me.txtNote.Value,
"NULL") & "','" &Nz(Me.txtPO.Value, "NULL") & "');"
 
Yes, that will give the same results. What you have is

'" &Nz( Me.txtNote.Value, "NULL") & "'

which results in
'Null'

Basically, it is trying to put the word Null in the field instead of a Null
value.

Example from the Immediate window:
?"INSERT INTO tbOrd (Qty, ItmNum, Notes, PONumber) values ('" & 1 & "','" &
2 & "', '" & Nz(Null, "NULL") & "','" & Nz(Null, "NULL") & "');"

Results in:
INSERT INTO tbOrd (Qty, ItmNum, Notes, PONumber) values ('1','2', '','');

Note the 2 single quotes around the 2 values that should be Null. This is
actually a zero length string.

Try this:
"INSERT INTO tbOrd (Qty, ItmNum, Notes, PONumber) values ('" &
Me.txtQty.Value & "','" & rs![ItmNum] & "', " &
IIf(IsNull(Me.txtNote.Value), NULL, "'" & Me.txtNote.Value & "'") & ",'" &
Nz(IsNull(Me.txtPO.Value), NULL, "'" & Me.txtPO.Value & "'") & ");"

Note that the single quotes have been moved to within the IIf statement so
that they only show up when the value isn't Null.
 
NOTE that inserting a null into a field or leaving it null is never a good
idea.

zero, an empty string or even the word "Null" allows Access to actually use
the field.
NULL is not any of the above.

1000 + 0 = 1000, 1000 + NULL = NULL and that's hard to explain to the
accountants.


Wayne Morgan said:
Yes, that will give the same results. What you have is

'" &Nz( Me.txtNote.Value, "NULL") & "'

which results in
'Null'

Basically, it is trying to put the word Null in the field instead of a Null
value.

Example from the Immediate window:
?"INSERT INTO tbOrd (Qty, ItmNum, Notes, PONumber) values ('" & 1 & "','" &
2 & "', '" & Nz(Null, "NULL") & "','" & Nz(Null, "NULL") & "');"

Results in:
INSERT INTO tbOrd (Qty, ItmNum, Notes, PONumber) values ('1','2', '','');

Note the 2 single quotes around the 2 values that should be Null. This is
actually a zero length string.

Try this:
"INSERT INTO tbOrd (Qty, ItmNum, Notes, PONumber) values ('" &
Me.txtQty.Value & "','" & rs![ItmNum] & "', " &
IIf(IsNull(Me.txtNote.Value), NULL, "'" & Me.txtNote.Value & "'") & ",'" &
Nz(IsNull(Me.txtPO.Value), NULL, "'" & Me.txtPO.Value & "'") & ");"

Note that the single quotes have been moved to within the IIf statement so
that they only show up when the value isn't Null.

--
Wayne Morgan
Microsoft Access MVP


Mark said:
I tried the:
Nz(Me.txtNote.Value, Null)
But, with the same results.
Click to expand...
Click to expand...
 
NOTE that inserting a null into a field or leaving it null is never a good
idea.

zero, an empty string or even the word "Null" allows Access to actually use
the field.
NULL is not any of the above.

1000 + 0 = 1000, 1000 + NULL = NULL and that's hard to explain to the
accountants.
Click to expand...

I would have to disagree. Null indicates that there is no value which can
be very useful. I would think you would want to know that there is an error
in a calculation rather than replace the error with an invalid value. (You
can catch the problem before it gets to the accountants rather than giving
them the wrong information.)

-My 2 cents worth

LaVern
 
Back
Top