build sql search string (1 Viewer)

[cpampas]

hello,
i am building a sql setence in order to query records in the field(descMaq) in a table (tblMaq),
assuming that my search string is: stTextoLibre= "BobCat S100"
i run the code to build this part of the sql statement

While fincadena = False
    i = i + 1
    If Mid(stTextoLibre, i, 1) = " " Then
    stTextoLibre = Left(stTextoLibre, i - 1) & _
     "*'_AND_(tblMaq.descMaq)_LIKE_'*" & Right(stTextoLibre, Len(stTextoLibre) - i)
    End If
    If i = Len(stTextoLibre) Then
    fincadena = True
    End If

stFiltro = "(tblMaq.descMaq) LIKE '*" & stTextoLibre & "*'"
    Wend

the code returns this string (stFiltro) that works correctly when running the new built query

(tblMaq.descMaq) LIKE '*bobcat*' AND (tblMaq.descMaq) LIKE '*s100*'

Nevertheless I noticed that with this construction I miss some records, for example it will miss this "BobCat S 100", wich I would like to include so
I thought of splitting the string stTextoLibre by space, but in that case i would also get undesired records such as :

Caterpillar S 45
New Holland 465 S

is there a solution to include "BobCat S 100" and exclude Caterpillar S 45 ?
Thanks

 

[theDBguy]

Hi. Just a thought... What happens if you search for?

... AND descMaq LIKE '*s[ ]100*'

 

[Mike Krailo]

Would be a lot easier if you broke the data down into Manufacture and Model. You can always concatenate the two fields together to display it that way on a form.

 

[cpampas]

... AND descMaq LIKE '*s[ ]100*'

with that syntax I can get all the results matching "Bobcat S 100", but not the "Bobcat S100"

Would be a lot easier if you broke the data down into Manufacture and Model. You can always concatenate the two fields together to display it that way on a form

i guess it would be better to have two different fields for Manufacture and model, but the data is being added to the table in a non structured way, via webscraping, wich makes it difficult to split into those fields. i can have in my table different string for the same type of machine , like:

Bobcat Loader S100
Skid steer Bobcat S100
Bobcat S 100
used Bobcat S100

 

[Isaac]

Definitely try to handle it in the import/input process if at all possible...it's "one time" vs. "many times" querying thereafter.

Maybe you can identify a static list of known manufacturers that exist in your data. Using Instr() just test to see if it exists at all - Bobcat, etc. If so, assign the Make to Bobcat and then either a) leave all the original text as the model, or b) use Replace() to remove the known Make value from the input string.

Either way, at least you'll then have Make in its own field thereafter. That might be a starting point

 

[cpampas]

Maybe you can identify a static list of known manufacturers that exist in your data. Using Instr() just test to see if it exists at all - Bobcat, etc.

That is a good idea.
I also noticed that the manufacturer always comes first in the string and afterwards the model , so if I find with instr the position of the existing manufacturer, perhaps I could remove all the spaces thereafter

Bobcat S 100, would become Bobcat S100
i guess I will have to loop through all records /find the position of the manufacturer, and edit the record after the position removing all spaces

I will give it a try, unless there is any other suggestion
Thanks

 

[theDBguy]

with that syntax I can get all the results matching "Bobcat S 100", but not the "Bobcat S100"

Hi. Sorry to hear that. This is all I could figure out.

...WHERE Replace(descMaq," ","") Like "*s100*"

 

[Mike Krailo]

i guess it would be better to have two different fields for Manufacture and model, but the data is being added to the table in a non structured way, via webscraping, wich makes it difficult to split into those fields. i can have in my table different string for the same type of machine , like:

Bobcat Loader S100
Skid steer Bobcat S100
Bobcat S 100
used Bobcat S100

In that case, this might be one of those times to use a temporary database to collect all the unclean data and as Isaac suggested, clean up the data using the method that he suggested or similar and then import the clean data into your actual database. Never let bad data go into your database. Is this a ongoing web scraping of data or just a one time thing?

 

[cpampas]

This is an ongoing web scraping, so i guess i should clean the current data, and also the code when importing new records

 

[Isaac]

That is a good idea.
I also noticed that the manufacturer always comes first in the string and afterwards the model , so if I find with instr the position of the existing manufacturer, perhaps I could remove all the spaces thereafter

Bobcat S 100, would become Bobcat S100
i guess I will have to loop through all records /find the position of the manufacturer, and edit the record after the position removing all spaces

I will give it a try, unless there is any other suggestion
Thanks

Good luck. I think this strategy might provide reasonable results, especially if your manufacturer words/terms are unique enough - Bobcat, Caterpillar, etc.

 

[jdraw]

Try this is your code. .. Like "*s*100*" ..

It should find
"BobCat S 100" and "BobCat S100"

However, I agree with the others - don't let bad/inconsistent data into your database.
Separate your Make, Model into their own tables.

Import -->Cleanse-->Store

 

[cpampas]

It should find
"BobCat S 100" and "BobCat S100"

it does find both, but it also finds "bomag s 45", and others containing the "S" character, therefor it might be the best to find the manufactor in each field to exclude all the non-bobcat records
Thanks for your help

 

[jdraw]

Can you post your screen scraping code? Just an interest.

 

[arnelgp]

stFiltro = "(tblMaq.descMaq) LIKE '*" & Replace$(stTextoLibre, " ", "*") & "*'"

 

[cpampas]

stFiltro = "(tblMaq.descMaq) LIKE '*" & Replace$(stTextoLibre, " ", "*") & "*'"

I do have to create the tblManufactors, to make the search more efficient, but in the meantime arnelgp 's string works for both cases
"BobCat S 100" and "BobCat S100", excluding other undesired records containing the "S" character

Many thanks