Update Blank Fields Only

[ldiaz]

I want to Update only nulls [Upload_Date] fields, i'm using this code, but it
does not work, please help me.


Private Sub SQLR_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE IDVolatilDBID_lbl " & _
"SET [Upload_Date] = Now()" & _
"WHERE([Upload_Date] = "")"
DoCmd.SetWarnings True
Me.IDVolatilDBID_lbl_SubForm.Requery

End Sub

Thanks in advanced

 

[CompGeek78]

I want to Update only nulls [Upload_Date] fields, i'm using this code, but it
does not work, please help me.

Private Sub SQLR_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE IDVolatilDBID_lbl " & _
"SET [Upload_Date] = Now()" & _
"WHERE([Upload_Date] = "")"
DoCmd.SetWarnings True
Me.IDVolatilDBID_lbl_SubForm.Requery

End Sub

Thanks in advanced

Try using WHERE Upload_Date IS NULL instead of WHERE Upload_Date = "".

This is assuming that Upload_Date is actually set as a Date/Time
field. You are testing for the empty string "" which works for Text
fields, but a Date/Time field that is blank isn't equal to "" it's
null. If Upload_Date is really a string, you may want to make sure
there are no spaces in there by using: WHERE trim(Upload_Date) = "".

Keven Denen

 

[Steve Schapel]

Lorenzo,

Should be:
....
" WHERE([Upload_Date] Is Null)"

