Issue with IIF[fieldname] is not null statement

  • Thread starter Thread starter Sue Compelling
  • Start date Start date
S

Sue Compelling

Hi ALL

I get an error message on this expressiin stating I have too many arguments
and have been unable to correct it.

TIA

StName: IIf(IsNull([SecondName]),IIf([PreferredName]is not null),[FirstName]
& " (" & [PreferredName] & ") " &
[LastName],IIf(IsNull([SecondName]),IIf(IsNull([PreferredName]),[FirstName] &
" " & [LastName],[FirstName] & " " & [SecondName] & " " & [LastName]))))
 
I believe the issue is with your IIf statement syntax. Research the syntax
of "nested IF"

IIF(condition, Return Value, Else)

IIF(condition, Return Value, IIF(condition, Return Value, Else))

IIF(condition, Return Value, IIF(condition, Return Value, IIF(condition,
Retrune Value, Else)))

Hope this helps
 
Sue,

I think your problem is in the second IIF clause.

Change: IIF([PreferredName] is not NULL),
To: IIF([PreferredName] is not NULL,

However, when you look at your logic, you can simplify this considerably.
Basically, your logic says, include the First and Last name in all cases, and
include the [PreferredName] if it is not null, otherwise include the
[SecondName] if it is not null. So you should be able to use:

stName: [FirstName] & (" " + NZ([PreferredName], [SecondName])) & " " &
[LastName]

By concatenating the " " and the NZ( ) function using a + sign, you will get
one of the following conditions:

IF both [PreferredName] and [SecondName] are NULL, you will get
[FirstName] & " " & [LastName]

IF [PreferredName] is NULL and [SecondName] is not, you will get
[FirstName] & " " & [SecondName] & " " & [LastName]

IF [PreferredName] is not NULL and [SecondName] is NULL, you will get
[FirstName] & " " & [PreferredName] & " " & [LastName]

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
This was brilliant Dale - there's no way I would have been able to have
distilled my logic like that.

Two things however:

Using your abbreviated code my Preferred Name doesn't have the brackets
around it eg: Malo (Munji) Fandango - is there a way to do that using your
statement? (It does work with the correction you supplied me)

Do you have a minute to explain what the " " in your statement does?

Kind Regards

Sue
--
Sue Compelling


Dale Fye said:
Sue,

I think your problem is in the second IIF clause.

Change: IIF([PreferredName] is not NULL),
To: IIF([PreferredName] is not NULL,

However, when you look at your logic, you can simplify this considerably.
Basically, your logic says, include the First and Last name in all cases, and
include the [PreferredName] if it is not null, otherwise include the
[SecondName] if it is not null. So you should be able to use:

stName: [FirstName] & (" " + NZ([PreferredName], [SecondName])) & " " &
[LastName]

By concatenating the " " and the NZ( ) function using a + sign, you will get
one of the following conditions:

IF both [PreferredName] and [SecondName] are NULL, you will get
[FirstName] & " " & [LastName]

IF [PreferredName] is NULL and [SecondName] is not, you will get
[FirstName] & " " & [SecondName] & " " & [LastName]

IF [PreferredName] is not NULL and [SecondName] is NULL, you will get
[FirstName] & " " & [PreferredName] & " " & [LastName]

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Sue Compelling said:
Hi ALL

I get an error message on this expressiin stating I have too many arguments
and have been unable to correct it.

TIA

StName: IIf(IsNull([SecondName]),IIf([PreferredName]is not null),[FirstName]
& " (" & [PreferredName] & ") " &
[LastName],IIf(IsNull([SecondName]),IIf(IsNull([PreferredName]),[FirstName] &
" " & [LastName],[FirstName] & " " & [SecondName] & " " & [LastName]))))
Click to expand...
Click to expand...
 
Hi Jeff - Thanks for the response, appreciate the structure ...
--
Sue Compelling


Jeff C said:
I believe the issue is with your IIf statement syntax. Research the syntax
of "nested IF"

IIF(condition, Return Value, Else)

IIF(condition, Return Value, IIF(condition, Return Value, Else))

IIF(condition, Return Value, IIF(condition, Return Value, IIF(condition,
Retrune Value, Else)))

Hope this helps


--
Jeff C
Live Well .. Be Happy In All You Do


Sue Compelling said:
Hi ALL

I get an error message on this expressiin stating I have too many arguments
and have been unable to correct it.

TIA

StName: IIf(IsNull([SecondName]),IIf([PreferredName]is not null),[FirstName]
& " (" & [PreferredName] & ") " &
[LastName],IIf(IsNull([SecondName]),IIf(IsNull([PreferredName]),[FirstName] &
" " & [LastName],[FirstName] & " " & [SecondName] & " " & [LastName]))))
Click to expand...
Click to expand...
 
