Help creating unique identifier reusing a range of numbers

[puttergirl]

HELP me; I'm really stuck. I need to create license numbers for Xray machines based on the county and type of machine. The number will have 3 parts- a county code followed by a dash, then a 4 digit number within a range depending on machine type, and then a distinguishing letter at the end to prevent repeats within the type part.

Users will be prompted for data and then will be given the license number. So first they'll enter the county and the county code assigned to it will make up the first part of the license number. Example- FULTON is 001, DEKALB is 002, and COBB is 003.

Then they will select a machine type from these ranges:
0000-0999- Medical
1000-1999- Hospital
2000-2999- Dental .... etc
This will make up the second part of the license number.

So the first FULTON county MEDICAL record will be 001-0000 and the second 001-0001 and so forth.

BUT, since we have thousands of Xray machines, we need to be able to begin the sequence over again once we get to the end of the range. So, we want to add a letter to the end of the license number. The first 1000 FULTON county MEDICAL machines will have an A on the end... 001-0000A through 001-0999A. Then we'll begin renumbering but with a B on the end... 001-0000B through 001-0999B.

This seems like it would be easy with code, but I'm not sure how to begin writing it. We're stuck with this method due to a filing system that would take months to change!!

 

[stix4t2]

You can use the DMAX("field", "table", "filter") function to pull the last value used. Use the filter to select the county and type. If you have multiple users, be careful to pull the max before saving the record. Oh, add one to the max for the new number.

 

[puttergirl]

How do I handle the range of numbers? If they reach the top of the range for a machine type, I need the numbering to start over, except with a new letter tacked on to the end?

 

[stix4t2]

I would think you would not want to restart the numbering, it allows for 9999 machines in a county of each type. If you have a table to associate machine type with the letter and flag active to test which letter is being actively used. And then test that a DMAX of 9999 would update the table, wouldn't that work.

 

[puttergirl]

Thanks for your help! There are only 1000 machines max in each county. We have to use the following ranges to specify which type of machine it is:
0000-0999- Medical
1000-1999- Hospital
2000-2999- Dental .... etc

We actually reach 1000 in the large ones within about a year. People with Xray machines open and close constantly and require new license numbers each time.

So lets say we're just talking about Dental machines. I need a way to number sequentially from 2000A to 2999A and then begin over at 2000B.

 

[stix4t2]

You may consider putting the year into the code so you can identify the code by year. Otherwise, part of the dmax filter will have to be restricting the ranges you discussed. But the filter should still work.

The table I discussed earlier with machine type and letter should also contain the number ranges that you are discussing. Then use DLookup or open the table to pull the information to dynamically build the DMAX function.

 

[Zion7]

pgirl, at a quick glance, working from stix's idea, i'll suggest this;

If it is consistant that the code will be formated as such,
3 digit county code, hyphen, 4 digit machine type, one letter then

I would use both DMin() & DMax()

strMin = DMin("licNum","tblXRay", _
"txtCounty = '" & Me.txtCounty & "' And " & _
"txtMachineType '" Me.txtMachineType & "'")

strMax DMax("licNum","tblXRay", _
"txtCounty = '" & Me.txtCounty & "' And " & _
"txtMachineType '" Me.txtMachineType & "'")

intMTypeMin = Cint(Mid(strMin,5,4))
intMTypeMax = Cint(Mid(strMax,5,4))


If intMTypeMax - intMTypeMin = 999 Then
strRange = Chr(Asc(Right(strMax,1)) + 1)
strLicNº = Me.txtCounty & Left(Me.txtMachineType,1) & "000" & strRange
Else
strLicNº = Me.txtCounty & (intMTypeMax + 1)& Right(strMax)
End If

just typed, not tried.

I trust though, this should give you some viable ideas

Good luck either way.