MS Access 2003 SQL Statement

[Guest]

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 Ellison]

Dear Roy:

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

 

[Rick Brandt]

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)?

 

[Guest]

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
Is this what you are suggesting?

 

[Guest]

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

 

[Amy Blankenship]

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

 

[Rick Brandt]

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".

 

[Tom Ellison]

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.

 

[Guest]

Amy

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

Thanks

 

[Guest]

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