Help with "Like" in a query

O

orv

Access03

Ok I have an unbound search form with an unbound input field named
'txtNumber'.
The field is used to search for a number ie 09-0001. This is how the number
is saved in the table, tblCase.
When you click Search on the search form it performs a query. The expression
in the query is as follows:

Expr1: tblCase!CaseNumber Like "*" & Forms!frmSearch!txtNumber & "*" Or
Forms!frmSearch!txtNumber Is Null
no sort and no criteria

[tblCase]![CaseNumber] Like "*" & [Forms]![frmSearch]![txtNumber] & "*" Or
[Forms]![frmSearch]![txtNumber] Is Null
no sort, criteria=true

The field txtNumber in tblCase is Data Type = Number, Format = 00-0000, Inpu
Mask= 99\-9999.

With the above expressions in the query, if I have say 09-1111, 09-0009,
07-7777, 08-1111, as data in the CaseNumber of tblCase these are the results
of the search:

09-1111 = No records
1, 11, 111, or 1111 = returns 09-1111,08-1111
09 = 09-0009

I have tried using the '#' instead of the asterisk with no results. I have
also tried using some patterns with no luck.

I would like to be able to search by entering the number either as 09-1111
or 091111.
Any help is greatly appreciated.

The search form has other unbound txt fields which work flawlessly.
 
K

KARL DEWEY

Try this --
[tblCase]![CaseNumber] Like "*" & Replace([Forms]![frmSearch]![txtNumber],
"-", "")& "*" Or [Forms]![frmSearch]![txtNumber] Is Null
 
D

Dirk Goldgar

orv said:
Access03

Ok I have an unbound search form with an unbound input field named
'txtNumber'.
The field is used to search for a number ie 09-0001. This is how the
number is saved in the table, tblCase.
When you click Search on the search form it performs a query. The
expression in the query is as follows:

Expr1: tblCase!CaseNumber Like "*" & Forms!frmSearch!txtNumber & "*" Or
Forms!frmSearch!txtNumber Is Null
no sort and no criteria

[tblCase]![CaseNumber] Like "*" & [Forms]![frmSearch]![txtNumber] & "*" Or
[Forms]![frmSearch]![txtNumber] Is Null
no sort, criteria=true

The field txtNumber in tblCase is Data Type = Number, Format = 00-0000,
Inpu Mask= 99\-9999.

With the above expressions in the query, if I have say 09-1111, 09-0009,
07-7777, 08-1111, as data in the CaseNumber of tblCase these are the
results of the search:

09-1111 = No records
1, 11, 111, or 1111 = returns 09-1111,08-1111
09 = 09-0009

I have tried using the '#' instead of the asterisk with no results. I have
also tried using some patterns with no luck.

I would like to be able to search by entering the number either as 09-1111
or 091111.
Any help is greatly appreciated.

The search form has other unbound txt fields which work flawlessly.

If your field's data type is Number, then the value is *not* stored as (for
example) "09-1111". The hyphen that is part of your format and input mask
cannot be stored in the field, and there are no leading zeros stored in
number fields.

Since you aren't going to be doing any calculations with the case number,
the best thing for you to do would be to change the field from Number to
Text. Then you could either have the hyphen stored in the field (so that
they all are in the form 99-9999) or not stored, but formatted that way for
display. In that case, your searches would be easier, but you'd need to
remove the hyphen entered by the user before any comparisons.

If you leave the CaseNumber as a number, then you need to do comparisons on
a formatted version of it, and you *also* need to remove the hyphen entered
by the user. It would be something like this:

Format(CaseNumber, "000000") Like "*" &
Replace(Forms!frmSearch!txtNumber, "-", "") & "*"
 
O

orv

I get an error "This expression is typed incorrectly, or it is too complex
to be evaluated. blah, blah, blah"


KARL DEWEY said:
Try this --
[tblCase]![CaseNumber] Like "*" & Replace([Forms]![frmSearch]![txtNumber],
"-", "")& "*" Or [Forms]![frmSearch]![txtNumber] Is Null


orv said:
Access03

