alpha AND numeric, best way to set up

[Don]

Hi -
I want to establish a car registry with just enough exceptions to create
problems. The VIN field can be "unique" -- almost.
VINs are generally 1 to 4 digit numbers, to be reported out in numerical
order, but with some duplicates, that can be made unique with trailing alpha
characters.
Here are some VINs
unk101 -- (this is the 101st car found with an unknown original vin)
101
125
254
1254
1254Replicar
These would all be different cars
In my one (car) to many (owners, past and present), I would search for 1254,
but need be able to see that there are TWO 1254 cars, from which I would have
to chose the one to work with.

Should I use a single TEXT field as my VIN and deal with the sorting issues?
That is, I can ( but would rather not) add a leading zero to my 3 digit vins,
and then strip it out in reports. Or should I try two fields that combined
become "unique" - 80% of the VINS are not duplicated.

While there likely are several ways to get the job done, I am looking for
the one with the least issues later on in adding cars and owners and
reporting sorted lists in the right order.
Thanks in advance for any thoughts. Don

 

[Allen Browne]

My preference would be to use 2 fields:
- a Number field, properties:
Size: Integer (since it's only 4 digits)
Required Yes
- a Text field, properties:
Required Yes
Allow Zero Length Yes
Default Value ""

This blocks nulls in both fields, but the VinSuffix (Text) field accepts a
zero-length string (ZLS), and that's the value you get unless the user types
something there. Create a unique index on the combination of the 2 fields.
For any VinNumber, you can have only one record where the VinSuffix is a
ZLS.

It's easy to sort on the combination of the two, and to concatenate the two
(e.g. to print on a report.)

I think that would be more efficient than using one Text field and setting
up your query to:
ORDER BY Val([VIN]), VIN

 

[m stroup]

tblBillets tblEmployees
Slot(PK - autonumber) EmpID(PK)
Instance Name
Job Desc SlotID(FK)

This was working fine, but alas, I was short-sighted and now need to split
my billets for part-time workers. I found your response and thought this was
the solution, but I hit a snag right away. I have several forms that have an
unbound box which runs a query of the Billet table to load a particular
record. I changed the query to find [Slot]&[Instance] and added this code to
what I had in the AfterUpdate event (which had been working fine):

strRight = Right(Me.cboSlot, 1)
ctleft = Len(Me.cboSlot) - 1
strLeft = Left(Me.cboSlot, ctleft)
If Not IsNull(Me.cboSlot) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst ("[Slot] = " & strLeft And "[Instance] = " & strRight)
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

I get a type mismatch error.

I also see I will need to change the FK in tblEmployees.

I am sure there is a better way to do this. Any suggestions?
.....


--
Teach me to fish! Thanks for the help.
Pax, M

My preference would be to use 2 fields:
- a Number field, properties:
Size: Integer (since it's only 4 digits)
Required Yes
- a Text field, properties:
Required Yes
Allow Zero Length Yes
Default Value ""

This blocks nulls in both fields, but the VinSuffix (Text) field accepts a
zero-length string (ZLS), and that's the value you get unless the user types
something there. Create a unique index on the combination of the 2 fields.
For any VinNumber, you can have only one record where the VinSuffix is a
ZLS.

It's easy to sort on the combination of the two, and to concatenate the two
(e.g. to print on a report.)

I think that would be more efficient than using one Text field and setting
up your query to:
ORDER BY Val([VIN]), VIN

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi -
I want to establish a car registry with just enough exceptions to create
problems. The VIN field can be "unique" -- almost.
VINs are generally 1 to 4 digit numbers, to be reported out in numerical
order, but with some duplicates, that can be made unique with trailing
alpha
characters.
Here are some VINs
unk101 -- (this is the 101st car found with an unknown original vin)
101
125
254
1254
1254Replicar
These would all be different cars
In my one (car) to many (owners, past and present), I would search for
1254,
but need be able to see that there are TWO 1254 cars, from which I would
have
to chose the one to work with.

Should I use a single TEXT field as my VIN and deal with the sorting
issues?
That is, I can ( but would rather not) add a leading zero to my 3 digit
vins,
and then strip it out in reports. Or should I try two fields that combined
become "unique" - 80% of the VINS are not duplicated.

While there likely are several ways to get the job done, I am looking for
the one with the least issues later on in adding cars and owners and
reporting sorted lists in the right order.
Thanks in advance for any thoughts. Don