Really Stuck with DMax

[Geoff Codd]

"One day I'll actuallywork out what all the different syntax are"

I have a Query which has three fields

Date - Date Format
Time - Date Format
Present_Reading - Number

I have added a fourth field Previous_Reading which is the following formula

Previous_Reading: DMax("[Reading]","Satchwell_Trend_Logs_(Temp)","[Date] <= #" & Format([Date],"mm/dd/yyyy") & "#")

This works fine, now I need to reduce the results so It looks for the record where the Time is less than Time.

Something like this

Previous_Reading: DMax("[Reading]","Satchwell_Trend_Logs_(Temp)","[Date] <= #" & Format([Date],"mm/dd/yyyy") & "#" And [Time]<[Time])

But I've played about with the syntax and can't seem to get it right.

Does anyone have any ideas

Thanks
Geoff

 

[Mile-O]

Just curious but you have the criteria [Time]<[Time].

How do you expect to get a result from this? I don't understand.

Do you have two fields called [Time]?
Is one of those [Time] a control on a form?

The way I see it, no matter what [Time] will always be equal to [Time] as it looks as though you are evaluating something against itself. ie. if time is 14:00:00 then to say WHERE 14:00:00 is less than 14:00:00 just doesn't seem right at all.


Also, the structure is slightly wrong on what you've put, too.

Here it is amended:

Previous_Reading: DMax("[Reading]","Satchwell_Trend_Logs_(Temp)","[Date] <= #" & Format([Date],"mm/dd/yyyy") & "# And [Time]<[Time]")

 

[Rich]

Date is a reserved word in Access, you should change the field name to something else, it will cause you problems.
This problem is also easier handled with a unique key field ID, do you have such a field?