I am trying to get a query that will return a value of the current record and the value of the immediately preceding record.

The date is contained in a query which contains the values of the portoflia of clients over certain dates. The dates are not consecutive, they are working dates.

The query where the data is located looks like this

Client __DATE___ VALUE
AAA1 02-May-09 €21,320.54
AAA1 05-May-09 €21,418.26
AAA1 06-May-09 €21,267.06
AAA1 07-May-09 €21,310.71
BBB1 02-May-09 €191,298.24
BBB1 05-May-09 €200,705.62
BBB1 06-May-09 €209,719.18
BBB1 07-May-09 €206,822.48
CCC1 02-May-09 €581,510.44
CCC1 05-May-09 €593,714.04
CCC1 06-May-09 €607,035.83
CCC1 07-May-09 €607,726.90

I want a new query that will allow me to calculate the daily return of the portfolia for each client for each date.

In order to do so i will need the value on each specific date - the value of the portofolio on the immediately preceding date. For example for the 05-May-09 the value of the portfolio was €593,714.04. The immediately preceding value was that on the 02-May-09 €581,510.44. The performance was 2.09% for that date.

My problem is that i do not know what function or how to write a code that will instruct ACCESS to get the immediately previous record from the current date.

Any ideas?

I do something similar...I have to grab order numbers from the previous day in order to create an address file. Here is the code I use to find the previous day:



Dim dtmFileDate as Date
Dim stPhoneFileDate as string

'Used to trim out the date from the import file name
dtmFileDate = Right(Me!Text6, stNameLength - 9)
dtmFileDate = Format(dtmFileDate, "MM-DD-YYYY")

stPhoneFileDate = dtmFileDate - 1

If Weekday(dtmFileDate, vbSunday) = 2 Then ' checks to see if the previous day is a sunday
stPhoneFileDate = dtmFileDate - 3 ' if so, sets the phone file date to fridays
End If

stAchPhoneLineCount = DCount("[order_id]", "tblAchData", "[check_date]=#" & stPhoneFileDate & "# And [Imported] = no")

If stAchPhoneLineCount = 0 Then 'checks to make sure there are transactions to export. If not, asks for last day of transactions
stPhoneFileDate = InputBox("The previous business day, " & stPhoneFileDate & " has no transactions" & _
" listed. This is most likely due to a holiday. Please enter the date of the " & _
"previous business day. Thank you")

stPhoneFileDate = Format(stPhoneFileDate, "mm-dd-yyyy")
End If


Basically, it takes the current day and subtracts one. If that day is a Sunday (Which occurs on a Monday morning) it grabs Fridays orders. It then checks to make sure that there are actual records for that date. If not, then it prompts the user for the last business day. I'm sure there are ways to check for holidays, but there are days where I work where we get shut down due to power failure and such.

Once you get the last working day, then you can use that as criteria in an sql statement like so:


stAppendPhoneData = "INSERT INTO tblInternetAddressTemp ( PKGID, CSCUST, SHIPTO2, SHIPTO3, ZIP )" & _
"SELECT tblAchData.PACKAGE_ID, IIf(IsNull(tblcustomers.MiddleInitial),tblcustomer s.FirstName & " & stSpace & _
" & tblcustomers.LastName,tblcustomers.FirstName & " & stSpace & " & tblcustomers.MiddleInitial & " & stSpace & _
" & tblcustomers.LastName) AS FullName, tblCustomers.BILL_TO_AD, tblCustomers.bill_city & " & stSpace & _
" & tblCustomers.bill_st AS CityState, tblCustomers.BILL_ZIP " & _
"FROM tblCustomers INNER JOIN tblAchData ON tblCustomers.CUSTOMER_ID = tblAchData.CUSTOMER_ID " & _
"WHERE (((tblCustomers.BILL_ZIP) Is Not Null) AND ((tblAchData.CHECK_DATE)= #" & stPhoneFileDate & "#));"


This grabs the appropreate customer data and appends it to a temp table, which I then export and email off.

Hope this isn't too confusing....I'm sure there are other ways to do it, but for me, it works. Hope it's helpful.

thanks for that. A bit complicated - will try and give it a go.

If you have any questions, let me know. My Access knowledge is purely self taught...so my methods may not follow any unwritten standards.

i am really sorry this is not working. I will try anothr way with finding records on a specific date. Thanx for the help again