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.

 

[lameid]

SQLText = "Select X As Expr1, Max(OrderDate) as MaxOfOrderDate From Table Where Field01 = X or Field02 = X Group By X

 

[Melagan]

Right on man, that is a great start for me. The "Group By X" really pointed me in the right direction. I'll post my full function as soon as I get it working correctly, or if I have more questions =)


P.S. I just substituted my real field names in for the generic data and applied it to the context of my function and it worked. Nicely done lameid!


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

 

[Melagan]

Now this is more of a VB question - but should the recordset contain "no current record", how can I tell my boolean function to return False instead of error?



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

 

[Golom]

A recordset will return "No Current Record" is it's position before the first record (BOF) or after the last one (EOF). An empty recordset will have both BOF and EOF set to true. You can test those values with

If rs.EOF and rs.BOF Then
   [COLOR=green]' Recordset is empty[/color]
ElseIf rs.EOF Then
   [COLOR=green]' Before the first record[/color]
   rs.MoveFirst
ElseIf rs.BOF Then
   [COLOR=green]' Recordset is beyond the last record[/color]
   rs.MoveLast
End If

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Melagan]

Awesome - now that I have my function returning the correct evaluation, I need to get rid of the message box that says "No Current Record", as I have a custom message box that tells the user more relevant information. Any idea there?

Perhaps it is a specific error code I can trap in my error handler ? like:

err_handler
if err = x then
msgbox "More Relevant Prompt", vbinformation, "Hi"
else
msgbox err.description
end if

Or should I just "On Error Resume Next" ?




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

 

[Golom]

You probably want to fix your code that's generating the error rather than just trapping and ignoring it. For example, if the following code was creating the error

myValue = rs.fields(2).Value

then you would fix it with

If rs.EOF and rs.BOF Then
   MsgBox "There are no records to Process"
Else
   myValue = rs.fields(2).Value
Endif

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Melagan]

I'm already doing exactly that, but I get two message boxes. The one I want when rs.eof and rs.bof evaluate to true, and the one that access pops up by default when there are "no current records", probably because I have the generic error trapping procedure:

go error goto err_handler

err_handler:
msgbox err.description
resume exithandler

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

 

[Melagan]

Now back to the original SQL - is there any way I can grab the "2nd highest" date, or like, Max()-1 ?


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

 

[Melagan]

I'm thinking the right way to do this would be to do away with the Max([Order Date]) in my where clause, then add:
ORDER BY OrderDate

...then somehow scan that recordset, go to the end, then back up one record and tag that as the one I want. How to do that, I have no clue =)


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

 

[Golom]

You should be able to get rid of the Access message with

DoCmd.SetWarnings False

To get the second highest date

Select MAX(DateField)
From myTable
Where DateField < (Select MAX(DateField) From myTable)

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Melagan]

The subquery does seem to be working, but the DoCmd.SetWarnings False doesn't get rid of the "No Current Records" prompt.

Big kuddos to Golom so far for being such a great help. *



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

 

[lameid]

In order for us to help with the error message, I think we need to see the whole procedure...

Also, Golom's SQL did not include criteria like your original post requested. If you need more help with it, I'm sure either of us can fix it.

 

[Melagan]

I was able to take the ideas and logic from your posts to adapt it to my own needs. As for the procedure, here it is:

Public Function Target(Agent As String) As Boolean
On Error GoTo Err_Handler
Dim rstAgent As Recordset, SQLText
Dim lngMaxDateDiff As Long
Dim temp As String
DoCmd.SetWarnings False

SQLText = "SELECT '" & Agent & "' as X, Max([Opening Date]) as MaxOfDate " _
    & "FROM Escrows " _
    & "WHERE [Opening Date] < (SELECT MAX([Opening Date]) FROM Escrows) AND " _
    & "PDC = '" & Agent & "' OR NDC = '" & Agent & "' " _
    & "GROUP BY '" & Agent & "'"
Set rstAgent = CurrentDb.OpenRecordset(SQLText)

If rstAgent.EOF And rstAgent.BOF Then
    MsgBox Agent & " has no previous order.", vbInformation, "Target"
    Target = True
End If
    
lngMaxDateDiff = DateDiff("d", rstAgent![MaxOfDate], Date)

If lngMaxDateDiff >= 180 Then
    Target = True
    MsgBox Agent & " is likely a target! Their last open order was on " _
        & rstAgent![MaxOfDate] & ".", vbInformation, "Target!"
Else
    Target = False
End If
    
ExitHandler:
Exit Function

Err_Handler:
MsgBox Err.Description
Resume ExitHandler

End Function

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

 

[Melagan]

It's funny when you step away from something then come back to it the next day -- after I posted that code, it occured to me that I could probably just do this after the first IF test:

