SELECT from text that contains characters other than letters...??

[battlecat]

Hi There,

I have a table in an Access db that stores information about speech files. One of the fields in this table is called "Text" and it contains the phrase spoken in that particular speechfile.

These phrases often have characters such as the "#" sign at the end to indicate what tone of voice is used.

I am trying to create a Search where users can enter the phrase they are looking for, and will be returned the file (or combination of files) that contain this phrase.

My problem is, when I try to search for a string of text that includes the "#" I get 0 results everytime.

An example of what I am doing is this:

SELECT Speechfiles.Name FROM Speechfiles
WHERE Speechfiles.Text LIKE 'aero#'


It works fine for 'aero' or '*aero*' but whenever I try to add a character that is not a letter, it won't work.

If anyone has any ideas, I would REALLY appreciate it!!! I am completely at a loss.

Thanks so much....

 

[battlecat]

Hi again,

I'd asked this question in a couple of other forums, and someone answered and I thought I'd post it here too, incase anyone ever reads this and wonders. The solution to my problem is this:

"SELECT Speechfiles.Name FROM Speechfiles
WHERE InStr(Speechfiles.Text, 'aero#')>0"

Thanks.

 

[WayneRyan]

Battle,

Or:

SELECT Speechfiles.Name
FROM Speechfiles
WHERE Speechfiles.Text, '*aero#*'

Or:

SELECT Speechfiles.Name
FROM Speechfiles
WHERE Speechfiles.Text, '*' & [SomeField]& '*'

Wayne

 

[Jon K]

When you use the Like operator, Access treats # as the wildcard character for any single digit (0 - 9).

To return the # character instead, you can put it inside a pair of square brackets, e.g.

Like "aero[#]"

Like "*aero[#]*"

etc.
.