Breakout Query

[TylerTand]

I have a table with the following Fields:
Symbol
DeliveryMonth/Year
DDate
Close

I am trying to evaluate today's close in relation to the close from the previous 20 trading days to see if it is a new High. I can create a query that looks at the contracts between date()-21 and date()-1 to get the last 20 days. Then I need to see if today's close is greater than the maximum from this range. This is where I need some help. I thought I could use:

DDate:
Criteria: date()

Close:
Criteria: >(Select MAX([Close] where date between date()-21 and date()-1) - The name of the Date field is DDate since "date" is a keyword in access. I wasn't sure if I needed to put the name of the field here or the word date?

But this doesn't quite do it. It is the max of a range criteria that is messing me up. Can you guys help me to get this to work?
Thanks for your help,
Tyler

 

[pbaldy]

This is a shot in the dark, but try:

>(Select MAX([Close]) FROM TableName where DDate between date()-21 and date()-1)

but given that you have symbols in there, you'd want to further restrict by that I suspect.