stName: [FirstName] & (" " + NZ("(" + [PreferredName] + ")", [SecondName])) &
" " & [LastName]

Which " " are you talking about?

It's probably the concatenation inside the brackets. When you concatenate
strings, you can either use the & or the + to do the concatenation. If you
use the &, Access will give you what is on the left, tacked onto what is on
the right, regardless of whether the left or right is NULL. If you use the
+, then access will look at both sides of the + and if either value is NULL,
it will return a NULL. So:

"A" & "B" = "AB"
"A" & NULL = "A"
"A" + "B" = "AB"
"A" + NULL = NULL

So, in the modified code I gave you above the stuff inside the NZ( )
function will get evaluated as:

NZ("(" + [PreferredName] + ")", [SecondName])

IF [PreferredName] is NULL then using the + sign to concatenate the "(" and
")" to it will still return a NULL, so [SecondName] would get returned. If
[PreferredName] is not Null, then this new code would return (Munjo) as in
your example.

You can use this technique to your advantage in a lot of situations, but the
one I find most useful is with names, similar to what you are doing.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Sue Compelling said:
This was brilliant Dale - there's no way I would have been able to have
distilled my logic like that.

Two things however:

Using your abbreviated code my Preferred Name doesn't have the brackets
around it eg: Malo (Munji) Fandango - is there a way to do that using your
statement? (It does work with the correction you supplied me)

Do you have a minute to explain what the " " in your statement does?

Kind Regards

Sue
--
Sue Compelling


Dale Fye said:
Sue,

I think your problem is in the second IIF clause.

Change: IIF([PreferredName] is not NULL),
To: IIF([PreferredName] is not NULL,

However, when you look at your logic, you can simplify this considerably.
Basically, your logic says, include the First and Last name in all cases, and
include the [PreferredName] if it is not null, otherwise include the
[SecondName] if it is not null. So you should be able to use:

stName: [FirstName] & (" " + NZ([PreferredName], [SecondName])) & " " &
[LastName]

By concatenating the " " and the NZ( ) function using a + sign, you will get
one of the following conditions:

IF both [PreferredName] and [SecondName] are NULL, you will get
[FirstName] & " " & [LastName]

IF [PreferredName] is NULL and [SecondName] is not, you will get
[FirstName] & " " & [SecondName] & " " & [LastName]

IF [PreferredName] is not NULL and [SecondName] is NULL, you will get
[FirstName] & " " & [PreferredName] & " " & [LastName]

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Sue Compelling said:
Hi ALL

I get an error message on this expressiin stating I have too many arguments
and have been unable to correct it.

TIA

StName: IIf(IsNull([SecondName]),IIf([PreferredName]is not null),[FirstName]
& " (" & [PreferredName] & ") " &
[LastName],IIf(IsNull([SecondName]),IIf(IsNull([PreferredName]),[FirstName] &
" " & [LastName],[FirstName] & " " & [SecondName] & " " & [LastName]))))
Click to expand...
Click to expand...
Click to expand...
 
Hi Dale - you're extremely good at explaining things (thanks for taking the
time) and I've put this response in my favorites as I see myself returning to
it often.

Unfortunately the new code gives me an error when:

Student only has a First and Last Name
Student only has a First and Last and Second Name (ie no Preferred)

AND where student has ALL four names it fails to display the Second Name

It works wherever a student has a Preferred Name Scenario

Help

Lastly - does NULL & "B" = "B"?

TIA

--
Sue Compelling


Dale Fye said:
stName: [FirstName] & (" " + NZ("(" + [PreferredName] + ")", [SecondName])) &
" " & [LastName]

Which " " are you talking about?

It's probably the concatenation inside the brackets. When you concatenate
strings, you can either use the & or the + to do the concatenation. If you
use the &, Access will give you what is on the left, tacked onto what is on
the right, regardless of whether the left or right is NULL. If you use the
+, then access will look at both sides of the + and if either value is NULL,
it will return a NULL. So:

"A" & "B" = "AB"
"A" & NULL = "A"
"A" + "B" = "AB"
"A" + NULL = NULL

So, in the modified code I gave you above the stuff inside the NZ( )
function will get evaluated as:

NZ("(" + [PreferredName] + ")", [SecondName])

IF [PreferredName] is NULL then using the + sign to concatenate the "(" and
")" to it will still return a NULL, so [SecondName] would get returned. If
[PreferredName] is not Null, then this new code would return (Munjo) as in
your example.

You can use this technique to your advantage in a lot of situations, but the
one I find most useful is with names, similar to what you are doing.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Sue Compelling said:
This was brilliant Dale - there's no way I would have been able to have
distilled my logic like that.

Two things however:

Using your abbreviated code my Preferred Name doesn't have the brackets
around it eg: Malo (Munji) Fandango - is there a way to do that using your
statement? (It does work with the correction you supplied me)

