Parameter Query - Spaces (1 Viewer)

[razorking]

I am trying to figure out a good way to deal with a linked table that has some spaces at the end of the data, and how to query for that. What I have is data in a field that looks like this:
A
A+
B
B+
C
C+

All of the one digit records actually have a space, in the field, after the single digit. So i'm using a parameter query to allow a user to specify a parameter and return those records. The parameter query look like this: Like [Enter Price Level or * For All]. It works for the A+, B+, C+ but for the A, B and C I have to key in A , B , C (that's a value followed by a space). I don't want to have to have the user key a space after the parameter. Is there any way to have the query deal with this instead of the user having to key the space?

 

[MarkK]

To me that trailing space is noise or dirt in your data. If I could, I would run an update that cleans that up. If I couldn't run an update to clean up the source, I would write a query that trims the space, like...

SELECT Trim(t.Grade) As Grade FROM tGrades As t

...which renders a datasource that doesn't have trailing spaces.
Hope this helps,

 

[Galaxiom]

Access doesn't store trailing spaces entered from bound controls. Consequently I expect the backend is a server that supports fixed length text fields such as nchar or char on MS SQL Server.

One way to deal with it would be to check the length of the parameter and append a space if it was only one character.

Another alternative would be to test the parameter against the trimmed field value but this might be slower.

 

[razorking]

Thanks for the replies - I appreciate it.

I agree that ideally these spaces would be a part of the data. Unfortunately I have to deal with linked tables fairly frequently. In this particular case it does look like the Trim Function is going to work for me.

Thanks!