How to trim a record when using DB_OPEN_TABLE

[GHawkins]

I'm working with an Access database (Access 2007). TableA is my main table
with data, and TableB contains some items that need to be updated in TableA.
The tables are set up as follows:

TableA:
EmplID (primary key)
QtrDate (primary key)
Code

TableB:
EmplID (primary key)
ChangeDate
Code

Right now, I am taking the first record in TableB, searching TableA for a
match, then comparing the dates. When TableA.ID = TableB.EmplID and
TableA.Date = TableB.ChangeDate, I update TableA.Code with the value in
TableB. This all works fine, but recently we've run into a problem where
there are extra spaces at the end of some data in the TableA.EmplID. Employee
1234 might show up as '1234' in some records, but as '1234 ' in others. When
there are extra spaces in TableA.ID, it is not seen as a match when I compare
it to TableB, so the Code is not updated. Is there a way to Trim the ID in
TableA before comparing it to TableB?

Here's the code - it's not something I wrote, just something I support. I
don’t work with VBA much.

Dim sqlquery As String
Dim Empl As Database, EmplCode as Recordset, Changes as Recordset, EmpQuery
As QueryDef

Set Empl = CurrentDb()

DoEvents

Set EmpQuery = Empl.CreateQueryDef()
EmpQuery.NAME = ""

sqlquery = "SELECT DBO_TableB.EmplID, DBO_TableB.EffectiveDate,
DBO_TableB.Category from DBO_TableB"

EmpQuery.SQL = sqlquery

DoEvents

Set Changes = EmpQuery.OpenRecordset(dbOpenForwardOnly)
Set EmplCode = Empl.OpenRecordset("TableA", DB_OPEN_TABLE)

If Not Changes.EOF Then
Do Until Changes.EOF

EmplCode.Index =â€EmplIDâ€
EmplCode.Seek “=â€, Changes(“EmplIDâ€)
If Not EmplCode.NoMatch Then
If EmplCode(“EmplIDâ€) = Changes(“EmplIDâ€) Then
Do Until EmplCode(“EmplIDâ€) <> Changes(“EmplIDâ€)
If EmplCode(“QtrDateâ€) >= Changes(“ChangeDateâ€) Then
EmplCode.Edit
EmplCode(“Categoryâ€) = Changes(“Categoryâ€)
EmplCode.Update
End If
EmplCode.MoveNext
If EmplCode.EOF = True Then
EmplCode.MoveFirst
End If
Loop
End If
End If
Changes.MoveNext
Loop
End If

Changes.Close
EmplCode.Close
Exit Sub

 

[Dirk Goldgar]

I'm working with an Access database (Access 2007). TableA is my main table
with data, and TableB contains some items that need to be updated in
TableA.
The tables are set up as follows:

TableA:
EmplID (primary key)
QtrDate (primary key)
Code

TableB:
EmplID (primary key)
ChangeDate
Code

Right now, I am taking the first record in TableB, searching TableA for a
match, then comparing the dates. When TableA.ID = TableB.EmplID and
TableA.Date = TableB.ChangeDate, I update TableA.Code with the value in
TableB. This all works fine, but recently we've run into a problem where
there are extra spaces at the end of some data in the TableA.EmplID.
Employee
1234 might show up as '1234' in some records, but as '1234 ' in others.
When
there are extra spaces in TableA.ID, it is not seen as a match when I
compare
it to TableB, so the Code is not updated. Is there a way to Trim the ID in
TableA before comparing it to TableB?

It seems to me that you should first address the issue that you have records
in TableA that are supposed to represent the same entity, but that have
different keys; e.g., '1234' vs. '1234 '. If that extra space is
meaningful, these don't represent the same entity, but what you're trying to
do suggests that '1234' is the *correct* key for both records, and '1234 '
is an error. It would be unusual for trailing spaces to be significant in a
text field, especially if that field is a key. Is there somewhere a TableC
whose primary key is EmplID, that contains the "master" data for these
records? If so, is there a record in that table for both '1234' and '1234
'?

You can remove all the existing leading and trailing spaces in the key field
by executing an update query with SQL like this:

UPDATE TableA SET EmplID = Trim(EmplID)

If you only need to remove the trailing spaces and need to leave leading
spaces intact, you can use RTrim() instead of Trim().

One possible issue that could arise with that update is that you might
conceivable end up with a key violation, if the update would result in two
records wih the same primary key. For example, if you had these records:

EmplID='1234', QtrDate = #9/4/2008#
EmplID='1234 ', QtrDate = #9/4/2008#

.... the update would result in these records:

EmplID='1234', QtrDate = #9/4/2008#
EmplID='1234', QtrDate = #9/4/2008#

.... and that would not be allowed by the primary key constraint. However,
if '1234' and '1234 ' really represent the same entity, then that shouldn't
happen.

 

[GHawkins]

Thanks, I will try that. We are puzzled as to how data is added with the
extra spaces at the end, and that's what we would realistically fix, but the
data is coming from a different source and imported into our system. Extra
spaces are added to some ID's, but not all, so for now we're going to work
around the issue until we can figure out what else is going on to create the
extra spaces. There is a TableC with the "master" data of EmplID's and "1234"
is definitely the only ID that exists - there is no "1234 ". I'll work on
getting that ironed out, but at least for now we'll trim the data so it works
like they need it to!

Thanks again for your suggestion.

 

[John Spencer]

One thing that can add extra spaces is if the field is in an MS SQL
table and the field is defined as a Char field and not a VarChar field.
Char fields are always padded to have the defined length. That is if
the field is defined as Char 10 the field will always have a length of
10 - so 1234 becomes 1234 and 6 trailing spaces.

You can see similar things with Excel spreadsheets. A cell in Excel can
have trailing spaces and when Access links to the spreadsheet, the
trailing spaces are seen.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Dirk Goldgar]

One thing that can add extra spaces is if the field is in an MS SQL table
and the field is defined as a Char field and not a VarChar field.

The fact that the table names in GHawkins' code began with the prefix "DBO_"
suggests that as a possibility.

 

[GHawkins]

That seems to be exactly what's going on. I'll work on getting this changed,
thanks for that feedback!