INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Expanding on the left(string, length) expression

Expanding on the left(string, length) expression

(OP)
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.

RE: Expanding on the left(string, length) expression

Try use IIf()

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

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

RE: Expanding on the left(string, length) expression

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.

RE: Expanding on the left(string, length) expression

(OP)
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.

RE: Expanding on the left(string, length) expression

(OP)
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!

RE: Expanding on the left(string, length) expression

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:

gname sname  FLAG  assigned to
 Bob  Smith   0     B Smith
 Joe  King    0     J King
 MDT  410     1     MDT
 

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.

RE: Expanding on the left(string, length) expression

(OP)
That is a cool way of doing it! Thanks.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close