Re: Create Table?

DFS wrote:
GCM wrote:
Hi,

I have a table that has the following fields; LName, FName and MName.
Also there are other fields in the table. I need to create another
table with those fields combined as "Trim([LName] & " " & [FName] & "
" & [MName]). How can I create this table?

Thanks in advance...
GCM


SELECT (Trim(LName) & ' ' & Trim(FName) & ' ' & Trim(MName)) AS FullName
INTO NewTable
FROM ExistingTable
WHERE ...

Years ago SELECT (Trim(LName) & ' ' & Trim(FName) & ' ' & Trim(MName))
might result in two spaces in some cases where a field was null or held
a zero length string. I don't know if that is the case anymore but a
solution has been discussed many times here as in:

http://groups.google.ca/group/comp.databases.ms-access/msg/a807ea4bc514ad10

where Arvin writes:

"Try:

=[Street1] & (", " + [Street2]) & (", " + [Town]) & (", " + [County]) &
(",
" + [PostCode])

The "+" operator will not concatenate with a null, while the "&" will."

Regardless, I am with Keith in wondering if creatig this table is
REALLY necessaryfor the original poster as the first part of your query
gives all that the new table gives.

.

Relevant Pages

  • RE: Leading blanks being stripped
    ... FullName - FName, MName, LName ... FormalName - Prefix, FName, MName, LName, Degrees ...
    (microsoft.public.access.forms)
  • SQL UNION Question (or not UNION?)
    ... SELECT DISTINCT Last_Name as LName, First_Name as FName, Middle_Name as ... MName From Table1 UNION SELECT DISTINCT LastName as LName, ... FName, MiddleName as MName From Table2 ...
    (microsoft.public.vb.database)
  • Re: SQL UNION Question (or not UNION?)
    ... MiddleName as MName From Table2 ... SELECT DISTINCT Last_Name as LName, First_Name as FName, Middle_Name ... FirstName as FName, MiddleName as MName, 2 as FromTable From Table2 ...
    (microsoft.public.vb.database)
  • Re: Create Table?
    ... Lyle Fairfield wrote: ... I have a table that has the following fields; LName, FName and ... MName. ...
    (comp.databases.ms-access)
  • Re: Form wont remember last value
    ... "Ken Snell (MVP)" wrote: ... "I am using the LName and FName parameters as the Control Source for ... Report SQL: ...
    (microsoft.public.access.formscoding)