Sum Query (1 Viewer)

[MICHELE]

I have a query based on 2 tables. Table A has loadDates. Table B has loadDateChanges(every time a loadDate changes in Table A, Table B generates a new record traclomg the change).

I have a yes/no field in table B called "update" that automatically updates every night all records that are "no" to "yes".

I have a report that automatically prints before the update every night, and it's printing all records whose update field is still "no". This report shows all of the current changes during that day with the most recent loadDate.

Now that I have that information, they want more. They want the previous loadDate. I'm not sure how to get this. I thought of a sum query using table B, but I don't want the max of the entryDate and time (date of the loadDateChange). I want the date just before the max.

Is there any way to do this?

I hope I have explained this clearly. If not, please let me know and I will do better. I appreciate any help.
Thank you in advance!

Michele

 

[Fizzio]

What do they want to show? - do they just want the loadDate or the information relating to that LoadDate?

How I would retrieve the last but one date is to use a function (which you can then use in the query).

Using a recordset, sort it via date, movelast then moveprevious to return this date.

 

[MICHELE]

I'll try this.

I already have all the data they need to see other than the previous loadDate. From Table B, all that is needed is the most current and second most current loadDate.

Could you show me an example of the function you would used to get that value?

Thanks again!!

 

[Fizzio]

Try this

Function SecondLastDate(lngRecordID as Variant)
Dim rs as dao.recordset
Set rs = currentdb.openrecordset("tblLoadDates",dbopenStatic)
rs.filter = "[AutonumberID] = " & lngRecordID
set rs = rs.openrecordset

rs.movelast
rs.moveprevious
SecondLastDate = rs("DateField")
rs.close
End Function

Call the function in the query by setting a field to PrevDate: SecondLastDate([AutonumberID])
where AutonumberID id the name of your index field.

This assumes that
a) you have an autonumber field
b) your dates are stored chronoligically - if not you will need to sort the data by date first - easily done by setting the recordset as a SQL with an ORDER BY eg

Set rs = currentdb.openrecordset("SELECT * " & _
"FROM tblLoadDate ORDER BY DateField", _
dbOpenStatic)

hth