display and find by last 8 of auto VIN

[Guest]

I'm sure I found this somewhere before, but My hard drive crashed and I
didn't back up my bookmarks:

In my "Products" table, I store vehicles for sale and sold. I have a field
for the VIN, which is not unique, because sometimes we get the same vehicle
back more than once, so each record has a unique ID. I have a form for
entering the sale of the vehicle, on which the user finds the vehicle by the
ID number.

I also have a form for re-printing bills of sale, also used for editing
records, such as if there is a typo in the name. On this form, they also use
the same combo and find the vehicles by ID number, but sometimes they don't
know the ID number, so I would like to be able to search by VIN, but I would
like the user to just have to enter the last 6 or 8 digits, instead of the
whole thing. I don't know the syntax for that.

 

[Guest]

I would like the user to just have to enter the last 6 or 8 digits, instead

of the whole thing. I don't know the syntax for that.

In a parameter query, the criteria would be:

Like "*" & [Enter VIN]

or, you can reference a text box on an open form:

Like "*" & [Forms]![NameOfForm]![NameOfControl]


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[John Nurick]

Hi 031,

You can use the Like operator to find partial matches. For instance, if
the partial VIN is in a textbox txtVINtoFind, this
Like "*" & txtVINtoFind.Value
will match records where the last digits of the VIN match those typed.

 

[Guest]

So you are both telling me that

Like "*"

will return a match to the whole VIN if the user enters just the last 6
digits?

 

[Guest]

Yes, if I understand you correctly. For example, I just donated an old car
whose VIN number is: JM1GD222XJ1576106

If this data was in a table (lets call it tblCars), in a field named VIN,
the following query could be used as a parameter prompt:

PARAMETERS [Enter VIN] Text ( 255 );
SELECT tblCars.*
FROM tblCars
WHERE (((tblCars.VIN) Like "*" & [Enter VIN]));


Any of the following entered parameters will retrieve this record:

JM1GD222XJ1576106 (ie. the full VIN number)
M1GD222XJ1576106
1GD222XJ1576106
GD222XJ1576106
D222XJ1576106
222XJ1576106
22XJ1576106
2XJ1576106
XJ1576106
J1576106 (Last 8 digits)
1576106
576106 (Last 6 digits)
76106
6106
106
06
6


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[John Nurick]

Not quite.

Like "*"

will match any value except NULL. But

Like "*" & XXX

will match any string ending with XXX. So if you use this criterion for
VIN in a query

Like "*" & [Enter last digits of VIN]

you'll get all the records where VIN ends with whatever the user typed
(no matter how many digits).

If it's essential that the user should always have to provide exactly
six or exactly 8 digits, you could use a Like criterion as above, and
also add a calculated field to the query:
CritLen: Len([Enter last digits of VIN])
with two 'Or' criteria of 6 and 8.

 

[Guest]

What if I put a required 6-number input mask?

Not quite.

Like "*"

will match any value except NULL. But

Like "*" & XXX

will match any string ending with XXX. So if you use this criterion for
VIN in a query

Like "*" & [Enter last digits of VIN]

you'll get all the records where VIN ends with whatever the user typed
(no matter how many digits).

If it's essential that the user should always have to provide exactly
six or exactly 8 digits, you could use a Like criterion as above, and
also add a calculated field to the query:
CritLen: Len([Enter last digits of VIN])
with two 'Or' criteria of 6 and 8.

So you are both telling me that

Like "*"

will return a match to the whole VIN if the user enters just the last 6
digits?

 

[John Nurick]

(a) I'm not certain that you can have an input mask that allows 6 digits
or 8 digits but nothing else.

(b) However you could use a validation rule along these lines
(>= 100000 And <= 999999) Or (>=10000000 And <= 99999999)

(c) But the criterion will return only the records where the VIN ends
with whatever the user typed. Why is it necessary to force them to type
6 or 8 but not 4,5,7,9 or 10 digits?

What if I put a required 6-number input mask?

