Find sequential numbers

[Daine]

Thanks to everyone who gave me pointers last time, I got what I needed done, now I have a new one.

We have pulled the invoices from our sales systems into access. The dockets should be sequential, so I want tot find out whic (if any) numbres have been skipped so I can find out why the docket was not invoiced.

I have a table that list lost of information but I'm interested in DockNo from the InvHistory table between certain dates, I can set dates and sort the data but Ilm not sure how check if a number in the sequence is missing short of manually checking.

I have read some threads here (and around the net) while some are similiar to what I want to achieve they used VB Code rather than Access, so any help would be apprciated.

[Lysander]

Thanks to everyone who gave me pointers last time, I got what I needed done, now I have a new one.

We have pulled the invoices from our sales systems into access. The dockets should be sequential, so I want tot find out whic (if any) numbres have been skipped so I can find out why the docket was not invoiced.

I have a table that list lost of information but I'm interested in DockNo from the InvHistory table between certain dates, I can set dates and sort the data but Ilm not sure how check if a number in the sequence is missing short of manually checking.

I have read some threads here (and around the net) while some are similiar to what I want to achieve they used VB Code rather than Access, so any help would be apprciated.

There are two ways you could approach this.

One, create a table that just holds DockNo and populate from 0000 to 9999 or whatever your range of valid docket numbers are, then write a query joining this table with your InvHistory table, showing all DockNo and displaying only those where the InvHistory.DockNo is null. This will list the missing dockets.

Two, write a procedure in code to run through the table and display missing dockets. It would be something like this

Sub showmissing()
Dim db As Database
Dim rs As Recordset
Dim intDocket As Integer
Dim intX As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select DockNo from InvHistory order by DockNo")
intDocket = rs!DockNo 'This is the first record
intDocket = intDocket + 1 'this should be the next record
While Not rs.EOF
    rs.MoveNext
    If intDocket = rs!DockNo Then
        'we have the correct record
        intDocket = intDocket + 1
    Else
        'Missing 1 or more invoices
        For intX = intDocket To rs!DockNo - 1
            Debug.Print "Missing Invoice No " & intX
            'or write these out to a table
        Next
        intDocket = rs!DockNo + 1
    End If
Wend
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
 

[Daine]

There are two ways you could approach this.

One, create a table that just holds DockNo and populate from 0000 to 9999 or whatever your range of valid docket numbers are, then write a query joining this table with your InvHistory table, showing all DockNo and displaying only those where the InvHistory.DockNo is null. This will list the missing dockets.

Two, write a procedure in code to run through the table and display missing dockets. It would be something like this

Sub showmissing()
Dim db As Database
Dim rs As Recordset
Dim intDocket As Integer
Dim intX As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Select DockNo from InvHistory order by DockNo")
intDocket = rs!DockNo 'This is the first record
intDocket = intDocket + 1 'this should be the next record
While Not rs.EOF
    rs.MoveNext
    If intDocket = rs!DockNo Then
        'we have the correct record
        intDocket = intDocket + 1
    Else
        'Missing 1 or more invoices
        For intX = intDocket To rs!DockNo - 1
            Debug.Print "Missing Invoice No " & intX
            'or write these out to a table
        Next
        intDocket = rs!DockNo + 1
    End If
Wend
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
 

Thanks for your help, while the first is certainly the eaisiet and one that I considered it would only work for the first location, where as we have several.

The code looks like it would work for me, you have given an option to print to a message box, how do I make it print the missing ones to a table?

OK I tried running the code and got and overflow error on this line:
For intX = intDocket To rs!DocketNo - 1

[Daine]

OK I tried running the code and got and overflow error on this line:
For intX = intDocket To rs!DocketNo - 1

For some reason I can't edit that post, but I solved that problem by changing the integers to Long.

Still trying to add the data to a table, slowly getting there but learning lots in the meantime.

OK so I got the table part working so thanks for your help on the first part. Now to work out how to speed it up (it doesn't like the 340 000 records lol, I woder why, I don't think I would either)

Set rs = db.OpenRecordset("Select DockNo from InvHistory order by DockNo")

I'm going to try but incase I need help I'm assuming the part in between the ("") is only a select query in which case I should be able to set dates for it to search between (should be easy) or even get a start and end date input from the user (which is what might take me some time).

[Lysander]

For some reason I can't edit that post, but I solved that problem by changing the integers to Long.

Still trying to add the data to a table, slowly getting there but learning lots in the meantime.

OK so I got the table part working so thanks for your help on the first part. Now to work out how to speed it up (it doesn't like the 340 000 records lol, I woder why, I don't think I would either)

Set rs = db.OpenRecordset("Select DockNo from InvHistory order by DockNo")

