dlookup with date

[94Sport5sp]

Hi:

I have a select query to display record from a table. I would like to lookup the closing value for the corresponding record for yesterday. I have coded the following DLookup to get that data and it works if I hard code the date. Now I need to change it to be current date of current record minus 1. So far it is not working. Here is the working DLOOKUP

DLookUp("CloseInv","Stocktbl","GroupDept =" & [GroupDept] & " and int(SaleDate) = #4/30/2012#") AS EndInv

In the above SaleDate is the date for the current record and I would like SaleDate -1 to equal 4/30/2012 in the above example

DLookUp("CloseInv","Stocktbl","GroupDept =" & [GroupDept] & " and int(SaleDate) = " & Int(Saledate -1) ") AS EndInv

returns a zero value.

SaleDate is a full date plus time and the time for what I want is not relevant.

What am I missing?

Thanks

 

[MarkK]

Try...

DateValue(SaleDate)
[COLOR="Green"]'as opposed to [/COLOR]
int(SaleDate)

...to drop the time from your date value.
And I don't understand what you mean here...

I would like SaleDate -1 to equal 4/30/2012 in the above example

You could do...

DLookUp("CloseInv","Stocktbl", _
  "GroupDept =" & [GroupDept] & " and DateValue(SaleDate) - 1 = #4/30/2012#")

...but this expression...

DateValue(SaleDate) - 1 = DateValue(SaleDate)

...will never evaluate to True.

 

[94Sport5sp]

Hi lagbolt:

Thanks for the reply. For the query that I have set up the select statement returns information from the table such as RegId, SaleDate, SaleAmt, etc. The records are restricted with a where clause to a specific date. To the retrieved records I would like to add the field CloseInv from the previous days data, hence the DLOOKUP. The where clause of the Dlookup should find CloseInv where GroupDept are the same and where the lookup records are one day less. In my sample data the records displayed are for May 1, 2012 so the Dlookup should find CloseInv for the previous day of Apr 30, 2012. In the returned records, the field, SaleDate will have May1 so the Dlookup should find SaleDate -1 or Apr 30. When I hard code the date Apr 30, 2012, the Dlookup works to deliver the information I want. So now I need a way to build the SQL statement dynamically to retrieve any days data I want.

Thanks for your thoughts