combining data from fields into a single field

[mgovatos]

I am creating a table in an Access database and I want to create a field that takes information from two other fields in thie record. I have a field called maiden_name and one called last_name, and I want to have a new field called display_name that does one of two things.

1. It fills itself with the contents of maiden_name followed by last_name (with a space in between) if the maiden_name field is not empty or

2 It fills itself with just the contents of last_name if the maiden_name field is empty.

I am thinking there is a way to use expression builder to solve this, but I am not certain how to use this.

Please help.

 

[FoFa]

iif(isnull([maiden_name]),[last_name],[maiden_name] & " " & [last_name])
Assumes no maiden name is null

 

[Rich_Lovina]

Okay, you basically want to create a calculated text field:

Here's the basic solution:
Usually you will make a new table of data from the existing table in which case in the query design grid type

display_name: [maiden_name]&" "&[last_name]

Using this format, when maiden_name is blank you will have a last name with a leading blank space. Others may comment on removing that, or using an if condition to encompass the above.

I believe you can create the same in the table design; just read your Help. I haven't visited that option for some time.
Hope this gets you started.

 

[mgovatos]

Thanks for the replies. I tried Fofa's suggestion and put it in the validation rule for the display_name field.

I got the following message when I tried to save the file:

Invalid SQL syntax - cannot use multiple columns in a column level CHECK constrant. '

Am I putting this formula in the wrong spot?