if ... then statement

[Coles via AccessMonster.com]

I have a database with first name, last name, sex (ie m or f) etc, and have a
label that prints this info. However, I would like to be able to add Mr. or
Ms. to the label and don't know how to make access do this. i know its
something like if sex = m then Mr, Ms but not sure of exactly how to type it
and have spent fruitless ages going through the help menu. Any advice would
be welcome. Thanks

 

[JudithJubilee]

Hello Coles,

Try:

Full Name:IIF([Sex]="m","Mr "&[First Name]&" "&[Last Name],"Ms "&[First
Name]&" "&[Last Name])

In Access it is IIF not IF

Judith

 

[Coles via AccessMonster.com]

Thanks Judith, but
1) where do I type this expression?
2) why do I need to have first name and last name in the expression not just
iif sex=m, Mr

Sorry, I am a just lost!

Thanks - Isabel (Coles)

Hello Coles,

Try:

Full Name:IIF([Sex]="m","Mr "&[First Name]&" "&[Last Name],"Ms "&[First
Name]&" "&[Last Name])

In Access it is IIF not IF

Judith

I have a database with first name, last name, sex (ie m or f) etc, and have a
label that prints this info. However, I would like to be able to add Mr. or
Ms. to the label and don't know how to make access do this. i know its
something like if sex = m then Mr, Ms but not sure of exactly how to type it
and have spent fruitless ages going through the help menu. Any advice would
be welcome. Thanks

 

[Trev B]

Hi,

What I do is make this a field in a database call saluation.

Then when info is added you can use any of the following

Sir
Sir
Mr Whatisname
Bill
George
madam
Miss

if oher way would be query as follows:-

Saluation: IIf([sex]="m","Sir",IIf([sex]="f","Ms",""))

 

[John Spencer MVP]

In the query you could use a calculated field using this expression
Field: Salutation: IIF(Sex = "m","Mr","Ms")

This assumes that you have a field named Sex that always contains a value of
"m" or "f". If the field (Sex) contains "m" then "Mr" will be returned. If
it does NOT contain "m" then "Ms" will be returned. Of course, if the field
is blank then you will get "Ms" which may not be your intention. That case
can be handled by nesting an IIF statement inside an IIF statement to return a
zero-length string (blank) if the value of Sex is anything other than M or F.

Field: Salutation: IIF(Sex = "m","Mr",IIF(Sex = "F","Ms",""))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Coles via AccessMonster.com]

Hi Trevor,

Thank you for your help. While it is true I could add a salutation field.
I inherited this database and don't feel like adding 450 Mr. or Ms into such
a field although my frustration level is such it may just be the easiest way.


Can you tell me where to enter the expression you gave me? into a query,
into the label somewhere, ??? and do I need to copy ALL of the expression
specifically salutation or are you saying I should enter the expression into
a field called Salutation?

Isabel (Coles )

Hi,

What I do is make this a field in a database call saluation.

Then when info is added you can use any of the following

Sir
Sir
Mr Whatisname
Bill
George
madam
Miss

if oher way would be query as follows:-

Saluation: IIf([sex]="m","Sir",IIf([sex]="f","Ms",""))

I have a database with first name, last name, sex (ie m or f) etc, and have a
label that prints this info. However, I would like to be able to add Mr. or
Ms. to the label and don't know how to make access do this. i know its
something like if sex = m then Mr, Ms but not sure of exactly how to type it
and have spent fruitless ages going through the help menu. Any advice would
be welcome. Thanks

 

[Coles via AccessMonster.com]

Very helpful reply John. Sex does indeed have either M or F. However, where
do I enter our very helfpul expression? To print my label I have a report
with various fields ie lastname, firstname, city, state, etc, but don't know
how to make the sex = M then Mr. expression or where to type it.

Thank you for your help.

Isabel (Coles)

 

[John Spencer MVP]

You can do it in the underlying query as a calculated field
Field: Salutation: IIF(Sex = "m","Mr","Ms")

OR you can add a control to your report layout and set the control's source to
= IIF([Sex] = "m","Mr","Ms")
That does mean that the underlying query will have to have the field [Sex] in
it so that the field is available in the report.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Trev B]

Hi

You would enter it in a query just like:-

Saluation: IIf([sex]="m","Sir",IIf([sex]="f","Ms",""))

As long as the table head already been selected it should work fine.

Regards


--
Trev B

Hi Trevor,

Thank you for your help. While it is true I could add a salutation field.
I inherited this database and don't feel like adding 450 Mr. or Ms into such
a field although my frustration level is such it may just be the easiest way.


Can you tell me where to enter the expression you gave me? into a query,
into the label somewhere, ??? and do I need to copy ALL of the expression
specifically salutation or are you saying I should enter the expression into
a field called Salutation?

Isabel (Coles )

Hi,

What I do is make this a field in a database call saluation.

Then when info is added you can use any of the following

Sir
Sir
Mr Whatisname
Bill
George
madam
Miss

if oher way would be query as follows:-

Saluation: IIf([sex]="m","Sir",IIf([sex]="f","Ms",""))

I have a database with first name, last name, sex (ie m or f) etc, and have a
label that prints this info. However, I would like to be able to add Mr. or
Ms. to the label and don't know how to make access do this. i know its
something like if sex = m then Mr, Ms but not sure of exactly how to type it
and have spent fruitless ages going through the help menu. Any advice would
be welcome. Thanks