Table with wildcards

[Guest]

I'm making a search engine for a table that contains entries like these

10300 (??? SF)

6 mm² (4 + G)

0.138"- 0.414"

# 12 AWG (19)


in various text fields. These symbols are causing problems with the search
queries. in fact Access cant even filter for them properly. Any tips?

 

[Douglas J. Steele]

Surround special characters with square brackets. Assuming you want anything
with three question marks in it, you'd use LIKE "*[?][?][?]*". For anything
with an octothorpe (#) in it, use LIKE "*[#]*"

Double up the quotes. To find something with double quotes inside the
string, use LIKE "*""*" (exagerated for clarity, that's LIKE " * " " * ")

I had no problems using LIKE "*mm²*". What problem are you having?

 

[Guest]

The table is a link to a read only table in an Infor database. My search tool
is made so that there are multiple pulldown boxes with potential criteria
listed to assist in te search. Each selection filters the next one so the
user doesnt have to type anything to narrow down the search. The symbol thats
giving me the most problems is #, because # doesnot find itself, while ?
does. Is there anyway to temporaily deactivate wildcards?

Surround special characters with square brackets. Assuming you want anything
with three question marks in it, you'd use LIKE "*[?][?][?]*". For anything
with an octothorpe (#) in it, use LIKE "*[#]*"

Double up the quotes. To find something with double quotes inside the
string, use LIKE "*""*" (exagerated for clarity, that's LIKE " * " " * ")

I had no problems using LIKE "*mm²*". What problem are you having?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm making a search engine for a table that contains entries like these

10300 (??? SF)

6 mm² (4 + G)

0.138"- 0.414"

# 12 AWG (19)


in various text fields. These symbols are causing problems with the search
queries. in fact Access cant even filter for them properly. Any tips?

 

[Douglas J Steele]

No, there's no way to disable wildcards.

What exactly are you doing with the data? I don't understand from your
description.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The table is a link to a read only table in an Infor database. My search tool
is made so that there are multiple pulldown boxes with potential criteria
listed to assist in te search. Each selection filters the next one so the
user doesnt have to type anything to narrow down the search. The symbol thats
giving me the most problems is #, because # doesnot find itself, while ?
does. Is there anyway to temporaily deactivate wildcards?

Surround special characters with square brackets. Assuming you want anything
with three question marks in it, you'd use LIKE "*[?][?][?]*". For anything
with an octothorpe (#) in it, use LIKE "*[#]*"

Double up the quotes. To find something with double quotes inside the
string, use LIKE "*""*" (exagerated for clarity, that's LIKE " * " " * ")

I had no problems using LIKE "*mm²*". What problem are you having?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm making a search engine for a table that contains entries like these

10300 (??? SF)

6 mm² (4 + G)

0.138"- 0.414"

# 12 AWG (19)


in various text fields. These symbols are causing problems with the search
queries. in fact Access cant even filter for them properly. Any tips?

 

[Guest]

This is the sql code embedded in one of my combo boxes

SELECT DISTINCT dbo_relQIS.Attrib01
FROM dbo_relQIS
WHERE (((dbo_relQIS.Grp) Like "*" & [Forms]![Search]![Group] & "*")) OR
((([Forms]![Search]![Group]) Is Null))
ORDER BY dbo_relQIS.Attrib01;

The user has specified the group of parts he wants to search in. The box
displays all the status attributes of items that belong to that group, the
user can pick a status description from this pull down list and hit find to
bring up all the items in these 2 subsets. But if the record in Attrib01 or
Group contains the character # then the search wont bring up anything. It is
a read only parts search.

No, there's no way to disable wildcards.

What exactly are you doing with the data? I don't understand from your
description.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The table is a link to a read only table in an Infor database. My search tool
is made so that there are multiple pulldown boxes with potential criteria
listed to assist in te search. Each selection filters the next one so the
user doesnt have to type anything to narrow down the search. The symbol thats
giving me the most problems is #, because # doesnot find itself, while ?
does. Is there anyway to temporaily deactivate wildcards?

Surround special characters with square brackets. Assuming you want anything
with three question marks in it, you'd use LIKE "*[?][?][?]*". For anything
with an octothorpe (#) in it, use LIKE "*[#]*"

Double up the quotes. To find something with double quotes inside the
string, use LIKE "*""*" (exagerated for clarity, that's LIKE " * " " * ")

I had no problems using LIKE "*mm²*". What problem are you having?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm making a search engine for a table that contains entries like these

10300 (??? SF)

6 mm² (4 + G)

0.138"- 0.414"

# 12 AWG (19)


in various text fields. These symbols are causing problems with the search
queries. in fact Access cant even filter for them properly. Any tips?

 

[John Vinson]

Is there anyway to temporaily deactivate wildcards?

Don't use LIKE in your SQL.

John W. Vinson[MVP]

 

[Douglas J Steele]

Create a function along the lines of FixSp (for Fix Special Characters)
that's as simple as:

Function FixSp(InputText As Variant) As String
Dim strOutputText As String

strOutputText = InputText & vbNullString
strOutputText = Replace(strOutputText, "#", "[#]")
strOutputText = Replace(strOutpuText, "?", "[?]")
' Add any other characters which which you're having problems

FixSp = strOutputText

End Function

then change your

WHERE (((dbo_relQIS.Grp) Like "*" & FixSp([Forms]![Search]![Group]) & "*"))

(since FixSp always returns a string, you'll end up with WHERE
(((dbo_relQIS.Grp) Like "**")) when
Forms]![Search]![Group] is null, so you don't really need that condition
anymore.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This is the sql code embedded in one of my combo boxes

SELECT DISTINCT dbo_relQIS.Attrib01
FROM dbo_relQIS
WHERE (((dbo_relQIS.Grp) Like "*" & [Forms]![Search]![Group] & "*")) OR
((([Forms]![Search]![Group]) Is Null))
ORDER BY dbo_relQIS.Attrib01;

The user has specified the group of parts he wants to search in. The box
displays all the status attributes of items that belong to that group, the
user can pick a status description from this pull down list and hit find to
bring up all the items in these 2 subsets. But if the record in Attrib01 or
Group contains the character # then the search wont bring up anything. It is
a read only parts search.

No, there's no way to disable wildcards.

What exactly are you doing with the data? I don't understand from your
description.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The table is a link to a read only table in an Infor database. My

search
tool

is made so that there are multiple pulldown boxes with potential criteria
listed to assist in te search. Each selection filters the next one so the
user doesnt have to type anything to narrow down the search. The

symbol
thats

giving me the most problems is #, because # doesnot find itself, while ?
does. Is there anyway to temporaily deactivate wildcards?

:

Surround special characters with square brackets. Assuming you want anything
with three question marks in it, you'd use LIKE "*[?][?][?]*". For anything
with an octothorpe (#) in it, use LIKE "*[#]*"

Double up the quotes. To find something with double quotes inside the
string, use LIKE "*""*" (exagerated for clarity, that's LIKE " * " "

*
")

I had no problems using LIKE "*mm²*". What problem are you having?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm making a search engine for a table that contains entries like these

10300 (??? SF)

6 mm² (4 + G)

0.138"- 0.414"

# 12 AWG (19)


in various text fields. These symbols are causing problems with

the
search

queries. in fact Access cant even filter for them properly. Any tips?