Concatenate fields...

[PaulChilds]

Hi,

I have a database to keep track of all my records and CDs.

To enter the names of all the Bands and singers I have a form in which there are three fields:

First Name
Surname
Full Name

The full name field is concatenated from the other two using the following code:

Private Sub First_Name_AfterUpdate()
Me![Full Name] = [First Name] & " " & [Surname]
End Sub

and

Private Sub Surname_AfterUpdate()
Me![Full Name] = [First Name] & " " & [Surname]
End Sub

This seemed to be working great UNTIL... I entered a 'one-name' artist (Sting, Dido etc).

The code then puts a space after the name (which I don't want). How can I set it so that if the surname field is empty, then not to display the space?

I though of deleting & " " & [Surname] from the First Name code, but then if I decide to go back and change it, it gets in a bit of a mess (and the space was still there any way).

Any thoughts?

CHeers

PC

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[PaulChilds]

I've decided to put the 'One-Name' artists in the Surname field, so it will be sorted correctly (and so I can put 'The', as in 'The Beatles' in the first name. This still does not solve my space problem however, as now they get a space in front of them.

I need some sort of code in the After Update of the Surname to say:

If the First name field has something in it, then display [First Name], a space, and the [Surname] BUT if the [First Name] is blank/Null, then just display the surname.

Any thoughts chaps?

PC

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[PaulChilds]

Eventually worked it out on my own. The answer was:

Me![Full Name] = [First Name] & (" " + [Surname])

and

Me![Full Name] = ([First Name] + " &quot & [Surname]

Thanks for all the help.

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk