Query criteria

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I am looking for a way to format a criteria line to search for a specific
character within a group of letters/numbers. Specifically, I am working with
17 digit automotive VIN #'s (Vehicle Identification Numbers) and need to
create hits on all VIN's within a table that have a specific character at a
specific location within the VIN - 8th character needs to be either a 2, 8 or
V. The seven preceding characters are either letters or numbers (in no
defined sequence).

I know I can use an * after the eighth character as a wildcard.

Thanks in advance.....
 
I am looking for a way to format a criteria line to search for a specific
character within a group of letters/numbers. Specifically, I am working with
17 digit automotive VIN #'s (Vehicle Identification Numbers) and need to
create hits on all VIN's within a table that have a specific character at a
specific location within the VIN - 8th character needs to be either a 2, 8 or
V. The seven preceding characters are either letters or numbers (in no
defined sequence).

I know I can use an * after the eighth character as a wildcard.

Thanks in advance.....
Click to expand...

As criteria on the VIN field, write:
Mid([VIN],8,1) = 2

Then on the line below this, write:
Mid([VIN],8,1) = 8

Next, on the line below this, write:
Mid([VIN],8,1) = "V"

This will create an OR criteria
 
You can use criteria like the following in Access.

Find Vin with 7 characters followed by 2,8, or V followed by anything.

Like "???????[28V]*"



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

I am looking for a way to format a criteria line to search for a specific
character within a group of letters/numbers. Specifically, I am working with
17 digit automotive VIN #'s (Vehicle Identification Numbers) and need to
create hits on all VIN's within a table that have a specific character at a
specific location within the VIN - 8th character needs to be either a 2, 8 or
V. The seven preceding characters are either letters or numbers (in no
defined sequence).

I know I can use an * after the eighth character as a wildcard.

Thanks in advance.....
Click to expand...

As criteria on the VIN field, write:
Mid([VIN],8,1) = 2

Then on the line below this, write:
Mid([VIN],8,1) = 8

Next, on the line below this, write:
Mid([VIN],8,1) = "V"

This will create an OR criteria
Click to expand...
 
You could also use:

Criteria Like "???????[28V]*"


A different approach could be to add a calculated field:

Field Expr1: Mid(Vin, 8, 1)
Show <unchecked?>
Criteria Like "[28V]"
or
Criteria IN("2","8","V")
 
Back
Top