If rstAgent.EOF And rstAgent.BOF Then
MsgBox Agent & " has no previous order.", vbInformation, "Target"
Target = True
Exit Function
End If



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

 

[Melagan]

Just incase anyone else is following this thread - I revised my SQL to give me exactly what I needed. As of now, the function works perfect. Now that it works, I'll share in detail what it's for, in the context of my project.

The table tracks activity on Real Estate transactions. The "PDC" and "NDC" fields are for Real Estate agent names. Normally, there are "two sides" to Real Estate transations, or rather, two agents. One agent represents the buyer and the other represents the seller, but sometimes the same agent can represent both parties.

As a title and escrow company, we market to those Real Estate agents to bring us business, so it's important to know how long it's been since they've last brought us a deal, hence the need for this function. (Agents on the "PDC" side are generally the ones who have the control to direct where their escrow goes).

I wanted to be able to test new orders based on the Agent who was in the PDC field by looking at their order history on BOTH the PDC and NDC fields. If that agent hadn't been in a transaction within the last six months on EITHER the PDC or NDC side, they are considered a "Target", therefor the Function Target() should evaluate to true.

Whew. Big thanks to Lameid and Golom for all of their help on this one.

Final Code:

Public Function Target(Agent As String) As Boolean
On Error GoTo Err_Handler
Dim rstAgent As Recordset, SQLText
Dim intMaxDateDiff As Long

    SQLText = "SELECT '" & Agent & "' as X, Max([Opening Date]) as MaxOfDate " _
        & "FROM Escrows " _
        & "WHERE (((Escrows.PDC)= '" & Agent & "') AND ((Escrows.[Opening Date])" _
        & "<(SELECT Max([Opening Date]) FROM Escrows))) " _
        & "OR (((Escrows.NDC)= '" & Agent & "') AND ((Escrows.[Opening Date])" _
        & "<(SELECT Max([Opening Date]) FROM Escrows))) " _
        & "GROUP BY '" & Agent & "'"
    Set rstAgent = CurrentDb.OpenRecordset(SQLText)

    If rstAgent.EOF And rstAgent.BOF Then
        MsgBox Agent & " has no records in NATTrack.", vbInformation, "Target"
        Target = True
        Exit Function
    End If
    
intMaxDateDiff = DateDiff("d", rstAgent![MaxOfDate], Date)

    If intMaxDateDiff >= 180 Then
        Target = True
        MsgBox Agent & " is likely a target! Their last open order was on " _
            & rstAgent![MaxOfDate] & ".", vbInformation, "Target!"
    Else
        Target = False
        MsgBox Agent & " is not a target. Last order date: " _
            & rstAgent![MaxOfDate] & ".", vbInformation, "Target"
    End If
    
ExitHandler:
Exit Function

Err_Handler:
MsgBox Err.Description
Resume ExitHandler

End Function

For now, case closed =)


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

 

[PHV]

Doesn't this simpler SQL works the same ?
SQLText = "SELECT '" & Agent & "' as X, Max([Opening Date]) as MaxOfDate " _
& "FROM Escrows " _
& "WHERE '" & Agent & "' In ([PDC],[NDC]) AND [Opening Date]" _
& "<(SELECT Max([Opening Date]) FROM Escrows) " _
& "GROUP BY '" & Agent & "'"

I wonder if we even need the GROUP BY clause ...

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

 

[Melagan]

Well where have YOU been? =) That SQL seems to do the trick as well. I've never used the "In ()" statement before...seems to make grouping AND's and OR's a lot simpler.


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

 

[lameid]

Well as long as we are tweaking, it may run faster to use a "Not In" instead of "<"... Really not sure on that one. You might not have enough data to really test. It is not really the same thing, just the same results.

SQLText = "SELECT '" & Agent & "' as X, Max([Opening Date]) as MaxOfDate " _
& "FROM Escrows " _
& "WHERE '" & Agent & "' In ([PDC],[NDC]) AND [Opening Date]" _
& "NOT IN (SELECT Max([Opening Date]) FROM Escrows) " _
& "GROUP BY '" & Agent & "'

 

[Melagan]

I hate to come back to this thread after the fact when it's not fresh in anyone's mind, but I have an addional task I'd like to do with this idea:

Would there be a good way to run similar SQL but instead of looking at one value (Agent) and returning it's "second to highest" Max date, could it be done to analyze a whole set of records and return each one's "second to highest" max date?

The recordset I'm wanting to get returned vales for:

SELECT Escrows.PDC
FROM Escrows
WHERE Format([Closing Date],"mmm yyyy")=[Enter Month] AND [Recording Status]="On Record" AND IsNull([PDC])=False

__

Now I'd like to find the "second to highest" max date of all of the PDC's that meet the above criteria. Is it possible to do this in one query?



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