Serial Number Field Problem

[JimUK]

Here's the problem. I have a table Equip that has a SN (serial number) field in it. The higher powers that be decided to change the serial numbers on all pieces of equipment.
My problem is that at least until all 1000 items have been retagged, I need to be able to search and find all pieces of equipment by either the old or new serial number until the change is completed.
My thoughts are to add another field to table, for instance, New_SN but then how would I setup my search queries to work on searching either number to find a particular piece of equipment?
Maybe I'm missing an easier way of doing this. Any input greatly appreciated.

Jim

 

[scriverb]

I think you are correct in your analysis. I would add an additional field as you suggested. As you begin updating your table with the new seriel number the queries certainly can be made to accomodate both searches. Give some examples of the searches and I can help you with setting them up.
Bob Scriver

 

[VicM]

Jim:

Unless there is a direct relationship between old and new numbers that you can compare, e.g. old: 103-34-9A; new: 40103-34-9A, adding another column if probably the better way to go.

If there is a direct relationship as shown above, then you could test your search string something like this in VBA code:

If searchstring = Me.name of form field containing SN Or searchstring = "40" & Me.name of form field containing SN Then ...

If you are using the query design criteria line for filtering, then you would need to put the searchstring on the first criteria line under the old number, and the searchstring on the Or line under the new number.

Be aware that if you have other criteria for other fields on the first line, those criteria will need to be repeated on the same Or line for the searchstring under the new number.

HTH,

Vic

 

[JimUK]

Well the old SN are 6 digits (it's a text field btw) in the format 001234. The new SN will all have an AA in front then 6 numbers, i.e. AA123456. Of course they couldn't use the same number and just precede with AA, the AA number will be totally different.
With the field being text I am just using simple "Like" query to pull the info to a form.
I just love the bean counters that come up with these ideas ;-)


Jim