DLookup with Date Criteria

[nclsgna]

Hi. I have a table [qry_all] with the format show:

[Date] [DrBalCur] [DrBalODu] [DrBalRes] [DrBalTot] [PrDt]
28-Oct-05 1,002.00 29,379.66 753.83 31,135.48 27-Oct-05
29-Oct-05 1,949.07 29,379.66 322.79 31,651.53 28-Oct-05

What I need is an extra field [PrDr] that looks up automatically the prior
day's balance on DrBalTot. I have tried a DLookup with the fomula:

PrDr: DLookUp("[DrBalTot]","qry_all","DateSerial(Year([qry_all]![Date]),
Month([qryt_all]![Date]),Day([qry_all]![Date]))=DateSerial(Year([PrDt]),Month
([PrDt]),Day([PrDt]))"))

but I am getting a null result. Can anybody assist?

 

[Tom Lake]

Hi. I have a table [qry_all] with the format show:

[Date] [DrBalCur] [DrBalODu] [DrBalRes] [DrBalTot] [PrDt]
28-Oct-05 1,002.00 29,379.66 753.83 31,135.48 27-Oct-05
29-Oct-05 1,949.07 29,379.66 322.79 31,651.53 28-Oct-05

What I need is an extra field [PrDr] that looks up automatically the prior
day's balance on DrBalTot. I have tried a DLookup with the fomula:

PrDr: DLookUp("[DrBalTot]","qry_all","DateSerial(Year([qry_all]![Date]),
Month([qryt_all]![Date]),Day([qry_all]![Date]))=DateSerial(Year([PrDt]),Month
([PrDt]),Day([PrDt]))"))

Try this:

PrDr: DLookUp("[DrBalTot]","qry_all","[qry_all]![Date]= #" & [PrDt] & "#")

Tom Lake

 

[nclsgna via AccessMonster.com]

Thanks a lot. Its working perfectly.

Hi. I have a table [qry_all] with the format show:

[quoted text clipped - 8 lines]

Month([qryt_all]![Date]),Day([qry_all]![Date]))=DateSerial(Year([PrDt]),Month
([PrDt]),Day([PrDt]))"))

Try this:

PrDr: DLookUp("[DrBalTot]","qry_all","[qry_all]![Date]= #" & [PrDt] & "#")

Tom Lake