query parameter problem

  • Thread starter Thread starter Vivian Carroll
  • Start date Start date
V

Vivian Carroll

In Access 2000 I have a form that asks the user what catalog number to look
up. Then I have a query that uses that information as a parameter so that
only the specified catalog number appear. The problem is that the catalog
numbers are 8 digits and I want the query to find all catalog numbers with
the same first 4 digits.



The code that I have in the query Catalog field parameter area is:
[Forms]![MyReport]![txtCatalog] (where txtCatalog is where the user types
the catalog number).



If the user only types the first 4 digits of the catalog number, the query
returns nothing because there are no exact matches. I tried
using:Left([Forms]![ReportName]![txtCatalog],4) and I tried using: Like
"[Forms]![ReportName]![txtCatalog]*" - what is the proper way to do it? Do I
have to write an On Exit procedure in the form and put the result in another
field on the form that the query can then reference?



TIA,



Vivian Carroll
 
Hi Vivian

You almost had it. Change the expression "[Forms]![ReportName]![txtCatalog]*"

to the following:
[Forms]![ReportName]![txtCatalog] & "*"

or if you want to get really creative you can use:

"*" & [Forms]![ReportName]![txtCatalog] & "*"
where you will search anywhere in the string....OR ... if you don't enter any
value in the txtCatalog field and want to return all of the values, you could
use the following:

Like IIF([Forms]![ReportName]![txtCatalog]= "", "*", "*" &
[Forms]![ReportName]![txtCatalog] & "*"

Hope this helps

Maurice St-Cyr
 
Back
Top