Using Like and # in query criteria

[Chlaris]

Dear all,

I create a query with the following criteria :

Like Nz(Forms!frmReport!cboProjectCode,"*")

The value of Forms!frmReport!cboProjectCode is "AI-LC2050 TC#7"

After I run the query, it returns no record.
But if I change the criteria to : Forms!frmReport!cboProjectCode, it returns
several records.

Is it right that I can't use Like statement with field value contains # ?

Thanks.

Chlaris

 

[pietlinden]

Dear all,

I create a query with the following criteria :

Like Nz(Forms!frmReport!cboProjectCode,"*")

The value of Forms!frmReport!cboProjectCode is "AI-LC2050 TC#7"

After I run the query, it returns no record.
But if I change the criteria to : Forms!frmReport!cboProjectCode, it returns
several records.

Is it right that I can't use Like statement with field value contains # ?

Thanks.

Chlaris

Try removing the NZ().

What's wrong with
SELECT...FROM... WHERE SomeField LIKE Forms![frmReport]!
[cboProjectCode] & "*"

 

[John Spencer]

Try changing the criteria to

Forms!frmReport!cboProjectCode OR Forms!frmReport!cboProjectCode is Null

The problem is that "#" is a wildcard character (in Access (Jet) SQL) meaning
any number character. You could also try using the replace function, if you
are using Access 2000 (fully patched) or later.

LIKE IIF(Forms!frmReport!cboProjectCode is Null,"*",
Replace(Forms!frmReport!cboProjectCode,"#","[#]"))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Chlaris]

Thanks for the response.
I have asked the user to remove the # sign

Try changing the criteria to

Forms!frmReport!cboProjectCode OR Forms!frmReport!cboProjectCode is Null

The problem is that "#" is a wildcard character (in Access (Jet) SQL)
meaning any number character. You could also try using the replace
function, if you are using Access 2000 (fully patched) or later.

LIKE IIF(Forms!frmReport!cboProjectCode is Null,"*",
Replace(Forms!frmReport!cboProjectCode,"#","[#]"))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Dear all,

I create a query with the following criteria :

Like Nz(Forms!frmReport!cboProjectCode,"*")

The value of Forms!frmReport!cboProjectCode is "AI-LC2050 TC#7"

After I run the query, it returns no record.
But if I change the criteria to : Forms!frmReport!cboProjectCode, it
returns several records.

Is it right that I can't use Like statement with field value contains # ?

Thanks.

Chlaris