Do you have a minute to explain what the " " in your statement does?

Kind Regards

Sue
--
Sue Compelling


Dale Fye said:
Sue,

I think your problem is in the second IIF clause.

Change: IIF([PreferredName] is not NULL),
To: IIF([PreferredName] is not NULL,

However, when you look at your logic, you can simplify this considerably.
Basically, your logic says, include the First and Last name in all cases, and
include the [PreferredName] if it is not null, otherwise include the
[SecondName] if it is not null. So you should be able to use:

stName: [FirstName] & (" " + NZ([PreferredName], [SecondName])) & " " &
[LastName]

By concatenating the " " and the NZ( ) function using a + sign, you will get
one of the following conditions:

IF both [PreferredName] and [SecondName] are NULL, you will get
[FirstName] & " " & [LastName]

IF [PreferredName] is NULL and [SecondName] is not, you will get
[FirstName] & " " & [SecondName] & " " & [LastName]

IF [PreferredName] is not NULL and [SecondName] is NULL, you will get
[FirstName] & " " & [PreferredName] & " " & [LastName]

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi ALL

I get an error message on this expressiin stating I have too many arguments
and have been unable to correct it.

TIA

StName: IIf(IsNull([SecondName]),IIf([PreferredName]is not null),[FirstName]
& " (" & [PreferredName] & ") " &
[LastName],IIf(IsNull([SecondName]),IIf(IsNull([PreferredName]),[FirstName] &
" " & [LastName],[FirstName] & " " & [SecondName] & " " & [LastName]))))
Click to expand...
Click to expand...
Click to expand...
 
Yes, NULL & "B" = "B"

Not sure what is going on, it works in my test database. Can you cut your
code and paste it back here? I'll take a another look at it once I get home.


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Sue Compelling said:
Hi Dale - you're extremely good at explaining things (thanks for taking the
time) and I've put this response in my favorites as I see myself returning to
it often.

Unfortunately the new code gives me an error when:

Student only has a First and Last Name
Student only has a First and Last and Second Name (ie no Preferred)

AND where student has ALL four names it fails to display the Second Name

It works wherever a student has a Preferred Name Scenario

Help

Lastly - does NULL & "B" = "B"?

TIA

--
Sue Compelling


Dale Fye said:
stName: [FirstName] & (" " + NZ("(" + [PreferredName] + ")", [SecondName])) &
" " & [LastName]

Which " " are you talking about?

It's probably the concatenation inside the brackets. When you concatenate
strings, you can either use the & or the + to do the concatenation. If you
use the &, Access will give you what is on the left, tacked onto what is on
the right, regardless of whether the left or right is NULL. If you use the
+, then access will look at both sides of the + and if either value is NULL,
it will return a NULL. So:

"A" & "B" = "AB"
"A" & NULL = "A"
"A" + "B" = "AB"
"A" + NULL = NULL

So, in the modified code I gave you above the stuff inside the NZ( )
function will get evaluated as:

NZ("(" + [PreferredName] + ")", [SecondName])

IF [PreferredName] is NULL then using the + sign to concatenate the "(" and
")" to it will still return a NULL, so [SecondName] would get returned. If
[PreferredName] is not Null, then this new code would return (Munjo) as in
your example.

You can use this technique to your advantage in a lot of situations, but the
one I find most useful is with names, similar to what you are doing.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Sue Compelling said:
This was brilliant Dale - there's no way I would have been able to have
distilled my logic like that.

Two things however:

Using your abbreviated code my Preferred Name doesn't have the brackets
around it eg: Malo (Munji) Fandango - is there a way to do that using your
statement? (It does work with the correction you supplied me)

Do you have a minute to explain what the " " in your statement does?

Kind Regards

Sue
--
Sue Compelling


:

Sue,

I think your problem is in the second IIF clause.

Change: IIF([PreferredName] is not NULL),
To: IIF([PreferredName] is not NULL,

However, when you look at your logic, you can simplify this considerably.
Basically, your logic says, include the First and Last name in all cases, and
include the [PreferredName] if it is not null, otherwise include the
[SecondName] if it is not null. So you should be able to use:

stName: [FirstName] & (" " + NZ([PreferredName], [SecondName])) & " " &
[LastName]

By concatenating the " " and the NZ( ) function using a + sign, you will get
one of the following conditions:

IF both [PreferredName] and [SecondName] are NULL, you will get
[FirstName] & " " & [LastName]

IF [PreferredName] is NULL and [SecondName] is not, you will get
[FirstName] & " " & [SecondName] & " " & [LastName]

IF [PreferredName] is not NULL and [SecondName] is NULL, you will get
[FirstName] & " " & [PreferredName] & " " & [LastName]

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi ALL

I get an error message on this expressiin stating I have too many arguments
and have been unable to correct it.

TIA

StName: IIf(IsNull([SecondName]),IIf([PreferredName]is not null),[FirstName]
& " (" & [PreferredName] & ") " &
[LastName],IIf(IsNull([SecondName]),IIf(IsNull([PreferredName]),[FirstName] &
" " & [LastName],[FirstName] & " " & [SecondName] & " " & [LastName]))))
Click to expand...
Click to expand...
Click to expand...
 
