query if fields are null

[jamescullis]

looking for some help, please.

I've got 4 fields
[firstName] [Firstname_formal] [Lastname] [Lastname_formal]

Some users will have a formal name (could be one or both) but prefer to be called something different.

John | Soon | Downer | Tzvetozar
Pam | Pamila | Downer |
Jan | | Smith | Jones
Fred | Ivan | Downer |

I want to construct a query that will use

- If the firstName_formal exists, use that
else use firstName
eg. the first persons realname is "Soon Tzvetozar", their preferred name is "John Downer".

- If the Lastame_formal exists, use that
else use Lastname
eg. the second persons realname is "Pamila Downer", their preferred name is "Pam Downer".
eg. the third persons realname is "Jan Jones", their preferred name is "Jan Smith".
eg. the forth persons realname is "Ivan Downer", their preferred name is "Fred Downer".

I've attached a demo DB

any ideas?

 

[Minty]

You could use Nz() function which might be okay for a one off use, or build a function you can use anywhere.

See the attached for the function and how to use it.

 

[isladogs]

I've read your question in a different way to Minty so I've added my own solution and used the Nz approach so you can see both methods

 

[Pat Hartman]

Wouldn't "legal" be a better qualifier than "formal"?

Why not always fill in the values for all four fields in the data entry form? Make all four fields required at the table level. Then use either the "legal" or "informal" name depending on what you are doing. That puts the logic entirely into the data entry form.

 

[jamescullis]

Thank you Minty and Ridders, spot on. I"ve gone with ridders but you've both given my an excellent answer.

Pat_Hartman: my db has 4 entries where this is required, not a big issue, just frustrating

 

[isladogs]

We are all happy to help