help with search statement

[knuckle05]

Hi all,

I originally posted this in the SQL Server section, but it is really an Access db that I am using.

I have a database full of music bands that I want to search on.

Everything is fine except that I would like to omit the word 'The' from my search. For ex:

When searching for The Beatles or The Doors or The Band, they should be classified under B, or D respectively, instead of under T.

This is the only word I want to omit. How can I replace this word with blank charcaters in my search string?

Is there a function in Access SQL like Replace, it does not seem to work.



Thx!

 

[jrbarnett]

Knuckle

Access 2002 does have a Replace function, but it didn't exist in earlier versions. I have written one in VBA available from my website in the Tek-Tips database on the Free Stuf page, which you can copy and paste into your database.

John

 

[CosmoKramer]

You can add a user-defined field to your query which can reformat the field if it begins with The, otherwise leave it alone. Something like this would work, provided "The" is the only word you are concerned with:

BandClassification:  IIf(Left([bandname],4)="The ",Right([bandname],Len([bandname])-4) & ", The",[bandname])

Use this new field to perform your searches and the original field for display purposes.

[blue]The Beatles[/blue] would become [blue]Beatles, The[/blue]
while
[blue]They Might Be Giants[/blue] would remain.

Hoc nomen meum verum non est.