Comparing Data using a Query

[Stangleboy]

I am comparing name data from 2 different systems, the first is in DB2 and the other is a text file. The issue I have is the name (first, middle and last names)have following spaces in the DB2 format, such as each first name uses 20 spaces, so "CHRIS" is actually "CHRIS " with spaces at the end of the name. The text file only has "CHRIS". The query cannot match the data due to the spaces, is there a way in Access Query builder to match the text only and exculd the "SPACES" after the name in DB2? Thank you in advance for your help in this matter.

 

[sxschech]

you could use ltrim, trim or rtrim. In the example you provided:

In SQL:

rtrim(FirstName) AS First_Name,

In Query Design

First_Name:rtrim(FirstName)


rtrim will remove the extra spaces at the end of the text.
You will need to use the actual field names. So if FirstName is not the field name, you would substitute in your actual field name there.

 

[Stangleboy]

So where would I place this in a new box with a field name how the above language? I am in query builder, Design View. Thank you.

 

[sxschech]

Are you joining on any of the fields that have the extra spaces? Or are you doing something like this (assuming this is a representation of the query grid):
[tt]
Field: db2FirstName
Table: db2
Sort :
Show :
Criteria: txtFirstName
or:
[/tt]

If so change to:
[tt]
Field: db2First_Name:RTRIM(db2FirstName)
Table: db2
Sort :
Show :
Criteria: txtFirstName
or:
[/tt]

 

[Stangleboy]

I have 3 fields I want to return right now (just testing first name), the fields are: NAM_NAME_KEY, FIRSTNAME and NAM_FIRST_NAME. I have it joined, here is the screen:
Fields: NAM_NAME_KEY FIRSTNAME NAM_FIRST_NAME:Rtrim([NAM_FIRST_NAME])
Table: NI_NITNAME01 Angels_Cap NI_NITNAM01
Sort: Blanks
Show: All Checked
Criteria: Blanks

I get the following error: "Extra ) in query expression 'NI_NITNAM01.[RTrim([NAM_FIRST_NAME])]"

Thank you for your time and patience.

 

[sxschech]

Any chance you could post your sql statement? Click on View Menu and choose SQL view. Then copy and paste into your post. For clarity, before the code, put the word code in square brackets [ code ], be sure there are no spaces between the brackets and code. After the sql statement to a close code with [ /code ]. Same thing, no spaces.

 

[Stangleboy]

Definitely,

SELECT NI_NITNAM01.NAM_NAME_KEY, Black_Angels_Cap.FIRSTNAME, RTrim([NAM_FIRST_NAME]) AS NAM_FIRST_NAME
FROM Black_Angels_Cap INNER JOIN NI_NITNAM01 ON Black_Angels_Cap.FIRSTNAME = NI_NITNAM01.NAM_FIRST_NAME;

 

[sxschech]

Try adding the parts in [red]red[/red]:

SELECT NI_NITNAM01.NAM_NAME_KEY, Black_Angels_Cap.FIRSTNAME, RTrim([red]NI_NITNAM01.[/red][NAM_FIRST_NAME]) AS NAM_FIRST_NAME
FROM Black_Angels_Cap INNER JOIN NI_NITNAM01 ON Black_Angels_Cap.FIRSTNAME = [red]RTRIM([/red]NI_NITNAM01.NAM_FIRST_NAME[red])[/red];

Note: If you change it in this way, you may not be able to switch back to design view and have the query work properly. So if you still want to see in design view. Change the query to this:

SELECT NI_NITNAM01.NAM_NAME_KEY, Black_Angels_Cap.FIRSTNAME, RTrim(NI_NITNAM01.[NAM_FIRST_NAME]) AS NAM_FIRST_NAME
FROM Black_Angels_Cap, NI_NITNAM01
WHERE Black_Angels_Cap.FIRSTNAME = RTRIM(NI_NITNAM01.NAM_FIRST_NAME);

When you switch back to design view, you will notice that the line between Black_Angels_Cap and NI_NITNAM01 has disappeared, but the query will still run. If in the future you need to change to use a LEFT or RIGHT join, then you will need to work in SQL view rather than design view. In order to get around this, you might consider an "intermediate" query. This query would take care of the RTRIMS. It would only be one table in the query. Then, instead of joining the two tables you would join the query to the table.