MS Access 2003 SQL Statement

I am new to SOL and am trying to get the following statement to execute in
Access 2003. I am using the RTRIM to remove trailing spaces from the
Customers FirstName and combine it with the last name in a new field called
Name.

SELECT RTRIM (FirstName) & ' ' & LastName AS Name
FROM Customer;

When executing this query I always get a Compile error. Can anyone help?

Dear Roy:

Use double quotes in a Jet query for a literal string. Does that fix it?
--
Tom Ellison


"royeil" wrote in message
...
I am new to SOL and am trying to get the following statement to execute in
Access 2003. I am using the RTRIM to remove trailing spaces from the
Customers FirstName and combine it with the last name in a new field
called
Name.

SELECT RTRIM (FirstName) & ' ' & LastName AS Name
FROM Customer;

When executing this query I always get a Compile error. Can anyone help?

royeil wrote:
I am new to SOL and am trying to get the following statement to
execute in Access 2003. I am using the RTRIM to remove trailing
spaces from the Customers FirstName and combine it with the last name
in a new field called Name.

SELECT RTRIM (FirstName) & ' ' & LastName AS Name
FROM Customer;

When executing this query I always get a Compile error. Can anyone
help?

Where exactly are you trying to "execute" this? A compile error suggests that
you are typing this in a code window. SQL statements don't run from a code
module on their own.

Or are you typing this into the SQL view of the query designer (which would
work)?

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Tom

I have tried various methods. Enclosing the field name in " " as follows
is one. Did not work however.
SELECT RTRIM ("FirstName") & ' ' & LastName AS Name
FROM Customer;

Is this what you are suggesting?




"Tom Ellison" wrote:

Dear Roy:

Use double quotes in a Jet query for a literal string. Does that fix it?
--
Tom Ellison


"royeil" wrote in message
...
I am new to SOL and am trying to get the following statement to execute in
Access 2003. I am using the RTRIM to remove trailing spaces from the
Customers FirstName and combine it with the last name in a new field
called
Name.

SELECT RTRIM (FirstName) & ' ' & LastName AS Name
FROM Customer;

When executing this query I always get a Compile error. Can anyone help?

Rick

I am typing this in the Query SQL view. I have tried using all the TRIM
keywords and always get a compile error. Nothing seems to work. I am taking
an online Introduction to SQL Course at a local community college and this is
one of the practice exercises. The instructor has been unable to solve the
issue. The statement is suppose to work in Access version 2000 but I am using
2003.

I have tried to build the query using the expression wizzard and get the
same compile error as well.

Not sure where to go from here!

Thanks for the help!
Roy


"Rick Brandt" wrote:

royeil wrote:
I am new to SOL and am trying to get the following statement to
execute in Access 2003. I am using the RTRIM to remove trailing
spaces from the Customers FirstName and combine it with the last name
in a new field called Name.

SELECT RTRIM (FirstName) & ' ' & LastName AS Name
FROM Customer;

When executing this query I always get a Compile error. Can anyone
help?

Where exactly are you trying to "execute" this? A compile error suggests that
you are typing this in a code window. SQL statements don't run from a code
module on their own.

Or are you typing this into the SQL view of the query designer (which would
work)?

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Try

SELECT RTrim(FirstName) & " " & LastName AS FullName
FROM Customer.

Also, try this in the regular view of the query builder

FullName: RTrim(FirstName) & " " & LastName

with customer shown in the pane above the grid.

HTH;

Amy

"royeil" wrote in message
...
Tom

I have tried various methods. Enclosing the field name in " " as follows
is one. Did not work however.
SELECT RTRIM ("FirstName") & ' ' & LastName AS Name
FROM Customer;

Is this what you are suggesting?




"Tom Ellison" wrote:

Dear Roy:

Use double quotes in a Jet query for a literal string. Does that fix it?
--
Tom Ellison


"royeil" wrote in message
...
I am new to SOL and am trying to get the following statement to execute
in
Access 2003. I am using the RTRIM to remove trailing spaces from the
Customers FirstName and combine it with the last name in a new field
called
Name.

SELECT RTRIM (FirstName) & ' ' & LastName AS Name
FROM Customer;

When executing this query I always get a Compile error. Can anyone
help?