Cheers Dale

The following is the code I use in my QUERY which returns the correct data
and format EXCEPT for where a student has all four names and then it returns
First Preferred and Last (ie drops the second name)


stName: [FirstName] & (" "+NZ("("+[PreferredName]+")",[SecondName])) & " " &
[LastName]


The following is the code I use in my FORM which returns an #error where

Student only has a First and Last Name
Student only has a First and Last and Second Name (ie no Preferred)

AND as above - where student has ALL four names it fails to display the
Second Name

=[FirstName] & (" "+NZ("("+[PreferredName]+")",[SecondName])) & " " &
[LastName]

I believe I'm in for a "growling" by not informing you I was using the code
for both a query and a form field

Speak soon ....
--
Sue Compelling


Dale Fye said:
Yes, NULL & "B" = "B"

Not sure what is going on, it works in my test database. Can you cut your
code and paste it back here? I'll take a another look at it once I get home.


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Sue Compelling said:
Hi Dale - you're extremely good at explaining things (thanks for taking the
time) and I've put this response in my favorites as I see myself returning to
it often.

Unfortunately the new code gives me an error when:

Student only has a First and Last Name
Student only has a First and Last and Second Name (ie no Preferred)

AND where student has ALL four names it fails to display the Second Name

It works wherever a student has a Preferred Name Scenario

Help

Lastly - does NULL & "B" = "B"?

TIA

--
Sue Compelling


Dale Fye said:
stName: [FirstName] & (" " + NZ("(" + [PreferredName] + ")", [SecondName])) &
" " & [LastName]

Which " " are you talking about?

It's probably the concatenation inside the brackets. When you concatenate
strings, you can either use the & or the + to do the concatenation. If you
use the &, Access will give you what is on the left, tacked onto what is on
the right, regardless of whether the left or right is NULL. If you use the
+, then access will look at both sides of the + and if either value is NULL,
it will return a NULL. So:

"A" & "B" = "AB"
"A" & NULL = "A"
"A" + "B" = "AB"
"A" + NULL = NULL

So, in the modified code I gave you above the stuff inside the NZ( )
function will get evaluated as:

NZ("(" + [PreferredName] + ")", [SecondName])

IF [PreferredName] is NULL then using the + sign to concatenate the "(" and
")" to it will still return a NULL, so [SecondName] would get returned. If
[PreferredName] is not Null, then this new code would return (Munjo) as in
your example.

You can use this technique to your advantage in a lot of situations, but the
one I find most useful is with names, similar to what you are doing.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

This was brilliant Dale - there's no way I would have been able to have
distilled my logic like that.

Two things however:

Using your abbreviated code my Preferred Name doesn't have the brackets
around it eg: Malo (Munji) Fandango - is there a way to do that using your
statement? (It does work with the correction you supplied me)

Do you have a minute to explain what the " " in your statement does?

Kind Regards

Sue
--
Sue Compelling


:

Sue,

I think your problem is in the second IIF clause.

Change: IIF([PreferredName] is not NULL),
To: IIF([PreferredName] is not NULL,

However, when you look at your logic, you can simplify this considerably.
Basically, your logic says, include the First and Last name in all cases, and
include the [PreferredName] if it is not null, otherwise include the
[SecondName] if it is not null. So you should be able to use:

stName: [FirstName] & (" " + NZ([PreferredName], [SecondName])) & " " &
[LastName]

By concatenating the " " and the NZ( ) function using a + sign, you will get
one of the following conditions:

IF both [PreferredName] and [SecondName] are NULL, you will get
[FirstName] & " " & [LastName]

IF [PreferredName] is NULL and [SecondName] is not, you will get
[FirstName] & " " & [SecondName] & " " & [LastName]

IF [PreferredName] is not NULL and [SecondName] is NULL, you will get
[FirstName] & " " & [PreferredName] & " " & [LastName]

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi ALL

I get an error message on this expressiin stating I have too many arguments
and have been unable to correct it.

TIA

StName: IIf(IsNull([SecondName]),IIf([PreferredName]is not null),[FirstName]
& " (" & [PreferredName] & ") " &
[LastName],IIf(IsNull([SecondName]),IIf(IsNull([PreferredName]),[FirstName] &
" " & [LastName],[FirstName] & " " & [SecondName] & " " & [LastName]))))
Click to expand...
Click to expand...
Click to expand...
 
