Search Criteria Problem

  • Thread starter Thread starter Noor
  • Start date Start date
N

Noor

I have a field in a Product table named productno, this field is of text
type.
This field contains product no like Dafo #3 etc etc means strings that
contains # character.

The problem is when i search a product that has product no like strings that
contains # character, it returns me no record.

e.g

Select * from Prodcut where productno='Dafo #3'

the query doent not return any record. Im sure that the # no is creating
this problem,
Please suggest me some solution.

thanks
NM
 
Although the # can be used to represent a date (#12/12/2003#), it should not
interfere in the query when you have it encapsulated within the quotes(or
apostrophes). If you are trying to perform pattern matching, then you will
need to use the LIKE operator.

Select * from Prodcut where productno Like '*Dafo #3*'
--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Noor, the # is a wild card for any single numerical character. For example


1#3 finds 103, 113, 123

If you want to look for strings that contain the "#" you will need to do the
following

Like '*[#]*'

(the *'s just mean there can be any number of characters either side of the
#)

Regards
 
Thanks JohnFol!

It solved my problem but doing so i came up with another problem.
I also have [] characters in my productno, and when i do so, it says
"The pattern string is invalid" so how to escape this character too.

And please can you tell me where i can look for other character that can
potentially create problems for me.

NM


JohnFol said:
Noor, the # is a wild card for any single numerical character. For example


1#3 finds 103, 113, 123

If you want to look for strings that contain the "#" you will need to do the
following

Like '*[#]*'

(the *'s just mean there can be any number of characters either side of the
#)

Regards



Noor said:
I have a field in a Product table named productno, this field is of text
type.
This field contains product no like Dafo #3 etc etc means strings that
contains # character.

The problem is when i search a product that has product no like strings that
contains # character, it returns me no record.

e.g

Select * from Prodcut where productno='Dafo #3'

the query doent not return any record. Im sure that the # no is creating
this problem,
Please suggest me some solution.

thanks
NM
Click to expand...
Click to expand...
 
You can escape the [ by surrounding it with more brackets "[[]", the "]"
character is more difficult and can't be escaped. What you might be able to do
is to use "[\-^]" in your search criteria. This would match three characters,
"/", "]", and "^".

Try looking at the online help for "Wildcards" (which I just did) and it said
that you don't need to escape (bracket) the "]" character at all. I guess I've
learned something.

Like "*[[]*]*" should find a pair of matched "[]" with or without any
characters between them.
Thanks JohnFol!

It solved my problem but doing so i came up with another problem.
I also have [] characters in my productno, and when i do so, it says
"The pattern string is invalid" so how to escape this character too.

And please can you tell me where i can look for other character that can
potentially create problems for me.

NM

JohnFol said:
Noor, the # is a wild card for any single numerical character. For example


1#3 finds 103, 113, 123

If you want to look for strings that contain the "#" you will need to do the
following

Like '*[#]*'

(the *'s just mean there can be any number of characters either side of the
#)

Regards



Noor said:
I have a field in a Product table named productno, this field is of text
type.
This field contains product no like Dafo #3 etc etc means strings that
contains # character.

The problem is when i search a product that has product no like strings that
contains # character, it returns me no record.

e.g

Select * from Prodcut where productno='Dafo #3'

the query doent not return any record. Im sure that the # no is creating
this problem,
Please suggest me some solution.

thanks
NM
Click to expand...
Click to expand...
Click to expand...
 
Back
Top