I'm going to try but incase I need help I'm assuming the part in between the ("") is only a select query in which case I should be able to set dates for it to search between (should be easy) or even get a start and end date input from the user (which is what might take me some time).

Yes, the part between the " " is a select query. Design your query in the query builder and then cut and paste the SQL between the quotes.

You asked how to write the data out to a table. Before you open the rs recordset, do this
set rsOutput=db.openrecordset("mylogtable") obviously giving the correct tablename

then instead of debug.print use
rsOutput.addnew
rsOutput!DocketNumber=intX
rsOutput.update

and when you close rs, remember to close rsOutput as well.

[Daine]

Yes, the part between the " " is a select query. Design your query in the query builder and then cut and paste the SQL between the quotes.

You asked how to write the data out to a table. Before you open the rs recordset, do this
set rsOutput=db.openrecordset("mylogtable") obviously giving the correct tablename

then instead of debug.print use
rsOutput.addnew
rsOutput!DocketNumber=intX
rsOutput.update

and when you close rs, remember to close rsOutput as well.

Again thanks you have been a big help, I actually got the table working fine though, everything runs as I wanted it. Now I'mm just playing with small inprovements, such as removing the hard coding for dates. In queries if I types where date <=StartDate for example I get a textbox pop up for the input (as the vaule has yet to be defined, is there a way to duplicate this is code as I tried copying the Select query but in the case it stops the code with errors if I try to put teh query in the ("") section, infact it seems to do this anytime I put a where statements in the ("") section.

[Lysander]

Again thanks you have been a big help, I actually got the table working fine though, everything runs as I wanted it. Now I'mm just playing with small inprovements, such as removing the hard coding for dates. In queries if I types where date <=StartDate for example I get a textbox pop up for the input (as the vaule has yet to be defined, is there a way to duplicate this is code as I tried copying the Select query but in the case it stops the code with errors if I try to put teh query in the ("") section, infact it seems to do this anytime I put a where statements in the ("") section.

Glad you got it woking so far.

Not sure how you are running your code, but if from a form, then you could have a combo box (or calendar) to select and hold the start date, ditto for the end date if you have one. Then when you call the code from the form, pass the dates along with it. I tend to use text, and date value, rather than date fields, since sooner or later I hit a pc or server with USA style dates rather than UK ones.

so, you would have called your code, say MatchSales

Now call MatchSales startdate, enddate

and in your code

public sub MatchSales(byval StartDate as date(or string), byVal Enddate as)

and then build your sql statement. I would build a string, as the sql is getting quite long now.

So, you would have "select blahblah from tbl where startdate>=#21/5/2006#"

Now you can have "select blahblah from tbl where startdate>=datevalue('" & StartDate & "');"

No more hard coded dates.

[Daine]

Glad you got it woking so far.

Not sure how you are running your code, but if from a form, then you could have a combo box (or calendar) to select and hold the start date, ditto for the end date if you have one. Then when you call the code from the form, pass the dates along with it. I tend to use text, and date value, rather than date fields, since sooner or later I hit a pc or server with USA style dates rather than UK ones.

so, you would have called your code, say MatchSales

Now call MatchSales startdate, enddate

and in your code

public sub MatchSales(byval StartDate as date(or string), byVal Enddate as)

and then build your sql statement. I would build a string, as the sql is getting quite long now.

So, you would have "select blahblah from tbl where startdate>=#21/5/2006#"

Now you can have "select blahblah from tbl where startdate>=datevalue('" & StartDate & "');"

No more hard coded dates.

Again your help is apprecated, I just got back to this little project as other things have taken my time.

I'm never calling the code, I open the database, open the module, hit run. Open the table it prints to and print the table out, and I have my list of missing numbers to chase up before we are audited.

The US/UK date thing isn't a problem for me, our 'dates' are not true dates they are stored as 200706 (yyyymm).

Say I run this query
SELECT InvHist.Period, InvHist.DocketNo, InvHist.PlantName, InvHist.InvoiceNo, InvHist.CustNo, InvHist.ClientName, InvHist.InvoiceGST
FROM InvHist
WHERE (((InvHist.Period)>=sDate And (InvHist.Period)<=eDate))
ORDER BY InvHist.PlantName, InvHist.Period, InvHist.DocketNo;

I will get a little popup box (or two) when I run it as the values are undefined, I can simply type 200607 in the first and 200705 in the second, I can't do that from code as I get an error (unexpected amount of parametres passed expected 2).

I suppose I could do a query that prints to a table between the dates (as this would force the boxes) then open that table in the code as is, then open the last table to print- but I'm trying to do everything bar the printing from the code so it is one press open the table and print, I'm not even doing forms as it to run from as it doesn't need to look pretty as long as I get the tables.