Re: Set value as null or ""?
Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance
"Nick 'The database Guy'" <nick.mcmillen@xxxxxxxx> wrote in message
news:1154679095.137388.317180@xxxxxxxxxxxxxxxxxxxxxxxxxxxx
Just a small point Dirk, I have found that if you use 'If Nz([field
name]) = ""' Access will not differenciate between Null and a zero
length string.
Absolutely right, because you're converting any Nulls in the field to a
zero-length string before making your comparison. That's one method,
and another is just to concatenate the field to a ZLS before comparing:
If ([field] & "") = "" Then
or (my favorite)
If Len([field] & vbNullString) = 0 Then
But all of those add overhead I'd rather avoid, and the possibility of a
ZLS in the field prevents the use of SQL criteria like
WHERE [field] IS NULL
to find all the "empty" fields.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
.
Relevant Pages
- Re: Allow Zero Length
... In general, you should decide whether a field can be left blank as most field should, or if the field must contain data If you then want to allow a zero-length string as a valid data, they you should set Allow Zero Length to Yes. ... So if you know that someone has no phone number, you could represent that as a ZLS, whereas a Null would mean that we don't know if someone has a phone number. ... In practice, there's no visible difference to the end user between a ZLS and a Null, so it is is rarely useful to allow zero-length strings in your database. ...
(microsoft.public.access.tablesdbdesign) - Re: Allow Zero Length
... it is a zero-length string ... A zero length string is not the same thing as a Null: ... know that someone has no phone number, you could represent that as a ZLS, ... Allen Browne - Microsoft MVP. ...
(microsoft.public.access.tablesdbdesign) - Re: Problems moving SQL to Code using Mark Plumpton method
... The text boxes I tested are unbound. ... The Allow Zero Length is not a factor: it's just that to demonstrate that JET matches the Null text box to the zero-length-string entries, I needed some records taht contained a zls. ... The problem seems to be in the way the Expression Service evaluates the Null as being a zero-length string. ...
(microsoft.public.access.forms) - Re: Problems moving SQL to Code using Mark Plumpton method
... JET misinterprets the Null text box as a zero-length string. ... then the query returns the zero-length records and not the nulls. ... It ought not to return the zls records: they do not match the Null text box. ... JET resolves it to a ZLS instead of recognising the Null. ...
(microsoft.public.access.forms) - Re: Oracle NULL vs revisited
... the financial systems we dealt with ... one of the transforms we did was to "clean" invoice ... Nulls would have been nothing like equivalent ... I agree (I assume 0 refers to a zero-length string). ...
(comp.databases.oracle.server) |
|
