Expanding on the left(string, length) expression 1

[SherWat]

I have a form where gname, sname are entered.

I then have a text box called assigned to with the control source of: =Left([Gname],1) & " " & [Sname] which combines the first initial of their given name (gname) with a space then their full last name (sname).

gname sname assigned to
Bob Smith B Smith
Joe King J King
MDT 410 M 410


This works for most of my table.

But, there are some entries that I want the full gname to be entered rather than just the first initial.

So how would I create an expression that looks at the value in gname and if it equals MDT, then have the assigned to field be MDT 410 instead of M 410?

Hope that makes sense?

Thank you.

 

[dhookom]

Try use IIf()

=IIf(Gname = "MDT", Gname, Left([Gname],1)) & " " & [Sname]

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Is the "MDT" the only case of Gname where you want to do that?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SherWat]

dhookom - thanks - that expression worked!

andrzejek - MDT isn't the only one no - maybe there could be an "or" statement included in the IIF expression? There is only one other one.

Thanks.

 

[SherWat]

GOT IT!

=IIf([Gname]="MDT",[gname],IIf([gname]="CELL",[Gname],Left([Gname],1))) & " " & [Sname]

This statement works!

Thanks for pointing me in the right direction!

 

[Andrzejek]

If you do have some other Gname records that you want to treat differently than other, you may want to introduce another field in your table to indicate how you want to proceed:

[pre]
gname sname FLAG assigned to
Bob Smith 0 B Smith
Joe King 0 J King
MDT 410 1 MDT
[/pre]

Then your expression would be:

=IIf(FLAG = 1, Gname, Left([Gname],1)) & " " & [Sname])

This way you eliminate data from your expression and move this information into your table, where it belongs.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SherWat]

That is a cool way of doing it! Thanks.