Using Like and # in query criteria

  • Thread starter Thread starter Chlaris
  • Start date Start date
C

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
 
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
Click to expand...
Try removing the NZ().

What's wrong with
SELECT...FROM... WHERE SomeField LIKE Forms![frmReport]!
[cboProjectCode] & "*"
 
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
 
Thanks for the response.
I have asked the user to remove the # sign :)

John Spencer said:
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
Click to expand...
Click to expand...
 
Back
Top