Query File As

  • Thread starter Thread starter MaRSMAN
  • Start date Start date
M

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
 
Try this --
IIF([Last] Is Not Null, [Last] & IIF([Suffix] Is Not Null, " " & [Suffix],
"") & ", " & [First], & " " & [Middle], "")
 
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

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

--
Build a little, test a little.


MaRSMAN said:
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
Click to expand...
Click to expand...
 
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
Click to expand...

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 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.

John W. Vinson said:
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
Click to expand...

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.
Click to expand...
 
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
John W. Vinson said:
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
Click to expand...

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.
Click to expand...
 
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
Click to expand...

<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...
 
Back
Top