Dlookup not looking up smetimes

[hmho]

I have table name TbleDailySales and Form name FrmSalesEntry and i have these fields in the table

[ReportDate], [EndingStick], [Amount] Reportdate is Key and date and EndingStick in number and amount is currency.

In the form I'm using Dlookup to bring the previous EndingStick and here is the codes

=Dlookup("[EndingStick]","TbleDailySales","[ReportDate]=Forms![FrmSalesEntry]![ReportDate]-1")

Sometimes I'm getting the previous day's record and sometimes nothing even when there is previous day in the table. Please help as these driving me crazy.

Thanks

 

[Galaxiom]

Try:
=Dlookup("[EndingStick]","TbleDailySales","[TbleDailySales].[ReportDate]=Forms![FrmSalesEntry]![ReportDate]-1")

Access could confusing the form ReportDate field and with the form ReportDate Control when the focus is on the form. It is more reliable to spell out everything.

 

[hmho]

Galaxioma

Thanks you are right it was getting confuse you saved me lot of times and I will remember this in the future. I have been struggling with this few days.

 

[hmho]

Galaximo,

It is still doing the same. Some times I'm getting the previous day's record and sometimes nothing .

 

[gemma-the-husky]

[ReportDate]-1

using reportdate - 1 may be the problem

if you are on a monday, say then the previous date may be a non working date, which would return no records

you could make this a 2-stage process

first find the highest date, less than/equal to the search date

so

searchdate = dmax("reportdate","table","reportdate<= #" & form!reportdate-1 & "#")

and then use searchdate

 

[hmho]

ReportDate is the key and there is report every day