This is odd!

[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

[WindAndWaves]

My initial gut reaction is that some of the strings are "" while others are
Null.

I would try to use IIf(nz(([Preferred Name]), "") = "","Member",[Preferred
Name])

Let me know how you get on.
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

[Tony Williams]

Thanks I get an syntax error message "entered an operand without an
operator" Any ideas?
Thanks
Tony
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:cy********************@news.xtra.co.nz...

My initial gut reaction is that some of the strings are "" while others are Null.

I would try to use IIf(nz(([Preferred Name]), "") = "","Member",[Preferred
Name])

Let me know how you get on.
---
Please immediately let us know (by phone or return email) if (a) this email contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

[WindAndWaves]

Lets try it again:

the basic is: [Preferred Name]
then try this: NZ([Preferred Name], "")
then this: iif(len([Preferred Name])>0, "member", [Preferred Name]
next: iif(NZ([Preferred Name], "")="", "member", [Preferred Name])

if you slowly make the syntax more complex, you can find out where it goes
wrong... Often something very stupid.

Let me know how you go.

Ciao Chow

Nicolaas

"Tony Williams" <[e-mail]> wrote in message
news:c9**********@sparta.btinternet.com...

Thanks I get an syntax error message "entered an operand without an
operator" Any ideas?
Thanks
Tony
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:cy********************@news.xtra.co.nz...

My initial gut reaction is that some of the strings are "" while others

are

Null.

I would try to use IIf(nz(([Preferred Name]), "") = "","Member",[Preferred Name])

Let me know how you get on.
---
Please immediately let us know (by phone or return email) if (a) this

email

contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

[Wayne Gillespie]

On Thu, 3 Jun 2004 10:50:59 +0000 (UTC), "Tony Williams" <[e-mail]> wrote:

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

FirstName: IIf(Len([Preferred Name] & "")=0,"Member",[Preferred Name])
Wayne Gillespie
Gosford NSW Australia

[Terry Kreft]

IIF(Len([Preferred Name] & "") > 0, [Preferred Name], "Member")

appending the empty string will be quicker than using the NZ function and
testing the length of the string is faster than testing string equivalence.
--
Terry Kreft
MVP Microsoft Access
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:xQ********************@news.xtra.co.nz...

Lets try it again:

the basic is: [Preferred Name]
then try this: NZ([Preferred Name], "")
then this: iif(len([Preferred Name])>0, "member", [Preferred Name]
next: iif(NZ([Preferred Name], "")="", "member", [Preferred Name])

if you slowly make the syntax more complex, you can find out where it goes
wrong... Often something very stupid.

Let me know how you go.

Ciao Chow

Nicolaas

"Tony Williams" <[e-mail]> wrote in message
news:c9**********@sparta.btinternet.com...

Thanks I get an syntax error message "entered an operand without an
operator" Any ideas?
Thanks
Tony
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:cy********************@news.xtra.co.nz...

My initial gut reaction is that some of the strings are "" while

others

are

Null.

I would try to use IIf(nz(([Preferred Name]), "") =

"","Member",[Preferred Name])

Let me know how you get on.
---
Please immediately let us know (by phone or return email) if (a) this

email

contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

---
Please immediately let us know (by phone or return email) if (a) this

email contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

[Tony Williams]

Thanks but used Terry's suggestion because it seemed to work OK. Thanks
again for your patience and input
Tony
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:xQ********************@news.xtra.co.nz...

Lets try it again:

the basic is: [Preferred Name]
then try this: NZ([Preferred Name], "")
then this: iif(len([Preferred Name])>0, "member", [Preferred Name]
next: iif(NZ([Preferred Name], "")="", "member", [Preferred Name])

if you slowly make the syntax more complex, you can find out where it goes
wrong... Often something very stupid.

Let me know how you go.

Ciao Chow

Nicolaas

"Tony Williams" <[e-mail]> wrote in message
news:c9**********@sparta.btinternet.com...

Thanks I get an syntax error message "entered an operand without an
operator" Any ideas?
Thanks
Tony
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:cy********************@news.xtra.co.nz...

My initial gut reaction is that some of the strings are "" while

others

are

Null.

I would try to use IIf(nz(([Preferred Name]), "") =

"","Member",[Preferred Name])

Let me know how you get on.
---
Please immediately let us know (by phone or return email) if (a) this

email

contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

---
Please immediately let us know (by phone or return email) if (a) this

email contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

[Tony Williams]

Thanks Terry worked fine
Tony
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:YJ********************@karoo.co.uk...

IIF(Len([Preferred Name] & "") > 0, [Preferred Name], "Member")

appending the empty string will be quicker than using the NZ function and
testing the length of the string is faster than testing string equivalence.

--
Terry Kreft
MVP Microsoft Access
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:xQ********************@news.xtra.co.nz...

Lets try it again:

the basic is: [Preferred Name]
then try this: NZ([Preferred Name], "")
then this: iif(len([Preferred Name])>0, "member", [Preferred Name]
next: iif(NZ([Preferred Name], "")="", "member", [Preferred Name])

if you slowly make the syntax more complex, you can find out where it goes

wrong... Often something very stupid.

Let me know how you go.

Ciao Chow

Nicolaas

"Tony Williams" <[e-mail]> wrote in message
news:c9**********@sparta.btinternet.com...

Thanks I get an syntax error message "entered an operand without an
operator" Any ideas?
Thanks
Tony
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:cy********************@news.xtra.co.nz...
> My initial gut reaction is that some of the strings are "" while

others are
> Null.
>
> I would try to use IIf(nz(([Preferred Name]), "") =

"","Member",[Preferred

> Name])
>
> Let me know how you get on.
>
>
> ---
> Please immediately let us know (by phone or return email) if (a) this email
> contains a virus
> (b) you are not the intended recipient
> (c) you consider this email to be spam.
> We have done our utmost to make sure that
> none of the above are applicable. THANK YOU
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004
>
>

---
Please immediately let us know (by phone or return email) if (a) this

email

contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

[David W. Fenton]

"Tony Williams" <[e-mail]> wrote in
news:c9**********@titan.btinternet.com:

Thanks Terry worked fine

Why not clean up your data and get rid of the zero-length strings?
If you did that, you could replace:

IIF(Len([Preferred Name] & "") > 0, [Preferred Name], "Member")

with:

Nz([Preferred Name], "Member")

That is one function call in place of two.

I simply see no reason to ever store zero-length strings. They make
it much harder to tell if a field is empty -- without them, all you
have to do is test for Null, but with them, you have to concatenate
with a ZLS and then test the length of the result.

Unless the ZLS has meaning that is different from a Null, get rid of
them.

The default in Access tables is to disallow ZLS's, and this is the
best way to do it. If you're changing your table structure to
accomodate ZLS's, then the problem is with whatever you're doing
that's requiring you to write ZLS's. I've seen ignorant website
programmers who don't know how to write server-side code that
doesn't return ZLS's who allow ZLS's in tables instead of fixing
their code, but in my experience, that's just a result of ignorance.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[WindAndWaves]

Those are all really good points.
Than Zero Length String (ZLS) thing is useless in most cases, because the
normal users of the databases we make will never understand the difference
and why would they.

However, I would say, from a big picture point of view it would be easier to
make everything ZLS, because in that case, the data is similar to the other
records for the same field, which do have strings in them. With a null
value, you sometimes get strange errors and the like.

Therefore, I seem to have to use the NZ function (being from New Zealand,
that may not be such a bad thing), but it would be easier if I could write
if .... = "", instead of if NZ(..., "") = ""

Am i right with these ideas?

thank you

Nicolaas

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"Tony Williams" <[e-mail]> wrote in
news:c9**********@titan.btinternet.com:

Thanks Terry worked fine

Why not clean up your data and get rid of the zero-length strings?
If you did that, you could replace:

IIF(Len([Preferred Name] & "") > 0, [Preferred Name], "Member")

with:

Nz([Preferred Name], "Member")

That is one function call in place of two.

I simply see no reason to ever store zero-length strings. They make
it much harder to tell if a field is empty -- without them, all you
have to do is test for Null, but with them, you have to concatenate
with a ZLS and then test the length of the result.

Unless the ZLS has meaning that is different from a Null, get rid of
them.

The default in Access tables is to disallow ZLS's, and this is the
best way to do it. If you're changing your table structure to
accomodate ZLS's, then the problem is with whatever you're doing
that's requiring you to write ZLS's. I've seen ignorant website
programmers who don't know how to write server-side code that
doesn't return ZLS's who allow ZLS's in tables instead of fixing
their code, but in my experience, that's just a result of ignorance.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004

[Terry Kreft]

The reason for storing Nulls as as opposed to ZLS is that a Null is meant to
represent a piece of data which may exist but is unknown as opposed to a ZLS
which represents a piece of data which is known not to exist.

Having said that I've yet to implement a design where the customer cared
about the difference, on the most part, they could just as well be ZLS if
they're Null.
--
Terry Kreft
MVP Microsoft Access
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"Tony Williams" <[e-mail]> wrote in
news:c9**********@titan.btinternet.com:

Thanks Terry worked fine

Why not clean up your data and get rid of the zero-length strings?
If you did that, you could replace:

IIF(Len([Preferred Name] & "") > 0, [Preferred Name], "Member")

with:

Nz([Preferred Name], "Member")

That is one function call in place of two.

I simply see no reason to ever store zero-length strings. They make
it much harder to tell if a field is empty -- without them, all you
have to do is test for Null, but with them, you have to concatenate
with a ZLS and then test the length of the result.

Unless the ZLS has meaning that is different from a Null, get rid of
them.

The default in Access tables is to disallow ZLS's, and this is the
best way to do it. If you're changing your table structure to
accomodate ZLS's, then the problem is with whatever you're doing
that's requiring you to write ZLS's. I've seen ignorant website
programmers who don't know how to write server-side code that
doesn't return ZLS's who allow ZLS's in tables instead of fixing
their code, but in my experience, that's just a result of ignorance.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"WindAndWaves" <ac****@ngaru.com> wrote in
news:DH*************@news.xtra.co.nz:

Than Zero Length String (ZLS) thing is useless in most cases,
because the normal users of the databases we make will never
understand the difference and why would they.
Well, it's useful only if you've decided to treat ZLS as having a
meaning different from Null. If, for instance, you use Null to
indicate a field value that has not been entered, and ZLS to
indicate that the user has entered an empty value into the field
(that way you know that the value is known to be empty, as opposed
to not knowing whether nothing has been entered at all or that the
value really is empty). In some scenarios, this could be a useful
distinction.

But I've never encountered such a situation in my 8 years of Access
programming.
However, I would say, from a big picture point of view it would be
easier to make everything ZLS, because in that case, the data is
similar to the other records for the same field, which do have
strings in them. With a null value, you sometimes get strange
errors and the like.
What kind of errors? I've never seen any?

Have you read Allen Browne's articles on Nulls?

http://members.iinet.net.au/~allenbrowne/casu-12.html
http://members.iinet.net.au/~allenbrowne/casu-11.html

If you don't know the information in those articles, then you're not
really equipped to make an evaluation of the utility of Nulls.
Therefore, I seem to have to use the NZ function (being from New
Zealand, that may not be such a bad thing), but it would be easier
if I could write if .... = "", instead of if NZ(..., "") = ""

Am i right with these ideas?

I'm not sure what you want to do here.

Yes, if you converted all Nulls to ZLS, then you could test for ="".
You could do a one-time query to update all your Null fields to ZLS,
and set the default value of the field to a ZLS, and then that would
be fixed.

I don't know what happens, though, if a Null gets explicitly written
to the field -- I don't know if deleting an existing value in a form
control would write a Null to the field or leave it as ZLS. I
suspect you'd end up with a Null, so you'd end up again with a mix
of ZLS and Null.

So, it seems easiest to disallow ZLS and rely on Null.

It could very well be that there are indexing advantages to this,
but I'm not certain.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[WindAndWaves]

thank you both for your comments on this.

I actually have a database that is full of ZLS vs NULL distinctions.

For example, when people take booking, they have to complete a form which,
among other things, asks them where people are from.

the default entry is to be entered (null) and the last option on the list
not known (ZLS). There is quite a meaningfull difference between these two.

e.g. make a list of all the records that have not been entered yet

make a list of the unknown ones and address (e.g. how can we find out)

From a statistical point of view it also makes some difference.

Having said that, it is a lookup so 0 = to be entered and 255 = unknown.

Thank you once more for your comments


---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 02/06/2004