royeil wrote:
Rick

I am typing this in the Query SQL view. I have tried using all the
TRIM keywords and always get a compile error. Nothing seems to work.
I am taking an online Introduction to SQL Course at a local community
college and this is one of the practice exercises. The instructor has
been unable to solve the issue. The statement is suppose to work in
Access version 2000 but I am using 2003.

I have tried to build the query using the expression wizzard and get
the same compile error as well.

Not sure where to go from here!

If an Access file has any missing or broken references it causes many if the
built in functions to not work. I don't specifically recall Trim being one of
these but it's certainly possible.

In a code window look at Tools - References and see if any are marked "MISSING".

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Dear Roy:

No, I was suggesting replacing the single quotes you already had with double
quotes:

SELECT RTRIM(FirstName) & " " & LastName AS Name
FROM Customer;

If you have a references problem with RTRIM then try it without that:

SELECT FirstName & " " & LastName AS Name
FROM Customer;

If that fixes it, you need to add references.
--
Tom Ellison


"royeil" wrote in message
...
Tom

I have tried various methods. Enclosing the field name in " " as follows
is one. Did not work however.
SELECT RTRIM ("FirstName") & ' ' & LastName AS Name
FROM Customer;

Is this what you are suggesting?




"Tom Ellison" wrote:

Dear Roy:

Use double quotes in a Jet query for a literal string. Does that fix
it?
--
Tom Ellison


"royeil" wrote in message
...
I am new to SOL and am trying to get the following statement to
execute in
Access 2003. I am using the RTRIM to remove trailing spaces from the
Customers FirstName and combine it with the last name in a new field
called
Name.

SELECT RTRIM (FirstName) & ' ' & LastName AS Name
FROM Customer;

When executing this query I always get a Compile error. Can anyone
help?

Amy

I tried what you suggested and still get a complie error.

Thanks

"Amy Blankenship" wrote:

Try

SELECT RTrim(FirstName) & " " & LastName AS FullName
FROM Customer.

Also, try this in the regular view of the query builder

FullName: RTrim(FirstName) & " " & LastName

with customer shown in the pane above the grid.

HTH;

Amy

"royeil" wrote in message
...
Tom

I have tried various methods. Enclosing the field name in " " as follows
is one. Did not work however.
SELECT RTRIM ("FirstName") & ' ' & LastName AS Name
FROM Customer;

Is this what you are suggesting?




"Tom Ellison" wrote:

Dear Roy:

Use double quotes in a Jet query for a literal string. Does that fix it?
--
Tom Ellison


"royeil" wrote in message
...
I am new to SOL and am trying to get the following statement to execute
in
Access 2003. I am using the RTRIM to remove trailing spaces from the
Customers FirstName and combine it with the last name in a new field
called
Name.

SELECT RTRIM (FirstName) & ' ' & LastName AS Name
FROM Customer;

When executing this query I always get a Compile error. Can anyone
help?

Tom

The statement works fine with out the Rtrim Keyword by leaves unwanted
spaces between the FirstName and LastName in its results. I am not sure what
you mean by references!

Thanks

"Tom Ellison" wrote:

Dear Roy:

No, I was suggesting replacing the single quotes you already had with double
quotes:

SELECT RTRIM(FirstName) & " " & LastName AS Name
FROM Customer;

If you have a references problem with RTRIM then try it without that:

SELECT FirstName & " " & LastName AS Name
FROM Customer;

If that fixes it, you need to add references.
--
Tom Ellison


"royeil" wrote in message
...
Tom

I have tried various methods. Enclosing the field name in " " as follows
is one. Did not work however.
SELECT RTRIM ("FirstName") & ' ' & LastName AS Name
FROM Customer;

Is this what you are suggesting?




"Tom Ellison" wrote:

Dear Roy:

Use double quotes in a Jet query for a literal string. Does that fix
it?
--
Tom Ellison


"royeil" wrote in message
...
I am new to SOL and am trying to get the following statement to
execute in
Access 2003. I am using the RTRIM to remove trailing spaces from the
Customers FirstName and combine it with the last name in a new field
called
Name.

SELECT RTRIM (FirstName) & ' ' & LastName AS Name
FROM Customer;

When executing this query I always get a Compile error. Can anyone
help?