This is odd!

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a query which includes this expression
FirstName: IIf(IsNull([Preferred Name]),"Member",[Preferred Name])

What is odd is that when I run the query some records where the Preferred
name is blank show Member but others do not. I can't see anything different
in the records and cannot understand why the expression works for some
records and not others. Can anyone help?

TIA
Tony Williams
 
Tony,

Just guessing here, but it may be that field [Preferred Name] has one or
more spaces in some records, whichyou don't see, but they are not null!
To verify, make a plain select query on the table, and use the following
criterion on the field in question:
Like " " & *
If it returns any records, then you have records with spaces.

Another (less likely) case is, if you have set the particular field's Allow
Zero Length property to Yes in the table design (the default is No), then it
may be that in some records the field value is set to a zero length string,
which, again, is not the same as Null.
To verify, make a plain select query on the table, and use the following
criterion on the field in question:
""
If it returns any records, then you have records with zero length strings.

HTH,
Nikos
 
Tony

To add to what Nikos has offered, you could "embellish" your IIF() statement
to work around the possibility of a zero-length string. I'm not certain,
but I believe Access doesn't store a space/spaces only in a text field, so
you could try something like:

IIF(Nz([Preferred Name],"")="","Member",[Preferred Name])

This way, either a Null or a "" (zls) will result in "Member".
 
Thanks Nicos I'll investigate that
Tony
Nikos Yannacopoulos said:
Tony,

Just guessing here, but it may be that field [Preferred Name] has one or
more spaces in some records, whichyou don't see, but they are not null!
To verify, make a plain select query on the table, and use the following
criterion on the field in question:
Like " " & *
If it returns any records, then you have records with spaces.

Another (less likely) case is, if you have set the particular field's Allow
Zero Length property to Yes in the table design (the default is No), then it
may be that in some records the field value is set to a zero length string,
which, again, is not the same as Null.
To verify, make a plain select query on the table, and use the following
criterion on the field in question:
""
If it returns any records, then you have records with zero length strings.

HTH,
Nikos


Tony Williams said:
I have a query which includes this expression
FirstName: IIf(IsNull([Preferred Name]),"Member",[Preferred Name])

What is odd is that when I run the query some records where the Preferred
name is blank show Member but others do not. I can't see anything different
in the records and cannot understand why the expression works for some
records and not others. Can anyone help?

TIA
Tony Williams
Click to expand...
Click to expand...
 
Jeff,
As Far As I Know, Access doesn't store trailing spaces, BUT if you are using
linked tables to Excel or SQL (with the right field types) you can end up with
multiple spaces stored at the end of a string.

In that case, the IIF gets a little more complex in that you now have to TRIM
the field in addition to adding a zero-length string to it.

All I can say is
Diversity is a wonderful thing - it makes life interesting. But it also makes
programming a bit more difficult.
 
?!(TIC) Folks actually use Access with data other than Jet/Access back-ends?
<g>

Jeff

John Spencer (MVP) said:
Jeff,
As Far As I Know, Access doesn't store trailing spaces, BUT if you are using
linked tables to Excel or SQL (with the right field types) you can end up with
multiple spaces stored at the end of a string.

In that case, the IIF gets a little more complex in that you now have to TRIM
the field in addition to adding a zero-length string to it.

All I can say is
Diversity is a wonderful thing - it makes life interesting. But it also makes
programming a bit more difficult.

Jeff said:
Tony

To add to what Nikos has offered, you could "embellish" your IIF() statement
to work around the possibility of a zero-length string. I'm not certain,
but I believe Access doesn't store a space/spaces only in a text field, so
you could try something like:

IIF(Nz([Preferred Name],"")="","Member",[Preferred Name])

This way, either a Null or a "" (zls) will result in "Member".

--
Good luck

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
 
Back
Top