Dale - I'VE DONE IT, I'VE DONE IT (though only with your guidance)

but what a thrill ... here's my code .

StName: [FirstName] & (NZ(" ("+[PreferredName]+")" & (Nz(" "+[SecondName]) &
" " & UCase([LastName]))))

Thanks soooo much
--
Sue Compelling


Dale Fye said:
Yes, NULL & "B" = "B"

Not sure what is going on, it works in my test database. Can you cut your
code and paste it back here? I'll take a another look at it once I get home.


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Sue Compelling said:
Hi Dale - you're extremely good at explaining things (thanks for taking the
time) and I've put this response in my favorites as I see myself returning to
it often.

Unfortunately the new code gives me an error when:

Student only has a First and Last Name
Student only has a First and Last and Second Name (ie no Preferred)

AND where student has ALL four names it fails to display the Second Name

It works wherever a student has a Preferred Name Scenario

Help

Lastly - does NULL & "B" = "B"?

TIA

--
Sue Compelling


Dale Fye said:
stName: [FirstName] & (" " + NZ("(" + [PreferredName] + ")", [SecondName])) &
" " & [LastName]

Which " " are you talking about?

It's probably the concatenation inside the brackets. When you concatenate
strings, you can either use the & or the + to do the concatenation. If you
use the &, Access will give you what is on the left, tacked onto what is on
the right, regardless of whether the left or right is NULL. If you use the
+, then access will look at both sides of the + and if either value is NULL,
it will return a NULL. So:

"A" & "B" = "AB"
"A" & NULL = "A"
"A" + "B" = "AB"
"A" + NULL = NULL

So, in the modified code I gave you above the stuff inside the NZ( )
function will get evaluated as:

NZ("(" + [PreferredName] + ")", [SecondName])

IF [PreferredName] is NULL then using the + sign to concatenate the "(" and
")" to it will still return a NULL, so [SecondName] would get returned. If
[PreferredName] is not Null, then this new code would return (Munjo) as in
your example.

You can use this technique to your advantage in a lot of situations, but the
one I find most useful is with names, similar to what you are doing.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

This was brilliant Dale - there's no way I would have been able to have
distilled my logic like that.

Two things however:

Using your abbreviated code my Preferred Name doesn't have the brackets
around it eg: Malo (Munji) Fandango - is there a way to do that using your
statement? (It does work with the correction you supplied me)

Do you have a minute to explain what the " " in your statement does?

Kind Regards

Sue
--
Sue Compelling


:

Sue,

I think your problem is in the second IIF clause.

Change: IIF([PreferredName] is not NULL),
To: IIF([PreferredName] is not NULL,

However, when you look at your logic, you can simplify this considerably.
Basically, your logic says, include the First and Last name in all cases, and
include the [PreferredName] if it is not null, otherwise include the
[SecondName] if it is not null. So you should be able to use:

stName: [FirstName] & (" " + NZ([PreferredName], [SecondName])) & " " &
[LastName]

By concatenating the " " and the NZ( ) function using a + sign, you will get
one of the following conditions:

IF both [PreferredName] and [SecondName] are NULL, you will get
[FirstName] & " " & [LastName]

IF [PreferredName] is NULL and [SecondName] is not, you will get
[FirstName] & " " & [SecondName] & " " & [LastName]

IF [PreferredName] is not NULL and [SecondName] is NULL, you will get
[FirstName] & " " & [PreferredName] & " " & [LastName]

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi ALL

I get an error message on this expressiin stating I have too many arguments
and have been unable to correct it.

TIA

StName: IIf(IsNull([SecondName]),IIf([PreferredName]is not null),[FirstName]
& " (" & [PreferredName] & ") " &
[LastName],IIf(IsNull([SecondName]),IIf(IsNull([PreferredName]),[FirstName] &
" " & [LastName],[FirstName] & " " & [SecondName] & " " & [LastName]))))
Click to expand...
Click to expand...
Click to expand...
 
Sue,

Glad I could help.

If it works the way you have it, then it will probably also work without the
NZ( ) function calls. You only need to use those in the event that you want
to use one thing, if the other is NULL (which, BTW, was the way your
original IIF( ) statement was written. The NZ( ) function is used to
substitute something for a NULL. As an example, when defining names, the
government likes to display "NMN" (stands for No Middle Name) when no midde
initial is provide, so you might see

Full Name: [Name_First] & " " & NZ([Name_MI], "NMN") & " " & [Name_Last]

Now, there are purists (I tend to be one) that believe that a NULL value in
the [Name_MI] field does not mean "NMN", but rather that no value has been
provided, so we don't know whether the individual has a middle name or not,
but that is for a more thoughtful discussion.

I think you could probably replace what you have with the following, and get
the exact same results.

StName: [FirstName] & (" ("+[PreferredName]+")" ) & (" "+[SecondName]) & " "
& UCase([LastName])

Dale

Sue Compelling said:
Dale - I'VE DONE IT, I'VE DONE IT (though only with your guidance)

but what a thrill ... here's my code .

StName: [FirstName] & (NZ(" ("+[PreferredName]+")" & (Nz(" "+[SecondName])
&
" " & UCase([LastName]))))

Thanks soooo much
--
Sue Compelling


Dale Fye said:
Yes, NULL & "B" = "B"

Not sure what is going on, it works in my test database. Can you cut
your
code and paste it back here? I'll take a another look at it once I get
home.


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Sue Compelling said:
Hi Dale - you're extremely good at explaining things (thanks for taking
the
time) and I've put this response in my favorites as I see myself
returning to
it often.

