Code for Adding Sequence to set of records is dynamic and not sequential

[jlgray0127]

I have a make table query which I am using to compare two data sets from two different sources. These are Builds of Material and have to be in sequential order to do the data comparison. A1A has to be on line 1, A1B has to be on line 2. If it is not, that is an error in the comparison.

I require the ability to assign a sequential number to each record from both sets of data when I pull the data sets into Access to not lose the order the data is stored.
I have a make table query for both sets of data to create a temp table.

I've used the VBA code:

Global seqNumber As LongGlobal lastcall As Date


Function wAutoNumber(i) As Long
If Now - lastcall > 4 / 60 / 60 / 24 Then
lastcall = Now
seqNumber = 0
End If
seqNumber = seqNumber + 1


wAutoNumber = seqNumber
End Function

What I found, is does not stay sequential (1,2,3,4...) its coming out 1,235, 68, 69, 70, 3, 6, 4, etc....

I can not compare the two records this way. Am I missing something in my code? This is the first time I've used this one, and not seeing why it would not be sequential.

[Micron]

Sample data would help, along with knowing what values are being passed to your function. If Now-lastcall is greater than the expression, your counter starts over again. I guess that's what you want? Also, using Now might be an issue - depends on what your date values really are, not just what they look like due to formatting in the table or query. As functions, Now will always be greater than Date.

[orange]

What is it that makes the records sequential or ordered without your function?

[CJ_London]

why not just use a query joining the two tables on the common value?