Not quite.

Like "*"

will match any value except NULL. But

Like "*" & XXX

will match any string ending with XXX. So if you use this criterion for
VIN in a query

Like "*" & [Enter last digits of VIN]

you'll get all the records where VIN ends with whatever the user typed
(no matter how many digits).

If it's essential that the user should always have to provide exactly
six or exactly 8 digits, you could use a Like criterion as above, and
also add a calculated field to the query:
CritLen: Len([Enter last digits of VIN])
with two 'Or' criteria of 6 and 8.

So you are both telling me that

Like "*"

will return a match to the whole VIN if the user enters just the last 6
digits?


:

Hi 031,

You can use the Like operator to find partial matches. For instance, if
the partial VIN is in a textbox txtVINtoFind, this
Like "*" & txtVINtoFind.Value
will match records where the last digits of the VIN match those typed.


On Sat, 3 Feb 2007 22:51:00 -0800, miss031

I'm sure I found this somewhere before, but My hard drive crashed and I
didn't back up my bookmarks:

In my "Products" table, I store vehicles for sale and sold. I have a field
for the VIN, which is not unique, because sometimes we get the same vehicle
back more than once, so each record has a unique ID. I have a form for
entering the sale of the vehicle, on which the user finds the vehicle by the
ID number.

I also have a form for re-printing bills of sale, also used for editing
records, such as if there is a typo in the name. On this form, they also use
the same combo and find the vehicles by ID number, but sometimes they don't
know the ID number, so I would like to be able to search by VIN, but I would
like the user to just have to enter the last 6 or 8 digits, instead of the
whole thing. I don't know the syntax for that.

 

[Guest]

Well, what I would actually like to do, so as not to be so strict in them
entering 6 digits, is have a combo for the VIN, and tell the user to type in
the last 6, but have the combo filtering or auto-completing as they type, so
if the type "12" then automatically the VIN with "125498" as the last 6 will
come up. I don't know how to have the typing start at the 6th digit from the
end of the field, though.


Also, another question for something completely seperate, maybe you know: is
there any way to change the colour of either the text or background of only
one column in a combo?

(a) I'm not certain that you can have an input mask that allows 6 digits
or 8 digits but nothing else.

(b) However you could use a validation rule along these lines
(>= 100000 And <= 999999) Or (>=10000000 And <= 99999999)

(c) But the criterion will return only the records where the VIN ends
with whatever the user typed. Why is it necessary to force them to type
6 or 8 but not 4,5,7,9 or 10 digits?

What if I put a required 6-number input mask?

Not quite.

Like "*"

will match any value except NULL. But

Like "*" & XXX

will match any string ending with XXX. So if you use this criterion for
VIN in a query

Like "*" & [Enter last digits of VIN]

you'll get all the records where VIN ends with whatever the user typed
(no matter how many digits).

If it's essential that the user should always have to provide exactly
six or exactly 8 digits, you could use a Like criterion as above, and
also add a calculated field to the query:
CritLen: Len([Enter last digits of VIN])
with two 'Or' criteria of 6 and 8.



On Sun, 4 Feb 2007 20:58:00 -0800, miss031

So you are both telling me that

Like "*"

will return a match to the whole VIN if the user enters just the last 6
digits?


:

Hi 031,

You can use the Like operator to find partial matches. For instance, if
the partial VIN is in a textbox txtVINtoFind, this
Like "*" & txtVINtoFind.Value
will match records where the last digits of the VIN match those typed.


On Sat, 3 Feb 2007 22:51:00 -0800, miss031

I'm sure I found this somewhere before, but My hard drive crashed and I
didn't back up my bookmarks:

In my "Products" table, I store vehicles for sale and sold. I have a field
for the VIN, which is not unique, because sometimes we get the same vehicle
back more than once, so each record has a unique ID. I have a form for
entering the sale of the vehicle, on which the user finds the vehicle by the
ID number.