Unfortunately the new code gives me an error when:

Student only has a First and Last Name
Student only has a First and Last and Second Name (ie no Preferred)

AND where student has ALL four names it fails to display the Second
Name

It works wherever a student has a Preferred Name Scenario

Help

Lastly - does NULL & "B" = "B"?

TIA

--
Sue Compelling


:

stName: [FirstName] & (" " + NZ("(" + [PreferredName] + ")",
[SecondName])) &
" " & [LastName]

Which " " are you talking about?

It's probably the concatenation inside the brackets. When you
concatenate
strings, you can either use the & or the + to do the concatenation.
If you
use the &, Access will give you what is on the left, tacked onto what
is on
the right, regardless of whether the left or right is NULL. If you
use the
+, then access will look at both sides of the + and if either value
is NULL,
it will return a NULL. So:

"A" & "B" = "AB"
"A" & NULL = "A"
"A" + "B" = "AB"
"A" + NULL = NULL

So, in the modified code I gave you above the stuff inside the NZ( )
function will get evaluated as:

NZ("(" + [PreferredName] + ")", [SecondName])

IF [PreferredName] is NULL then using the + sign to concatenate the
"(" and
")" to it will still return a NULL, so [SecondName] would get
returned. If
[PreferredName] is not Null, then this new code would return (Munjo)
as in
your example.

You can use this technique to your advantage in a lot of situations,
but the
one I find most useful is with names, similar to what you are doing.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

This was brilliant Dale - there's no way I would have been able to
have
distilled my logic like that.

Two things however:

Using your abbreviated code my Preferred Name doesn't have the
brackets
around it eg: Malo (Munji) Fandango - is there a way to do that
using your
statement? (It does work with the correction you supplied me)

Do you have a minute to explain what the " " in your statement
does?

Kind Regards

Sue
--
Sue Compelling


:

Sue,

I think your problem is in the second IIF clause.

Change: IIF([PreferredName] is not NULL),
To: IIF([PreferredName] is not NULL,

However, when you look at your logic, you can simplify this
considerably.
Basically, your logic says, include the First and Last name in
all cases, and
include the [PreferredName] if it is not null, otherwise include
the
[SecondName] if it is not null. So you should be able to use:

stName: [FirstName] & (" " + NZ([PreferredName], [SecondName])) &
" " &
[LastName]

By concatenating the " " and the NZ( ) function using a + sign,
you will get
one of the following conditions:

IF both [PreferredName] and [SecondName] are NULL, you will get
[FirstName] & " " & [LastName]

IF [PreferredName] is NULL and [SecondName] is not, you will get
[FirstName] & " " & [SecondName] & " " & [LastName]

IF [PreferredName] is not NULL and [SecondName] is NULL, you will
get
[FirstName] & " " & [PreferredName] & " " & [LastName]

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi ALL

I get an error message on this expressiin stating I have too
many arguments
and have been unable to correct it.

TIA

StName: IIf(IsNull([SecondName]),IIf([PreferredName]is not
null),[FirstName]
& " (" & [PreferredName] & ") " &
[LastName],IIf(IsNull([SecondName]),IIf(IsNull([PreferredName]),[FirstName]
&
" " & [LastName],[FirstName] & " " & [SecondName] & " " &
[LastName]))))
Click to expand...
Click to expand...
Click to expand...
 
B.E.A.U.T.I.F.U.L.

Have changed the code - much easier on the eye - thanks a mill (are you a
closet Govt boffin?)


--
Sue Compelling


Dale Fye said:
Sue,

Glad I could help.

