How to add trailing spaces to a text field (1 Viewer)

[cpampas]

Hello,
I appreciate your your help on this :
I have a table that looks like the following :

maqId	descMaq
1	komatsu PC 30
3	komatsu PC 30 MR
4	komatsu PC 300
5	komatsu PC 300 8
6	komatsu PC 301
8	komatsu PC 130
9	komatsu TC 31 KL
10	komatsu PC 33
11	komatsu PC 30 MR 3

From a previous post I got the idea to build a query search string to find all the records for the machine Komatsu PC 30, anyway this machine can be entered with full or partial description, therefor I want to retrieve records number 1 , 3 , and 11

the query works great except for the last space ( note that there are spaces before and after the search variables ( '* pc *') and also ('* 30 *')
the problem i guess is that there are no stored trailing spaces on text fields, so that the query only finds records with ID number 3, and number 11, but misses, number 1 ( does not find a trailing space )

SELECT tblMaq.maqId, tblMaq.descMaq
FROM tblMaq
WHERE (((tblMaq.descMaq) Like 'komatsu *' And (tblMaq.descMaq) Like '* pc *' And (tblMaq.descMaq) Like '* 30 *'));

Is there a way to add a trailing space to all the records in my table ? or is there something I should change in my sql search string
Thanks

 

[arnelgp]

You add but access will remove it

 

[arnelgp]

You can only add to your query:

SELECT tblMaq.maqId, tblMaq.descMaq
FROM tblMaq
WHERE (((' ' & tblMaq.descMaq & ' ') Like '* komatsu *' And (tblMaq.descMaq) Like '* pc *' And (tblMaq.descMaq) Like '* 30 *'));

 

[Gasman]

Can you try?

SELECT tblMaq.maqId, tblMaq.descMaq
FROM tblMaq
WHERE (((tblMaq.descMaq & ' ') Like 'komatsu *' And (tblMaq.descMaq) Like '* pc *' And (tblMaq.descMaq) Like '* 30 *'));

 

[cpampas]

Thank you so much, it worked great :

SELECT tblMaq.maqId, tblMaq.descMaq
FROM tblMaq
WHERE ((([tblMaq].[descMaq] & ' ') Like 'komatsu *' And ([tblMaq].[descMaq] & ' ') Like '* pc *' And ([tblMaq].[descMaq] & ' ') Like '* 30 *'));

 

[Mike Krailo]

I had similar situation in my part tracking database. Due to the nature of differences in part names, I could not always find all the similar spare parts that were compatible and had different part numbers. The solution was to create another field in the table called PartGroup. This allowed me to force certain groups of parts together by assigning a group number. That completely solved the issue.

That may be something for you to consider and I hope that makes sense.