Calculate days between Billing Dates

[jsmith1061]

I am woking on a project full of nightmares. My latest, and the one I am currently unable to solve, involves trying to calculate the number of days between Billing Dates.

I have a table named "Date" - I didn't design this!
It contains a field named "Date"

If I query for just that Field I get the following:
Date
9/8/2005
8/8/2005
7/8/2005
6/8/2005
5/9/2005
...

I need to figure out a way to determine the number of days between the bills so I can calculate the average cost per day.

It seems like it should be an easy thing using like the datediff function or something, but since they are in separate records (rows), I can't figure it out. I have tried and tried and tried.

Any suggestions would be VERY much appreciated.

 

[GumbyD]

Here is function you can try - This is for a DAO database and will not work for ADO:

Public Function BillingDays(dteField As Date) As Integer
Dim rstDates As Recordset
Dim strSQL As String
Dim dtePrev As Date

strSQL = "SELECT date.Date " & _
"FROM [Date] " & _
"ORDER BY date.Date DESC;"
Set rstDates = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
rstDates.FindFirst ("Date = #" & dteField & "#")
If rstDates.NoMatch Then
MsgBox "there is no record with this date", vbOKOnly
BillingDays = 0
Else
rstDates.MoveNext
If rstDates.EOF Then
BillingDays = 0
Else
dtePrev = rstDates![Date]
BillingDays = dteField - dtePrev
End If
End If
rstDates.Close

End Function


In your query your expression will look as follows:
BillingDays: billingdays([date])

 

[jsmith1061]

Implementation Issues

Ok - I will have to admit my ignorance and say I am not sure how to implement the whole function thing.

Does the function portion get added as a module in access and then use the statement you gave me as an expression?

Thanks for the help!

 

[GumbyD]

Yes - Put the code in a new module. If you want to use that code you will need to be using DAO, so when you are in the module go to "Tools" on the menu bar and select "References." When the references window comes up you need to see a reference to the DAO 3.6 if you do not then your database is using ADO and the code I wrote will not work. Then you will have to decide if you want to switch to DAO or try and get some different code.