If it works the way you have it, then it will probably also work without the
NZ( ) function calls. You only need to use those in the event that you want
to use one thing, if the other is NULL (which, BTW, was the way your
original IIF( ) statement was written. The NZ( ) function is used to
substitute something for a NULL. As an example, when defining names, the
government likes to display "NMN" (stands for No Middle Name) when no midde
initial is provide, so you might see

Full Name: [Name_First] & " " & NZ([Name_MI], "NMN") & " " & [Name_Last]

Now, there are purists (I tend to be one) that believe that a NULL value in
the [Name_MI] field does not mean "NMN", but rather that no value has been
provided, so we don't know whether the individual has a middle name or not,
but that is for a more thoughtful discussion.

I think you could probably replace what you have with the following, and get
the exact same results.

StName: [FirstName] & (" ("+[PreferredName]+")" ) & (" "+[SecondName]) & " "
& UCase([LastName])

Dale

Sue Compelling said:
Dale - I'VE DONE IT, I'VE DONE IT (though only with your guidance)

but what a thrill ... here's my code .

StName: [FirstName] & (NZ(" ("+[PreferredName]+")" & (Nz(" "+[SecondName])
&
" " & UCase([LastName]))))

Thanks soooo much
--
Sue Compelling


Dale Fye said:
Yes, NULL & "B" = "B"

Not sure what is going on, it works in my test database. Can you cut
your
code and paste it back here? I'll take a another look at it once I get
home.


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi Dale - you're extremely good at explaining things (thanks for taking
the
time) and I've put this response in my favorites as I see myself
returning to
it often.

Unfortunately the new code gives me an error when:

Student only has a First and Last Name
Student only has a First and Last and Second Name (ie no Preferred)

AND where student has ALL four names it fails to display the Second
Name

It works wherever a student has a Preferred Name Scenario

Help

Lastly - does NULL & "B" = "B"?

TIA

--
Sue Compelling


:

stName: [FirstName] & (" " + NZ("(" + [PreferredName] + ")",
[SecondName])) &
" " & [LastName]

Which " " are you talking about?

It's probably the concatenation inside the brackets. When you
concatenate
strings, you can either use the & or the + to do the concatenation.
If you
use the &, Access will give you what is on the left, tacked onto what
is on
the right, regardless of whether the left or right is NULL. If you
use the
+, then access will look at both sides of the + and if either value
is NULL,
it will return a NULL. So:

"A" & "B" = "AB"
"A" & NULL = "A"
"A" + "B" = "AB"
"A" + NULL = NULL

So, in the modified code I gave you above the stuff inside the NZ( )
function will get evaluated as:

NZ("(" + [PreferredName] + ")", [SecondName])

IF [PreferredName] is NULL then using the + sign to concatenate the
"(" and
")" to it will still return a NULL, so [SecondName] would get
returned. If
[PreferredName] is not Null, then this new code would return (Munjo)
as in
your example.

You can use this technique to your advantage in a lot of situations,
but the
one I find most useful is with names, similar to what you are doing.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

This was brilliant Dale - there's no way I would have been able to
have
distilled my logic like that.

Two things however:

Using your abbreviated code my Preferred Name doesn't have the
brackets
around it eg: Malo (Munji) Fandango - is there a way to do that
using your
statement? (It does work with the correction you supplied me)

Do you have a minute to explain what the " " in your statement
does?

Kind Regards

Sue
--
Sue Compelling


:

Sue,

I think your problem is in the second IIF clause.

Change: IIF([PreferredName] is not NULL),
To: IIF([PreferredName] is not NULL,

However, when you look at your logic, you can simplify this
considerably.
Basically, your logic says, include the First and Last name in
all cases, and
include the [PreferredName] if it is not null, otherwise include
the
[SecondName] if it is not null. So you should be able to use:

stName: [FirstName] & (" " + NZ([PreferredName], [SecondName])) &
" " &
[LastName]

By concatenating the " " and the NZ( ) function using a + sign,
you will get
one of the following conditions:

IF both [PreferredName] and [SecondName] are NULL, you will get
[FirstName] & " " & [LastName]

IF [PreferredName] is NULL and [SecondName] is not, you will get
[FirstName] & " " & [SecondName] & " " & [LastName]

IF [PreferredName] is not NULL and [SecondName] is NULL, you will
get
[FirstName] & " " & [PreferredName] & " " & [LastName]

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi ALL

I get an error message on this expressiin stating I have too
many arguments
and have been unable to correct it.

TIA

StName: IIf(IsNull([SecondName]),IIf([PreferredName]is not
null),[FirstName]
& " (" & [PreferredName] & ") " &
[LastName],IIf(IsNull([SecondName]),IIf(IsNull([PreferredName]),[FirstName]
&
" " & [LastName],[FirstName] & " " & [SecondName] & " " &
[LastName]))))
Click to expand...
Click to expand...
Click to expand...
 
Don't know,

What is a "closet Govt boffin"?

Sue Compelling said:
B.E.A.U.T.I.F.U.L.

Have changed the code - much easier on the eye - thanks a mill (are you a
closet Govt boffin?)


--
Sue Compelling


Dale Fye said:
Sue,

Glad I could help.

