Query File As

[MaRSMAN]

My Table Fields
First
Middle
Last
Suffix
Need the new field in the qurey Will be FileAs:
What is the expression so that FileAs displays Last (space) Suffix (if not
null)
then a (,) and a (space) then Fist (space) Middle
Can anyone give me the correct expression for this so I can copy and paste
from your reply

Thank You from Marsman

 

[KARL DEWEY]

Try this --
IIF([Last] Is Not Null, [Last] & IIF([Suffix] Is Not Null, " " & [Suffix],
"") & ", " & [First], & " " & [Middle], "")

 

[MaRSMAN]

It asks for a parimiter for the suffix then it asks for first name and then
again for the suffix then it finaly opens the qurey but no results in the
fileAs field it created

Try this --
IIF([Last] Is Not Null, [Last] & IIF([Suffix] Is Not Null, " " & [Suffix],
"") & ", " & [First], & " " & [Middle], "")

--
Build a little, test a little.

My Table Fields
First
Middle
Last
Suffix
Need the new field in the qurey Will be FileAs:
What is the expression so that FileAs displays Last (space) Suffix (if not
null)
then a (,) and a (space) then Fist (space) Middle
Can anyone give me the correct expression for this so I can copy and paste
from your reply

Thank You from Marsman

 

[John W. Vinson]

My Table Fields
First
Middle
Last
Suffix
Need the new field in the qurey Will be FileAs:
What is the expression so that FileAs displays Last (space) Suffix (if not
null)
then a (,) and a (space) then Fist (space) Middle
Can anyone give me the correct expression for this so I can copy and paste
from your reply

Thank You from Marsman

A slightly simpler expression does the same thing as Clifford's, taking
advantage of the fact that both + and & are string concatenation operators,
but handle nulls differently:

FileAs: [Last] & (" " + [Suffix]) & ", " & [First] & (" " + [Middle])

This would display

Vinson, John Wilmot
Vinson Jr., John Walker
Vinson, Ed

depending on the presence or absence of suffix and middle values.

 

[MaRSMAN]

John I copyied and pasted your expresion into the query saaved and closed
when reopening the query I stil get the box asking for parimeter Suffix If I
select OK the query opens and all is fine except no suffix in the FileAs
Is it a problem with redesigning the query to many times.

My Table Fields
First
Middle
Last
Suffix
Need the new field in the qurey Will be FileAs:
What is the expression so that FileAs displays Last (space) Suffix (if not
null)
then a (,) and a (space) then Fist (space) Middle
Can anyone give me the correct expression for this so I can copy and paste
from your reply

Thank You from Marsman

A slightly simpler expression does the same thing as Clifford's, taking
advantage of the fact that both + and & are string concatenation operators,
but handle nulls differently:

FileAs: [Last] & (" " + [Suffix]) & ", " & [First] & (" " + [Middle])

This would display

Vinson, John Wilmot
Vinson Jr., John Walker
Vinson, Ed

depending on the presence or absence of suffix and middle values.

 

[MaRSMAN]

Thank You very much this worked perfect after I found my spelling error in my
tabe field Sufix in stead of suffix as was in the expression
Hopfully will watch my spelling closer
If you are interested in something cool google LILY THE BEAR it is a live
feed she gave birth to 1 cub and expecting another enjoy the link below
http://www.wildearth.tv/static/wildearth/channels/we_bear_den.html

My Table Fields
First
Middle
Last
Suffix
Need the new field in the qurey Will be FileAs:
What is the expression so that FileAs displays Last (space) Suffix (if not
null)
then a (,) and a (space) then Fist (space) Middle
Can anyone give me the correct expression for this so I can copy and paste
from your reply

Thank You from Marsman

A slightly simpler expression does the same thing as Clifford's, taking
advantage of the fact that both + and & are string concatenation operators,
but handle nulls differently:

FileAs: [Last] & (" " + [Suffix]) & ", " & [First] & (" " + [Middle])

This would display

Vinson, John Wilmot
Vinson Jr., John Walker
Vinson, Ed

depending on the presence or absence of suffix and middle values.

 

[John W. Vinson]

Thank You very much this worked perfect after I found my spelling error in my
tabe field Sufix in stead of suffix as was in the expression
Hopfully will watch my spelling closer

<chuckle> That's the usual cause of an unexpected prompt. I can't think HOW
many times I've fumblefingered a parameter like that!

Thanks for the Lily link...