Find Missing Record - Is this possible?

[LaurieW]

I have been asked to find missing records in a table for a purchase order number field. The ultimate goal is to give the user a report showing a list of these missing purchase order numbers. An example would be if the table has purchaser order numbers 00001 and 00003, but not 00002, they would want the report to list 00002.

The problem I see, after looking at their Access table, is that the purchase order number is not an integer, but is a text field. Some of the purchase order numbers, for example, end in a letter (00014C), but most do not.

Would it be at all possible to get the list they are requesting if their field is not a number?? All the posts I've found are dealing with integers, not strings. If you think this is possible, I would appreciate some help getting started. I would assume this would best be done with code.

Thanks for your help!
Laurie

 

[LaurieW]

I just found out that some of the po numbers have letters in front (e.g., R00001). This further complicates matters...

 

[DJkarl]

I'm sure it's possible...just not easy. For the pure numbers it's pretty simple, however for the alpha-numeric po numbers I think whoever generates po numbers needs to provide what ranges have been generated so you can compare to see what's missing. If that's not available how are you to know what comes before or after 00014C, let alone R00001. Based on personal experience I could hazard a guess at what the sequence might be, but businesses shouldn't be driven by guesses.

 

[LaurieW]

Thank you for your reply ... I agree with you 100%. However, I think the problem is they don't even know what they've generated. And now it's on my plate to tell them...

 

[DJkarl]

Thank you for your reply ... I agree with you 100%. However, I think the problem is they don't even know what they've generated. And now it's on my plate to tell them...

Well I think you at least need them to clarify the order somewhat, I mean what comes before R00001, Q99999? You could make that assumption and then if no po numbers start with Q you would generate a hundred thousand missing po numbers. So if you want to be somewhat nasty (which I TOTALLY would be in this case) you could take that approach and give them so much data they will either a) leave you alone, b) give you what you need to do the job right.

 

[Mike375]

If you make a query that has the field in question and then add a calculated field

NewField:Asc([TheFieldNameinQuestion])

That will give you the ANSI character number for the first character. Here is a list of them

http://www.alanwood.net/demos/ansi.html

That will pick whether the entry starts with a letter or a number. You could then do a query using IIF to split the data and then use the Left or Right functions as required.

Your IIF would be something like IIF([NewFileld]> Number And [NewField]< Number etc

Or you could do NewField: Asc(Left([TheFieldNameinQuestion],1)) and that would give you the ANSI for the first character. All sorts of ways.

 

[LaurieW]

Thanks for that ... I've been trying to find a way to strip the letters off front/back of the numbers. I surely appreciate your help!

 

[dkinley]

Not sure if it would help, but you might want to look up "sorting algorithms" on Google. I've done something in similar in C++ but not in VBA. The end-all-be all algorithm shouldn't be that many steps; just getting into VBA might be the difficult part.

You are talking about sorting by first character, then if
prevchar1 = thischar1 and thischara1 = nextchar1 .. then sort the 2nd character ... etc etc. This is a lot of nesting of For-Loops.

Once sorted ,,, something that goes along the lines that compares thisword and thatword to determine IF a word belongs between them.

Like I said, tons of examples out there - you just might have to look at their algorithms and translate into VBA terms.

-dK

 

[Mike375]

This might be quicker. Paste this into a module

Public Function FindNum(strName As String) As String
Dim strTemp As String
Dim i As Integer

For i = 2 To Len(strName)

strTemp = Mid(strName, i, 1)

If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 128 And Asc(strTemp) > 96) Then
FindNum = Right$(strName, Len(strName) - i)
End If
Next i
End Function

Then in a query make a new field as in NewField:FindNum([YourFieldName])

If the entry ends with a number that will return the number or numbers. Thus from abc123 it gives 123. If the entry ends with a letter it will return Null. Thus you can split the records on Is Null and Is Not Null etc.

 

[LaurieW]

Thanks, guys. I'm fried for today. Will give this another shot tomorrow. Thanks alot for your helpful suggestions!

 

[CraigDolphin]

Ok...this is extremely ugly but I think it may work for you.

Be warned: if you have a large range of PO numbers this may take several minutes to run as there are lots of loops to go through.

Some notes:

Smack the idiots who came up with the PO numbering system

Smack em again for good measure.

This code was originally written basen on the scenario in your first post. I ammended it to deal with the new information in the second.

It does not fill in the gap between the last PO starting with a number, and the first PO starting with a letter.

Only use the code as an example of horrible coding, but you can dissect it to see what functions I used in the process.

It boils down to recognizing which scenario you're facing with each PO (and assumes all PO's are unique)

New base number with no letter
New base number with front letter
New base number with end letter
Same base number with front letter
Same base number with end letter

Then you have to keep track of what base number and letter you were on, and determine if there are gaps in the expected sequence. If there are, you output the missing but expected numbers. If not, then you move to the next PO and start over.

Look at the values in the MyTable for exisitng PO's. Then take note of the empty tblResults.

Open the form, and click the button to run the code. Have a look at the output in tblResults. Check it out and see if the output matches your expectations.

[Edit] And naturally you should also take the time to add error handling to the subs if you use them.