Need SQL to select from two consecutive dates to display resullts at same time

[LindaRuble]

Hi,

I want to write a query that will select from a date range (not just from one date).
To be specific, I would like to select from anything...



Entered on Date() Where Time()>10:00AM through Date()+1 Where Time <10:00AM.

I know that the above statement is not written correctly but hopefully it will illustrate what I am trying to do.
Is it possible to have two "Where" statements in the same query?

Thanks

Linda

[orange]

You may want to review the Between operator.
http://www.techonthenet.com/sql/between.php

You may also want to look into Subqueries
see http://allenbrowne.com/subquery-01.html

[LindaRuble]

You may want to review the Between operator.
http://www.techonthenet.com/sql/between.php

You may also want to look into Subqueries
see http://allenbrowne.com/subquery-01.html

Hello,

I don't think that Subqueries do what I want to do. I need to be able to select between two different queries based on a certain criteria.


This is what I would like to do:


If the current Time() is before 10:00 AM Then use this Select Query:

SELECT DISTINCT (Mid([ClientDiv].[Client_Division],1,3)) AS ABC2, RTIClientTracker.MR_Assoc, RTIClientTracker.Cut, RTIClientTracker.CutTime, Date() AS TheDate, DayAfter() AS DayAfter
FROM ClientDiv INNER JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division
WHERE (((RTIClientTracker.MR_Assoc)="Linda") AND ((RTIClientTracker.Cut)=DayBefore()) AND ((RTIClientTracker.CutTime)>#12/30/1899 10:0:0#) AND ((RTIClientTracker.Division_Region)='West' Or (RTIClientTracker.Division_Region)='EPS' Or (RTIClientTracker.Division_Region)='North' Or (RTIClientTracker.Division_Region)='South' Or (RTIClientTracker.Division_Region)='Baylor')) OR (((RTIClientTracker.MR_Assoc)="Linda") AND ((RTIClientTracker.Cut)=Date()) AND ((RTIClientTracker.CutTime)<#12/30/1899 10:0:0#) AND ((RTIClientTracker.Division_Region)='West' Or (RTIClientTracker.Division_Region)='EPS' Or (RTIClientTracker.Division_Region)='North' Or (RTIClientTracker.Division_Region)='South' Or (RTIClientTracker.Division_Region)='Baylor'))
ORDER BY (Mid([ClientDiv].[Client_Division],1,3));

If the current Time() is after 10:00 AM Then use this Select Query:

SELECT DISTINCT (Mid([ClientDiv].[Client_Division],1,3)) AS ABC2, RTIClientTracker.MR_Assoc, RTIClientTracker.Cut, RTIClientTracker.CutTime, Date() AS TheDate, DayAfter() AS DayAfter
FROM ClientDiv INNER JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division
WHERE (((RTIClientTracker.MR_Assoc)="Linda") AND ((RTIClientTracker.Cut)=Date()) AND ((RTIClientTracker.CutTime)>#12/30/1899 10:0:0#) AND ((RTIClientTracker.Division_Region)='West' Or (RTIClientTracker.Division_Region)='EPS' Or (RTIClientTracker.Division_Region)='North' Or (RTIClientTracker.Division_Region)='South' Or (RTIClientTracker.Division_Region)='Baylor')) OR (((RTIClientTracker.MR_Assoc)="Linda") AND ((RTIClientTracker.Cut)=DayAfter()) AND ((RTIClientTracker.CutTime)<#12/30/1899 10:0:0#) AND ((RTIClientTracker.Division_Region)='West' Or (RTIClientTracker.Division_Region)='EPS' Or (RTIClientTracker.Division_Region)='North' Or (RTIClientTracker.Division_Region)='South' Or (RTIClientTracker.Division_Region)='Baylor'))
ORDER BY (Mid([ClientDiv].[Client_Division],1,3));


Hope this helps to explain what I want to do.

Thanks

Linda

[orange]

It seems to me that you have a lot of OR conditions for a Constraint but to answer your question. Your logic should break into this if you use named queries. Save your first query eg Query1 and the second eg Query2. Use names that are meaningful to you and adjust the code accordingly.

Sub twoqueries()
   On Error GoTo twoqueries_Error

    If Time() < #10:00:00 AM# Then
        Debug.Print "Less than 10" & Time() & "  so run Query1"
        'Docmd.openQuery("Query1")
    Else
        Debug.Print "Greater than " & Time() & "  so run Query2"
        'Docmd.openQuery("Query2")
    End If

   On Error GoTo 0
   Exit Sub

twoqueries_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure twoqueries of module4"
End Sub

You would remove the ' to activate the commented lines.

??? What happens if it is exactly 10:00:00 AM????

[LindaRuble]

Thank you for your help. Here is what I have so far:


Sub twoqueries_Linda()
On Error GoTo twoqueries_Linda_Error
If Time() <= #10:00:00 AM# Then
Debug.Print "Less than or Equal to 10" & Time() & " so run ClientDiv-EMCARE-Linda-DayAfter"
DoCmd.OpenQuery ("ClientDiv-EMCARE-Linda-DayAfter")
Else
Debug.Print "Greater than " & Time() & " so run ClientDiv-EMCARE-Linda-DayBefore"
DoCmd.OpenQuery ("ClientDiv-EMCARE-Linda-DayBefore")
End If
On Error GoTo 0
Exit Sub
twoqueries_Linda_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure twoqueries_Linda"
End Sub

.................................................. .................................................. .................................................. ......

Now what I need to do is to allow the Sub below to use the result of the If/Then statement:

I feel silly asking but how do you do this? I tried swapping ClientDiv-RTI-Linda for twoqueries_Linda() but that did not work.
Do I need to Dim a variable and set it as a recordset to capture the result of the If/Then statement then plug in the variable name (Set rst_ClDiv = db.OpenRecordset("variable name"))? Any help will be appreciated. Thanks.

Linda


Private Sub Released_Click()
On Error GoTo Err_Released_Click ' Initialize error handling.
Dim db As Database
Dim rst_ClDiv As Recordset
Dim sClDiv As String
Dim sClDivComma As String
Dim sClDivLess As String
Dim iEMB As Integer
Dim sOOBFix As Variant
Dim sOOBFix_Time As Variant
Dim sCut As Variant
Dim sCutTime As Variant
Dim sPri As String
Dim Ns As String
Dim Ns2 As String
Dim OutputString1 As String
Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

Set db = CurrentDb


Set rst_ClDiv = db.OpenRecordset("ClientDiv-RTI-Linda") <------- I NEED TO CHANGE THIS
If rst_ClDiv.EOF = True Then
OutputString1 = ""
Else


rst_ClDiv.MoveFirst


Do While rst_ClDiv.EOF <> True
sClDiv = rst_ClDiv.Fields("ABC2")
sClDivComma = sClDiv & ","
sClDivLess = sClDiv & "<"
iEMB = rst_ClDiv.Fields("EMB_OOB")
sOOBFix = rst_ClDiv.Fields("OOB_Fixed")
sOOBFix_Time = rst_ClDiv.Fields("OOBFix_Time")
sCut = rst_ClDiv.Fields("Cut")
sCutTime = rst_ClDiv.Fields("CutTime")


If (InStr(OutputString1, sClDivComma) <> 0) Or (InStr(OutputString1, sClDivLess) <> 0) Then
OutputString1 = OutputString1 & ""


Else
If ((sOOBFix = sCut) Or ((sOOBFix = DateAdd("d", 1, sCut)) And (sOOBFix_Time <= (#10:00:00 AM#)))) Then 'NORMAL FONT
OutputString1 = OutputString1 & sClDiv & ", "
ElseIf (Weekday(sCut) = 6 And sOOBFix = DateAdd("d", 3, sCut) And sOOBFix_Time <= #10:00:00 AM#) Then 'NORMAL FONT
OutputString1 = OutputString1 & sClDiv & ", "
ElseIf ((sOOBFix = DateAdd("d", -1, Date)) And (iEMB = -1)) Then 'Balanced = Red ' And (sOOBFix_Time > (#10:00:00 AM#))
OutputString1 = OutputString1 & "<strong><font color=""Red"">" & sClDiv & "</font></strong>" & ", "
ElseIf ((sOOBFix = Date) And (sOOBFix_Time <= (#10:00:00 AM#)) And (iEMB = -1)) Then 'Balanced = Red
OutputString1 = OutputString1 & "<strong><font color=""Red"">" & sClDiv & "</font></strong>" & ", "
ElseIf (IsNull(sOOBFix) And (iEMB = -1)) Then 'OOB = Black
OutputString1 = OutputString1 & "<strong><font color=""black"">" & sClDiv & "</font></strong>" & ", "
ElseIf ((sOOBFix = Date) And (sOOBFix_Time > (#10:00:00 AM#)) And (iEMB = -1)) Then 'OOB = Black
OutputString1 = OutputString1 & "<strong><font color=""black"">" & sClDiv & "</font></strong>" & ", "
ElseIf ((sOOBFix > Date) And (iEMB = -1)) Then 'OOB = Black
OutputString1 = OutputString1 & "<strong><font color=""black"">" & sClDiv & "</font></strong>" & ", "
Else
OutputString1 = OutputString1 & sClDiv & ", "
End If


End If

rst_ClDiv.MoveNext
Loop

End If
Debug.Print OutputString1
rst_ClDiv.Close


More, etc ...

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++++++++++++

It seems to me that you have a lot of OR conditions for a Constraint but to answer your question. Your logic should break into this if you use named queries. Save your first query eg Query1 and the second eg Query2. Use names that are meaningful to you and adjust the code accordingly.

Sub twoqueries()
   On Error GoTo twoqueries_Error

    If Time() < #10:00:00 AM# Then
        Debug.Print "Less than 10" & Time() & "  so run Query1"
        'Docmd.openQuery("Query1")
    Else
        Debug.Print "Greater than " & Time() & "  so run Query2"
        'Docmd.openQuery("Query2")
    End If

   On Error GoTo 0
   Exit Sub

twoqueries_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure twoqueries of module4"
End Sub

You would remove the ' to activate the commented lines.

??? What happens if it is exactly 10:00:00 AM????

[orange]

You're losing me.
What exactly are you trying to do ---plain English - forget the Access details?

I did not consider the date that was mentioned in your post #1.
You did not say what would happen if the Time was exactly 10:00 AM.

[LindaRuble]

You're losing me.
What exactly are you trying to do ---plain English - forget the Access details?

I did not consider the date that was mentioned in your post #1.
You did not say what would happen if the Time was exactly 10:00 AM.

Hi,

I actually figured it out. I just put the following at the top of the other Sub that I was trying to use it with:


If Time() <= #10:00:00 AM# Then
Debug.Print Time() & " is less than or Equal to 10 AM so run DayBefore"
Set rst_ClDiv = db.OpenRecordset("ClientDiv-EMCARE-Linda-DayBefore")
Set rst_ClDiv2 = db.OpenRecordset("ClientDiv-RTI-Linda-DayBefore")
Set rst_Notes = db.OpenRecordset("OOB-Clients-Linda-DayBefore")
Else
Debug.Print Time() & " is greater than 10 AM so run DayAfter"
Set rst_ClDiv = db.OpenRecordset("ClientDiv-EMCARE-Linda-DayAfter")
Set rst_ClDiv2 = db.OpenRecordset("ClientDiv-RTI-Linda-DayAfter")
Set rst_Notes = db.OpenRecordset("OOB-Clients-Linda-DayAfter")
End If


And now it has the logic that it needs to work correctly. Also, I added an equal sign to include what happens when it is exactly 10:00:00 AM

Thanks for the help!


Linda