(note the need for the space before WHERE, as well as the change to 'Is
Null')

 

[ldiaz]

Got it,

Right now it worked
Thanks

--
Lorenzo Díaz
Cad Technician

Lorenzo,

Should be:
....
" WHERE([Upload_Date] Is Null)"

(note the need for the space before WHERE, as well as the change to 'Is
Null')

--
Steve Schapel, Microsoft Access MVP

I want to Update only nulls [Upload_Date] fields, i'm using this code, but
it
does not work, please help me.


Private Sub SQLR_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE IDVolatilDBID_lbl " & _
"SET [Upload_Date] = Now()" & _
"WHERE([Upload_Date] = "")"
DoCmd.SetWarnings True
Me.IDVolatilDBID_lbl_SubForm.Requery

End Sub

Thanks in advanced

 

[Steve Schapel]

Lorenzo,

Well, now I look again I see there is also a space missing after the word
WHERE as well...

" WHERE ([Upload_Date] Is Null)"

 

[CompGeek78]

Lorenzo,

Should be:
...
" WHERE([Upload_Date] Is Null)"

(note the need for the space before WHERE, as well as the change to 'Is
Null')

--
Steve Schapel, Microsoft Access MVP

I want to Update only nulls [Upload_Date] fields, i'm using this code, but
it
does not work, please help me.

Private Sub SQLR_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE IDVolatilDBID_lbl " & _
"SET [Upload_Date] = Now()" & _
"WHERE([Upload_Date] = "")"
DoCmd.SetWarnings True
Me.IDVolatilDBID_lbl_SubForm.Requery

End Sub

Thanks in advanced

Adding the space before the WHERE is optional in this case. I would
put it in anyways just because it's a good habit to get into, but
because the previous line ends with Now(), the parens makes the space
unnecessary.

Keven Denen

 

[Steve Schapel]

How bizarre!

 

[Steve Schapel]

Keven,

This is not correct. If a text field contains a zero-length string "" then
a criteria of 'Is Null' will not find it. But if the text field is Null,
then a criteria of "" will not find it, and a criteria of 'Is Null' will.

It is therefore important to distinguish between Null and ZLS. Allen Browne
has a good article at
http://www.everythingaccess.com/tut...n-Nothing,-Empty,-Missing,-Null,-Zero-and-ZLS
which may help you to understand it better.

 

[CompGeek78]

Keven,

This is not correct.  If a text field contains a zero-length string "" then
a criteria of 'Is Null' will not find it.  But if the text field is Null,
then a criteria of "" will not find it, and a criteria of 'Is Null' will.

It is therefore important to distinguish between Null and ZLS.  Allen Browne
has a good article athttp://www.everythingaccess.com/tutorials.asp?ID=The-differences-betw...
which may help you to understand it better.

Well, seeing how the default in all versions of Access since 97 allow
ZLS in fields, it is possible that a field can be = "". Most people,
unfortunately, don't take the time to change the property on every
text field in their database to not accept ZLS, so I generally still
test for "" as well as nulls.

Keven Denen

 

[James A. Fortune]

Keven,

This is not correct. If a text field contains a zero-length string ""
then a criteria of 'Is Null' will not find it. But if the text field is
Null, then a criteria of "" will not find it, and a criteria of 'Is
Null' will.

It is therefore important to distinguish between Null and ZLS. Allen
Browne has a good article at
http://www.everythingaccess.com/tut...n-Nothing,-Empty,-Missing,-Null,-Zero-and-ZLS
which may help you to understand it better.

What you say is true, but watch out for the following 'gotcha':

In:

http://groups.google.com/group/microsoft.public.access/msg/280e6d26122b3cd7

I wrote:

PARAMETERS Forms!frmSelect!txtName.Value Text,
Forms!frmSelect!cboDeliverable.Value Text;
SELECT tblDepts.DeptID, tblDepts.[Department Name], tblUse.Status,
tblUse.Deliverable FROM tblDepts LEFT JOIN tblUse ON tblDepts.DeptID =
tblUse.DeptID
WHERE IIf(Forms!frmSelect!txtName.Value = "", True, tblDepts.[Department
Name] = Forms!frmSelect!txtName.Value) AND
IIf(Forms!frmSelect!cboDeliverable.Value = "", True,
tblDepts.[Department Name] = Forms!frmSelect!cboDeliverable.Value);

Without the PARAMETERS line, if the value of
Forms!frmSelect!txtName.Value is blank, then
IsNull(Forms!frmSelect!txtName.Value) in a query returns -1. If the
PARAMETERS line is added without changing the rest of the query, then
IsNull(Forms!frmSelect!txtName.Value), or even
'Forms!frmSelect!txtName.Value IS NULL', returns 0, even though there is
no value specified in the textbox. Therefore, using
IIf(Forms!frmSelect!txtName.Value IS NULL, True, ... in the SQL above
will cause the detection of a blank textbox on a form to be incorrect.

James A. Fortune
(e-mail address removed)

 

[Steve Schapel]

James,

I would normally avoid the use of an IIf() function within a WHERE clause.
I would do that query like this:

WHERE (tblDepts.[Department Name] = Forms!frmSelect!txtName OR
Forms!frmSelect!txtName Is Null) AND (tblDepts.[Department Name] =
Forms!frmSelect!cboDeliverable OR Forms!frmSelect!cboDeliverable Is Null)

If there was a chance that the form controls referred to could contain a
zero-length string, that would need to be catered to - though I would
normally avoid such a possibility. And also, there seems something odd
about the query you gave, in that the criteria values delivered by both the
form controls are used against the same field Department Name - so I guess
there may be a typo there somewhere.

But anyway, with the query constructed in this way, I would be surprised if
you encountered to same inconsistency problem with the inclusion or
otherwise of the PARAMETERS clause.

 

[John Spencer]

Since in this specific case the poster seems to be checking a field of
DateTime type it is not appropriate to test for a zero-length string.
The field either has a datetime value or it is null.

So the poster should be using criteria of
Where Upload_Date Is Null



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[James A. Fortune]

James,

I would normally avoid the use of an IIf() function within a WHERE
clause. I would do that query like this:

WHERE (tblDepts.[Department Name] = Forms!frmSelect!txtName OR
Forms!frmSelect!txtName Is Null) AND (tblDepts.[Department Name] =
Forms!frmSelect!cboDeliverable OR Forms!frmSelect!cboDeliverable Is Null)

If there was a chance that the form controls referred to could contain a
zero-length string, that would need to be catered to - though I would
normally avoid such a possibility. And also, there seems something odd
about the query you gave, in that the criteria values delivered by both
the form controls are used against the same field Department Name - so I
guess there may be a typo there somewhere.

But anyway, with the query constructed in this way, I would be surprised
if you encountered to same inconsistency problem with the inclusion or
otherwise of the PARAMETERS clause.

From what I can tell, the IIf() function in the WHERE clause, though
unusual, is completely acceptable the way I used it. In the original
post, a blank field was mentioned specifically as something that had to
be dealt with. The PARAMETERS behavior has nothing to do with the IIf()
function in the WHERE clause, as I just verified with a simple test
query. I.e., whenever you use PARAMETERS to state that a textbox value
on a form is Text, the boolean IS NULL or IsNull() value returned will
ALWAYS be False with respect to the textbox value regardless of the
value on the form. I just verified that also with another test query in
A97, although that behavior might be different in later versions. But
you are correct about the field name error -- I didn't change the field
name when I cut and paste the Deliverable part.

James A. Fortune
(e-mail address removed)

 

[Steve Schapel]

James,

Ok, thank you very much. That is a very interesting anomaly, which I had
never heard of before. Did you see it referred to elsewhere, or did you
"discover" it yourself?

 

[James A. Fortune]

James,

Ok, thank you very much. That is a very interesting anomaly, which I
had never heard of before. Did you see it referred to elsewhere, or did
you "discover" it yourself?

I don't remember seeing it referred to anywhere else. I discovered that
anomaly when testing out the query I designed with the IIf() function in
the WHERE criteria that I cited earlier. I got the wrong results (at
least to my way of thinking) while testing -- to my surprise -- when I
checked the IS NULL after putting in the PARAMETERS piece so I played
with the SQL some more to make sure it didn't behave the way I
originally expected. Many of us like defining the data types of
parameters in SQL, especially when they come from form controls, so I
felt it was a good idea to alert everyone that the behavior they get
with Null values in form controls might not be the behavior they expect.
I don't consider it a bug, just something that must be understood .

James A. Fortune
(e-mail address removed)