Access 2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to run a multiple parameter query to search 1 field and bring up 3
search results.

The field is text and it looks like this: 024-001-006

I would like it to bring up approximately 3 different results but pull only
the 1st three numbers in the field.

Like all records starting with 023, 024, 025. I built the query below and
it will bring up only 1 parameter search - not the other 2 parameter
searches.

Like [Enter category] & "*" And Like [Enter Category] & "*" Or Like [Enter
Category] & "*"

I tried this query below:
Like "*" & [Enter subject No] & "*" Or Like "*" & [Enter Next No] & "*" Or
Like "*" & [Additional Subject] & "*"

But it brings up every thing - like 001-023-001 - I'm just looking for it to
pull the 1st three numbers of the field.
 
This criteria should have worked

Where FieldName Like [Enter category] & "*"

Another option will be

Where Left([FieldName],3) = [Enter category]
 
Like [Enter subject No] & "*" Or Like [Enter Next No] & "*" Or
Like [Additional Subject] & "*"

However the problem here is that if you leave any prompt blank you will
get every record that has a value in the field

To combat that you can do

Like Nz([Enter subject No],"9999") & "*" Or Like NZ([Enter Next
No],"9999") & "*" Or NZ(Like [Additional Subject],"9999") & "*"

Assuming that none of your values start with 9999 you should get only
records that start with one of the values you input.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Thanks, that was it. If I need to leave the 2nd & 3rd choice empty I put in
2 spaces and that eliminates errors.

John Spencer said:
Like [Enter subject No] & "*" Or Like [Enter Next No] & "*" Or
Like [Additional Subject] & "*"

However the problem here is that if you leave any prompt blank you will
get every record that has a value in the field

To combat that you can do

Like Nz([Enter subject No],"9999") & "*" Or Like NZ([Enter Next
No],"9999") & "*" Or NZ(Like [Additional Subject],"9999") & "*"

Assuming that none of your values start with 9999 you should get only
records that start with one of the values you input.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I'm trying to run a multiple parameter query to search 1 field and bring up 3
search results.

The field is text and it looks like this: 024-001-006

I would like it to bring up approximately 3 different results but pull only
the 1st three numbers in the field.

Like all records starting with 023, 024, 025. I built the query below and
it will bring up only 1 parameter search - not the other 2 parameter
searches.

Like [Enter category] & "*" And Like [Enter Category] & "*" Or Like [Enter
Category] & "*"

I tried this query below:
Like "*" & [Enter subject No] & "*" Or Like "*" & [Enter Next No] & "*" Or
Like "*" & [Additional Subject] & "*"

But it brings up every thing - like 001-023-001 - I'm just looking for it to
pull the 1st three numbers of the field.
Click to expand...
Click to expand...
 
Back
Top