Ok I have an unbound search form with an unbound input field named
'txtNumber'.
The field is used to search for a number ie 09-0001. This is how the
number
is saved in the table, tblCase.
When you click Search on the search form it performs a query. The
expression
in the query is as follows:

Expr1: tblCase!CaseNumber Like "*" & Forms!frmSearch!txtNumber & "*" Or
Forms!frmSearch!txtNumber Is Null
no sort and no criteria

[tblCase]![CaseNumber] Like "*" & [Forms]![frmSearch]![txtNumber] & "*"
Or
[Forms]![frmSearch]![txtNumber] Is Null
no sort, criteria=true

The field txtNumber in tblCase is Data Type = Number, Format = 00-0000,
Inpu
Mask= 99\-9999.

With the above expressions in the query, if I have say 09-1111, 09-0009,
07-7777, 08-1111, as data in the CaseNumber of tblCase these are the
results
of the search:

09-1111 = No records
1, 11, 111, or 1111 = returns 09-1111,08-1111
09 = 09-0009

I have tried using the '#' instead of the asterisk with no results. I
have
also tried using some patterns with no luck.

I would like to be able to search by entering the number either as
09-1111
or 091111.
Any help is greatly appreciated.

The search form has other unbound txt fields which work flawlessly.
 
O

orv

Dirk Goldgar said:
orv said:
Access03

Ok I have an unbound search form with an unbound input field named
'txtNumber'.
The field is used to search for a number ie 09-0001. This is how the
number is saved in the table, tblCase.
When you click Search on the search form it performs a query. The
expression in the query is as follows:

Expr1: tblCase!CaseNumber Like "*" & Forms!frmSearch!txtNumber & "*" Or
Forms!frmSearch!txtNumber Is Null
no sort and no criteria

[tblCase]![CaseNumber] Like "*" & [Forms]![frmSearch]![txtNumber] & "*"
Or [Forms]![frmSearch]![txtNumber] Is Null
no sort, criteria=true

The field txtNumber in tblCase is Data Type = Number, Format = 00-0000,
Inpu Mask= 99\-9999.

With the above expressions in the query, if I have say 09-1111, 09-0009,
07-7777, 08-1111, as data in the CaseNumber of tblCase these are the
results of the search:

09-1111 = No records
1, 11, 111, or 1111 = returns 09-1111,08-1111
09 = 09-0009

I have tried using the '#' instead of the asterisk with no results. I
have also tried using some patterns with no luck.

I would like to be able to search by entering the number either as
09-1111 or 091111.
Any help is greatly appreciated.

The search form has other unbound txt fields which work flawlessly.

If your field's data type is Number, then the value is *not* stored as
(for example) "09-1111". The hyphen that is part of your format and input
mask cannot be stored in the field, and there are no leading zeros stored
in number fields.

Since you aren't going to be doing any calculations with the case number,
the best thing for you to do would be to change the field from Number to
Text. Then you could either have the hyphen stored in the field (so that
they all are in the form 99-9999) or not stored, but formatted that way
for display. In that case, your searches would be easier, but you'd need
to remove the hyphen entered by the user before any comparisons.

If you leave the CaseNumber as a number, then you need to do comparisons
on a formatted version of it, and you *also* need to remove the hyphen
entered by the user. It would be something like this:

Format(CaseNumber, "000000") Like "*" &
Replace(Forms!frmSearch!txtNumber, "-", "") & "*"


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I changed field type to Text (deleted all the relationships and changed the
other tbl's too!) and this works now for the search, thanks.

When entering the number, for a new record, the hyphen appears as a
placeholder, however, when I view the record later it appears as "091111"
instead of "09-1111". It's stored in the table as "091111". The format for
the Text Box on the form is 00-0000. Is there away to have it display the
hyphen?
 
D

Dirk Goldgar

orv said:
I changed field type to Text (deleted all the relationships and changed
the other tbl's too!) and this works now for the search, thanks.

Very good.
When entering the number, for a new record, the hyphen appears as a
placeholder, however, when I view the record later it appears as "091111"
instead of "09-1111". It's stored in the table as "091111". The format for
the Text Box on the form is 00-0000. Is there away to have it display the
hyphen?

Clear the Format property of the text box, and leave the Input Mask of
"99\-9999".
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top