Fake Autonumber in Query

[olerebel]

How can I create a fake autonumber in a query? I have a set of data that have their own individual number but I would like to sort them by date and then run a dsum() based on the FAKE autonumber.

-Alex

 

[ions]

I have the same question

 

[KeithG]

You can do this by making a function in VBA with a Global variable to store the number.

 

[KeithG]

Something like below will work in a module. Then you can use your function in a query.

Option Compare Database
Public intNum As Integer


Public Function FakeAutoNum(loanId)

Dim loanIdd As Long

loanIdd = loanId

intNum = intNum + 1
FakeAutoNum = intNum

End Function

 

[ions]

Thanks Keith ... I was thinking the exact same thing but thought it was very ineffecient due to numerous function calls. But after searching the forums I think this is the best solution.

Keith just out of curiosity don't you just need?

Public Function FakeAutoNum (LoanID)

       counter= counter + 1

       FakeAutoNum = counter

end Function

Thanks again for your quick response Keith.

 

[ions]

Hmmm I implemented it with the function but it's not working well with Access. Just by viewing the query in datasheet views the FakeAutoNumbers change. It seems very unstable to do it this way.

I decided to do what I want by creatinga tempTable.