Test for two values within a certain date range 2

[Melagan]

Greetings,

Here are the fields I'm working with, generically:
Field01, Field02, OrderDate

The desired output of this SQL would be one record at most, as I am looking for only the Maximum Date where Field01 = X OR Field02 = X, though I still want to see the values of Field01 or Field02 if they do indeed = X

Additionally, I am only concerned with records whose OrderDate is within the last 120 days.

I will be using this SQL in the context of a recordset called from a visual basic function, i.e.:

dim rstX as Recordset, SQLText
  
  SQLText = "(SQL for above criteria)"
  set rstX = CurrentDB.OpenRecordset(SQLText)

I've had problems with getting the DateDiff() function to work in this context, which is why I wanted to bring it to attention. (Usually the "d" in DateDiff("d", ) highlights in debugger)

Anyway, hopefully that will be enough to get started. Thank you in advance for your help!



~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

you may try this (typed, untested):
SELECT PDC, Max([Opening Date]) AS [2ndMaxOfDate]
FROM Escrows AS E
WHERE Format([Closing Date],'mmm yyyy')=[Enter Month] AND [Recording Status]='On Record' AND PDC Is Not Null
AND [Opening Date]<(Select Max([Opening Date]) From Escrows
Where Format([Closing Date],'mmm yyyy')=[Enter Month] And [Recording Status]='On Record' And PDC=E.PDC)
GROUP BY PDC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

Right on - I ran the query as typed and it did produce some good results. I found that I didn't need most of the criteria in the sub-query though. This is what I ended up with:

SELECT E.PDC, Max(E.[Opening Date]) AS 2ndMaxOfDate
FROM Escrows AS E
WHERE (((Format([Closing Date],'mmm yyyy'))=[Enter Month]) AND ((E.[Recording Status])='On Record') AND ((E.PDC) Is Not Null) AND ((E.[Opening Date])<(Select Max([Opening Date]) From Escrows Where PDC IN ([E.PDC],[E.NDC]))))
GROUP BY E.PDC;

~Melagan
______
"It's never too late to become what you might have been.