Combining Two Fields Into One

[Jonny]

How using SQL query in Acces to combine two fields into one.
For example:
Field1=Month
Field2=Date
===>
Field3=Month & Date

 

[DCrake]

You actually don't need two fields

Format(DateField, "mmmm dd/mm/yyyy")

will produce

May 01/10/2008

If you want to concatenate two fields together such as forename and surname use

Forename & " " & Surname

The & ampersand acts a piece of double sided tape. When using numbers be careful as some times it adds them together especially if you use + instead of &

David

 

[Jonny]

Probably I haven't explained clearly.
Yes, I need to concatenate two fields together such as forename and surname use into third field.
Forename & " " & Surname.
What the SQL code in Access for it?

Thanks

 

[DCrake]

If this is being done in a query it would look like this

FullName:[Forename] & " " [Surname]

Where forename and surname are the names of the fields you want to concat.

likewise in a form yoou would creqate an unbound text box and in the control source you would enter [Forename] & " " & [Surname]

 

[Jonny]

I mean to code below:

SELECT *,Field5+" "+Field6+" "+Field7+" "+Field8+" "+Field9+" "+Field10+" "+Field11 AS Expr1 INTO Temp1
FROM Temp;

But the problem that it does not work for fields where the at least on of fields is empty.

 

[DCrake]

As the resulting concat can only be expressed as a string nomatter what the type of the others are and whether they are null or not then try

Select *, Trim(Field1 & " ") & " " & Trim(Field2 & " ") & " " & Etc As Bigfield Into Temp1 From Temp

I would be tempted to test this a select query to get the systax right then change it to an append query. Then view the SQL to look how access has constructed it.

David

 

[neileg]

The + operator won't take nulls, the & operator will.