Trailing Spaces workaround

[shexe]

Is anyone aware of a workaround for the trailing spaces flaw within Access? I need to identify a field that can possibly have a space after the data. Am I better off just doing a comparison in VBA or can this be trouble shooted(shot?(sic))?

[RuralGuy]

What do you mean by flaw?

[shexe]

Access is matching data that has a space at the end to data that does not.

Ex: ABCD_ (where underscore = space) is matching to ABCD.

[RuralGuy]

Have you considered switching from:
Option Compare Database to Option Compare Binary?

[shexe]

I'm not sure how sort order is driving a match issue. Also, I would hate to inadvertantly affect other queries in the DB that have nothing to do with this action.

Can you go into more depth as to how this may help with my challenge?

[RuralGuy]

How are you attempting to identify this field that might have a trailing space?

[shexe]

I am matching a table of known BudgetWorkbooks to a front end user table. The results are non-match items (see WHERE clause).

SELECT IT_Staff_Allocation_2010.TemplateOwner, IT_Staff_Allocation_2010.Project, IT_Staff_Allocation_2010.BudgetWorkbook, IT_Staff_Allocation_2010.ResourceName, IT_Staff_Allocation_2010.January, IT_Staff_Allocation_2010.February, IT_Staff_Allocation_2010.March, IT_Staff_Allocation_2010.April, IT_Staff_Allocation_2010.May, IT_Staff_Allocation_2010.June, IT_Staff_Allocation_2010.July, IT_Staff_Allocation_2010.August, IT_Staff_Allocation_2010.September, IT_Staff_Allocation_2010.October, IT_Staff_Allocation_2010.November, IT_Staff_Allocation_2010.December

FROM IT_Staff_Allocation_2010 LEFT JOIN Budget_Workbook_Allowed ON IT_Staff_Allocation_2010.BudgetWorkbook = Budget_Workbook_Allowed.BudgetWorkbook

WHERE (((Budget_Workbook_Allowed.BudgetWorkbook) Is Null));

[RuralGuy]

Something about repeating fields SHOUTS normalization issue to me. I'll look closer and see what you are doing.

[RuralGuy]

So which one of the two tables "might" have a trailing space in the BudgetWorkbook field and would is it supposed to be there and does it mean anything significant? Can you just clean up the field with a [Field] = Trim([Field]) update query? I'm guessing you are not in control of the data input and just have to deal with the results, right?

[shexe]

IT_Staff_Allocation_2010 may have the data with spaces. You are correct, I do not control the template that the Front End user has.

[RuralGuy]

Are you just linking to the (probably a spreadsheet) or do you import it? How many records are you working with because Access runs much faster on imported tables than linked ones and you could then clean your own copy of the data.

[shexe]

The IT_Staff_Allocation_2010 table is automatically updated through VBA from numerous excel workbooks. The downstream reports are also excel and are updated automatically via data connections. This allows the user to update information on their template (IT_Staff_Allocation_2010) and see results in their BudgetWorkbooks almost same time.

My concern with the TRIM update is that Access will not be able to handle up to 10 users with an incorporated Update Query. Should I perhaps incorporate the TRIM update in VBA?

[RuralGuy]

You could probably just incorporate the Trim() function in your SQL. This is just AIR CODE:
'SELECT TemplateOwner, Project, BudgetWorkbook, ResourceName, _
January, February , March, April, May, June, _
July, August , September, October, November, December _
FROM IT_Staff_Allocation_2010 _
LEFT JOIN Budget_Workbook_Allowed _
ON Trim(IT_Staff_Allocation_2010.BudgetWorkbook) = Trim(Budget_Workbook_Allowed.BudgetWorkbook) _
WHERE (((Trim(Budget_Workbook_Allowed.BudgetWorkbook)) Is Null));

[weekend00]

she wants the one with space not equal to the one without space.

[RuralGuy]

So the space must remain and it is significant. Great! That is like significant trailing zero's after the decimal place. Let me think about it for a bit. Is there any *logic* behing that decision?