Query most recent date

[pbellc]

I have a database that houses daily data that is entered everyday for the previous day (for example yesterday's data will be entered today, and today's data will be entered tomorrow). The problem I have is that I need to query the previous days data to pull a field for calculations today. In other words I need a piece of data from 8/1/03 to run calculations that are based partly on data entered today in a report for today.

I tried this query:
SELECT [Adjustments].[Date], [Adjustments].[Type], [Adjustments].[Adjustments]
FROM Adjustments
WHERE (date)=(SELECT MAX(Adjustments.Date) from Adjustments WHERE Date<DATE());

but because we enter yesterday's data today, the max dat<date() is yesterday, and I need Friday's data.

I really hope I am explaining this correctly. Any thoughts would be greatly appreciated.

pc

 

[JoanneM]

First let me say its bad practice to call a field date as it is intrinsic to Access
I would Suggest abbreviating it to Dt
Here is a little function that may help using Dt

create a query add the table that contains the date info and copy the line below into one of the columns

yd:YesterdaysDt(dt)

copy the function below into a module
and run your query


Function YesterdaysDt(dt As Date)
Dim myweekday As Integer
myweekday = Weekday(dt)

Select Case myweekday
Case 2 ' &quot;monday
YesterdaysDt = dt - 3

Case 1 ' &quot;monday
YesterdaysDt = dt - 2

Case Else
YesterdaysDt = dt - 1
End Select
End Function


Include the Key field and include this in your original query - joining on the key field with
= yd in the criteria fline of the date field

hope this makes sense

regards

Jo

 

[tledwards]

Try adding a -1 after you the date function like this.

Where Date< Date()-1

 

[psemianonymous]

Yeah, unless you change your field name, Access will assume &quot;Date&quot; means &quot;the Date() function&quot;. Or it will assume that &quot;Date&quot; always means &quot;the [date] field&quot;. Either way, that's bad.


The workaround is to use [Date] every time you need to reference it as a field. But do us all (including you) a favor and rename the field.