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?

 

[Pat Hartman]

FoFa's solution is correct but you don't want to store this field as it is redundant. Simply include it in any query. Just put the following in the Field cell of an empty column:

CombName:iif(isnull([maiden_name]),[last_name],[maiden_name
] & " " & [last_name])