If it works the way you have it, then it will probably also work without
the
NZ( ) function calls. You only need to use those in the event that you
want
to use one thing, if the other is NULL (which, BTW, was the way your
original IIF( ) statement was written. The NZ( ) function is used to
substitute something for a NULL. As an example, when defining names, the
government likes to display "NMN" (stands for No Middle Name) when no
midde
initial is provide, so you might see

Full Name: [Name_First] & " " & NZ([Name_MI], "NMN") & " " & [Name_Last]

Now, there are purists (I tend to be one) that believe that a NULL value
in
the [Name_MI] field does not mean "NMN", but rather that no value has
been
provided, so we don't know whether the individual has a middle name or
not,
but that is for a more thoughtful discussion.

I think you could probably replace what you have with the following, and
get
the exact same results.

StName: [FirstName] & (" ("+[PreferredName]+")" ) & (" "+[SecondName]) &
" "
& UCase([LastName])

Dale

message
Dale - I'VE DONE IT, I'VE DONE IT (though only with your guidance)

but what a thrill ... here's my code .

StName: [FirstName] & (NZ(" ("+[PreferredName]+")" & (Nz("
"+[SecondName])
&
" " & UCase([LastName]))))

Thanks soooo much
--
Sue Compelling


:

Yes, NULL & "B" = "B"

Not sure what is going on, it works in my test database. Can you cut
your
code and paste it back here? I'll take a another look at it once I
get
home.


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi Dale - you're extremely good at explaining things (thanks for
taking
the
time) and I've put this response in my favorites as I see myself
returning to
it often.

Unfortunately the new code gives me an error when:

Student only has a First and Last Name
Student only has a First and Last and Second Name (ie no
Preferred)

AND where student has ALL four names it fails to display the
Second
Name

It works wherever a student has a Preferred Name Scenario

Help

Lastly - does NULL & "B" = "B"?

TIA

--
Sue Compelling


:

stName: [FirstName] & (" " + NZ("(" + [PreferredName] + ")",
[SecondName])) &
" " & [LastName]

Which " " are you talking about?

It's probably the concatenation inside the brackets. When you
concatenate
strings, you can either use the & or the + to do the
concatenation.
If you
use the &, Access will give you what is on the left, tacked onto
what
is on
the right, regardless of whether the left or right is NULL. If
you
use the
+, then access will look at both sides of the + and if either
value
is NULL,
it will return a NULL. So:

"A" & "B" = "AB"
"A" & NULL = "A"
"A" + "B" = "AB"
"A" + NULL = NULL

So, in the modified code I gave you above the stuff inside the
NZ( )
function will get evaluated as:

NZ("(" + [PreferredName] + ")", [SecondName])

IF [PreferredName] is NULL then using the + sign to concatenate
the
"(" and
")" to it will still return a NULL, so [SecondName] would get
returned. If
[PreferredName] is not Null, then this new code would return
(Munjo)
as in
your example.

You can use this technique to your advantage in a lot of
situations,
but the
one I find most useful is with names, similar to what you are
doing.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

This was brilliant Dale - there's no way I would have been able
to
have
distilled my logic like that.

Two things however:

Using your abbreviated code my Preferred Name doesn't have the
brackets
around it eg: Malo (Munji) Fandango - is there a way to do that
using your
statement? (It does work with the correction you supplied me)

Do you have a minute to explain what the " " in your statement
does?

Kind Regards

Sue
--
Sue Compelling


:

Sue,

I think your problem is in the second IIF clause.

Change: IIF([PreferredName] is not NULL),
To: IIF([PreferredName] is not NULL,

However, when you look at your logic, you can simplify this
considerably.
Basically, your logic says, include the First and Last name in
all cases, and
include the [PreferredName] if it is not null, otherwise
include
the
[SecondName] if it is not null. So you should be able to use:

stName: [FirstName] & (" " + NZ([PreferredName],
[SecondName])) &
" " &
[LastName]

By concatenating the " " and the NZ( ) function using a +
sign,
you will get
one of the following conditions:

IF both [PreferredName] and [SecondName] are NULL, you will
get
[FirstName] & " " & [LastName]

IF [PreferredName] is NULL and [SecondName] is not, you will
get
[FirstName] & " " & [SecondName] & " " & [LastName]

IF [PreferredName] is not NULL and [SecondName] is NULL, you
will
get
[FirstName] & " " & [PreferredName] & " " & [LastName]

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hi ALL

I get an error message on this expressiin stating I have too
many arguments
and have been unable to correct it.

TIA

StName: IIf(IsNull([SecondName]),IIf([PreferredName]is not
null),[FirstName]
& " (" & [PreferredName] & ") " &
[LastName],IIf(IsNull([SecondName]),IIf(IsNull([PreferredName]),[FirstName]
&
" " & [LastName],[FirstName] & " " & [SecondName] & " " &
[LastName]))))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top