Strip trailing blanks in a field 1

[SetBuilder]

I have 2 tables (Bill of Materials) in Access. Field1 in TableA is 50 characters long, Field1 in TableB is 25 characters long.

I am trying to match field1 in tableA to field1 in tableB. If I use the ADOX command .find, I can't get a match. They do match if I use a query, but I am trying to look at each component of a bill-of-material, so I need to see the differences between the two bills.

I have tried using a trim command to delete the trailing blanks, but that didn't work:

For example:
1. table1_Hold = Trim(rst1!fparent)
2. mySQLStringAccess = "trim(KitNum) = " & "'" & table1_Hold & "'"
3. .Find mySQLStringAccess, 0

where line #1 is setting the argument for the find in line taking the kit number (called 'fparent') from tableA;

where line #2 is building of the sql compare string taking the kit number from tableB;

where line #3 is the actual find that fails to find the matching kit.
A direct compare did not work either.

 

[ETID]

have you tried to trim the string as well

mySQLStringAccess = "trim(KitNum) = " & "'" & trim(table1_Hold) & "'"

 

[SetBuilder]

Thank you. That worked. Now I have a related problem, going the other way. One field is 25 characters and the other is 50. How do I pad a field to be a total of 25 characters. (I don't know how long the field actually is, but I want to make it exactly 25 characters). I could determine the lengh of the field, subtract it from 25 and add a literal of spaces to the end of the correct field, but there should be an easier way. In other words, I want to force the lenght to be exactly 25 characters.

field1 'xxx-xxx-xx'
len(field1) would give me 10
what I want is 'xxx-xxx-xx '
where len(field1) would give me 25
Some command like RightPad(field1,25)

 

[TonyJollans]

Hi SetBuilder,

The Space function returns a string of spaces of any length so you could use something like

Left(field1 & space(25),25)

Enjoy,
Tony

 

[SetBuilder]

Exactly! Thanks.