Nz function

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

Sue Compelling

Hi

I have an expression in my query that combines a number of fields for
Coordinators. In most instances the coordinator contact details that get
returned will not always have every type of contact number displayed (ie
Work, Home and Mobile) so in those instances I don't want the H:, W: or M: to
be shown.

My original expression which was doing this is:

Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] & " H: "
& Format([homephone],"(00) 000-0000") & " W: " & Format([workphone],"(00)
000-0000") & ", " & [workextension] & " M: " & Format([mobilephone],"(000)
000-0000")

I've tried using the Nz function in all sorts of iterations though error
after error comes back no matter how I contrive it. Here's my attempt - HELP
please!!!

Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] &

IIf(Nz(homephone),
" ",
(" H:†& Format([homephone],"(00) 000-0000"))) &

IIf(Nz(workphone),
" ",
(" W:†& Format([workphone],"(00) 000-0000") & ", " & [workextension])) &

IIf(Nz(mobilephone),
" ",
(" M:†& Format([mobilephone],"(000) 000-0000")))


TIA
 
Sue said:
I have an expression in my query that combines a number of fields for
Coordinators. In most instances the coordinator contact details that get
returned will not always have every type of contact number displayed (ie
Work, Home and Mobile) so in those instances I don't want the H:, W: or M: to
be shown.

My original expression which was doing this is:

Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] & " H: "
& Format([homephone],"(00) 000-0000") & " W: " & Format([workphone],"(00)
000-0000") & ", " & [workextension] & " M: " & Format([mobilephone],"(000)
000-0000")
Click to expand...


Assuming the fields are Text fields that look like
123456789, the Format function can take care of most of that
without any other fooling around:

.... & Format([homephone]," \H\: (@@) @@@-@@@@") &
Format([workphone]," \W\: (@@) @@@-@@@@") & (" X" +
[workextension]) & Format([mobilephone]," \M\: (@@)
@@@-@@@@")

Note the trick of using + for the Extension.
 
Hi Marshall

Great - it worked fine except ...

....there are instances where a mobile will only have 9 digits instead of 10
and I want them to display as:

(027) 123 456

Though currently they display as:

( 02) 712 3456

How do I get it to display from the left hand side first?

TIA

Also - what does that clever + do that a & doesn't?

--
Sue Compelling


Marshall Barton said:
Sue said:
I have an expression in my query that combines a number of fields for
Coordinators. In most instances the coordinator contact details that get
returned will not always have every type of contact number displayed (ie
Work, Home and Mobile) so in those instances I don't want the H:, W: or M: to
be shown.

My original expression which was doing this is:

Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] & " H: "
& Format([homephone],"(00) 000-0000") & " W: " & Format([workphone],"(00)
000-0000") & ", " & [workextension] & " M: " & Format([mobilephone],"(000)
000-0000")
Click to expand...


Assuming the fields are Text fields that look like
123456789, the Format function can take care of most of that
without any other fooling around:

.... & Format([homephone]," \H\: (@@) @@@-@@@@") &
Format([workphone]," \W\: (@@) @@@-@@@@") & (" X" +
[workextension]) & Format([mobilephone]," \M\: (@@)
@@@-@@@@")

Note the trick of using + for the Extension.
Click to expand...
 
Marshall - I also meant to ask ...

what part of this command is telling ACCESS not to include when the field is
null?
--
Sue Compelling


Marshall Barton said:
Sue said:
I have an expression in my query that combines a number of fields for
Coordinators. In most instances the coordinator contact details that get
returned will not always have every type of contact number displayed (ie
Work, Home and Mobile) so in those instances I don't want the H:, W: or M: to
be shown.

My original expression which was doing this is:

Combined: [Firstname] & " " & [Lastname] & ", " & [ContactType] & " H: "
& Format([homephone],"(00) 000-0000") & " W: " & Format([workphone],"(00)
000-0000") & ", " & [workextension] & " M: " & Format([mobilephone],"(000)
000-0000")
Click to expand...


Assuming the fields are Text fields that look like
123456789, the Format function can take care of most of that
without any other fooling around:

.... & Format([homephone]," \H\: (@@) @@@-@@@@") &
Format([workphone]," \W\: (@@) @@@-@@@@") & (" X" +
[workextension]) & Format([mobilephone]," \M\: (@@)
@@@-@@@@")

Note the trick of using + for the Extension.
Click to expand...
 
Sue said:
Hi Marshall

Great - it worked fine except ...

...there are instances where a mobile will only have 9 digits instead of 10
and I want them to display as:

(027) 123 456

Though currently they display as:

( 02) 712 3456

How do I get it to display from the left hand side first?
Click to expand...

If you can have less than 10 characters and you want them
left aligned use ! in the format string:
" \H\: !(@@@) @@@-@@@@"
Also - what does that clever + do that a & doesn't?
Click to expand...

It propagates Null
"ABC" + Null results in Null
"ABC" & Null results in "ABC"
which is very handy in the right situation.
 
Sue said:
Marshall - I also meant to ask ...

what part of this command is telling ACCESS not to include when the field is
null?
Click to expand...


The Format function.

It returns a ZLS (Zero Length String) if the value to be
formatted is Null or a ZLS AND you have not specified that
you want something else in those cases. E.g.

Format(Null, "\H\: @@@") returns ""
Format(Null, "\H\: @@@;""Unknown""") returns "Unknown"

Check VBA Help on Format Property for the multitude of
options that can be used.
 
Back
Top