Access Control Field

[cjany]

I am creating a letter in Access 2000, and in the contact part of the letter, I want to say to contact the officer [name] in charge of the account at their extension [extension]. But if the [COMPOFCR] field is null, then to contact the "main office".

If isnull [COMPOFCR], then "office at 555-5555" else
[first name][last name] at 555-5555 ext [extension]

If I need to create a field in the query, then that's fine.

Any help will be appreciated.

 

[dhookom]

Try:
IIf(IsNull([COMPOFCR]), "office at 555-5555", [First Name] & " " & [Last Name] & " at 555-5555 ext " & [Extension])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[belovedcej]

If isnull [COMPOFCR], then "office at 555-5555" else
[first name][last name] at 555-5555 ext [extension]


Another field in the query will probably work the best.

In the header of a new column in your query, try this:

Contact: IIF([COMPOFCR] IsNull, "Office at 555-5555", Trim([first name]&" " &[last name]& "at 555-5555 ext" & [extension]))

 

[belovedcej]

Duane - I didn't know you could combine fields without Trim. Thanks for the inadvertant tip!

 

[cjany]

Thank you, your tips worked. I was trying similar code, but couldn't get the correct syntax. Thanks so much for your help.

 

[dhookom]

Trim() might be required if the data is stored in some other type of database like a FoxPro or ORACLE or other.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]