I also have a form for re-printing bills of sale, also used for editing
records, such as if there is a typo in the name. On this form, they also use
the same combo and find the vehicles by ID number, but sometimes they don't
know the ID number, so I would like to be able to search by VIN, but I would
like the user to just have to enter the last 6 or 8 digits, instead of the
whole thing. I don't know the syntax for that.

 

[John Nurick]

To get the autocomplete to work you'll need to have the combo box's
RowSource be a query that returns just the last 6 characters, e.g.
SELECT DISTINCT Right([VIN], 6)
FROM MyTable
ORDER BY 1;
and then use this value in your search criterion, e.g.

WHERE Right([VIN], 6) = Forms![MyForm]![MyCombo]

If there are many records, I think this would be quite slow. It might
well be worth splitting the VIN between two fields, one containing the
the first part of the VIN and the second just the last six digits.
Assuming that the VIN is the unique identifier I think it is, you'd
include both the part-VIN fields in the table's primary key.

Then the RowSource of the como could be something like
SELECT DISTINCT VIN2
FROM MyTable
ORDER BY VIN2;

and the search criterion to find records matching the 6 digits in the
combo would be
WHERE VIN2 = Forms![MyForm]![MyCombo]

As for formatting different columns differently: I don't think this is
possible with Access combo boxes. In any case from the UI point of view
combo boxes usually work best with just one visible column. But in the
unlikely event I wanted to do what you ask I'd start at www.lebans.com.

Well, what I would actually like to do, so as not to be so strict in them
entering 6 digits, is have a combo for the VIN, and tell the user to type in
the last 6, but have the combo filtering or auto-completing as they type, so
if the type "12" then automatically the VIN with "125498" as the last 6 will
come up. I don't know how to have the typing start at the 6th digit from the
end of the field, though.


Also, another question for something completely seperate, maybe you know: is
there any way to change the colour of either the text or background of only
one column in a combo?

(a) I'm not certain that you can have an input mask that allows 6 digits
or 8 digits but nothing else.

(b) However you could use a validation rule along these lines
(>= 100000 And <= 999999) Or (>=10000000 And <= 99999999)

(c) But the criterion will return only the records where the VIN ends
with whatever the user typed. Why is it necessary to force them to type
6 or 8 but not 4,5,7,9 or 10 digits?

What if I put a required 6-number input mask?

:

Not quite.

Like "*"

will match any value except NULL. But

Like "*" & XXX

will match any string ending with XXX. So if you use this criterion for
VIN in a query

Like "*" & [Enter last digits of VIN]

you'll get all the records where VIN ends with whatever the user typed
(no matter how many digits).

If it's essential that the user should always have to provide exactly
six or exactly 8 digits, you could use a Like criterion as above, and
also add a calculated field to the query:
CritLen: Len([Enter last digits of VIN])
with two 'Or' criteria of 6 and 8.



On Sun, 4 Feb 2007 20:58:00 -0800, miss031

So you are both telling me that

Like "*"

will return a match to the whole VIN if the user enters just the last 6
digits?


:

Hi 031,

You can use the Like operator to find partial matches. For instance, if
the partial VIN is in a textbox txtVINtoFind, this
Like "*" & txtVINtoFind.Value
will match records where the last digits of the VIN match those typed.


On Sat, 3 Feb 2007 22:51:00 -0800, miss031

I'm sure I found this somewhere before, but My hard drive crashed and I
didn't back up my bookmarks:

In my "Products" table, I store vehicles for sale and sold. I have a field
for the VIN, which is not unique, because sometimes we get the same vehicle
back more than once, so each record has a unique ID. I have a form for
entering the sale of the vehicle, on which the user finds the vehicle by the
ID number.

I also have a form for re-printing bills of sale, also used for editing
records, such as if there is a typo in the name. On this form, they also use
the same combo and find the vehicles by ID number, but sometimes they don't
know the ID number, so I would like to be able to search by VIN, but I would
like the user to just have to enter the last 6 or 8 digits, instead of the
whole thing. I don't know the syntax for that.