Nz function

[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

 

[Marshall Barton]

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")

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.

 

[Sue Compelling]

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

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")

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.

 

[Sue Compelling]

Marshall - I also meant to ask ...

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

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")

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.

 

[Marshall Barton]

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?

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?

It propagates Null
"ABC" + Null results in Null
"ABC" & Null results in "ABC"
which is very handy in the right situation.

 

[Sue Compelling]

Marshall - you're a legend - much appreciated ...

Cheers

 

[Marshall Barton]

Marshall - I also meant to ask ...

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

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.