Query to find records not between two dates

Tangawizi said:
In a nutshell, I'm trying to create a report showing who hasn't made a payment between a selected date range, that's it.
Click to expand...
I don't know what the actual field names are, but the logic is:
< Date1 OR > Date2 OR Payment Amount ISNULL (because these people didn't make any payment at all)
 
ebs17 said:
SQL is calculation of quantities. Here:
Active people minus people who paid in the period

SQL: 
PARAMETERS
   parStart Date,
   parEnd Date
;
SELECT
   P.PersonID,
   P.LastName
FROM
   (
      SELECT
         PersonID,
         LastName
      FROM
         tblPersons
      WHERE
         active = true
   ) AS P
      LEFT JOIN
         (
            SELECT
               PersonIDf
            FROM
               tblPayments
            WHERE
               PaymentDay BETWEEN parStart AND parEnd
         ) AS X
         ON P.PersonID = X.PersonIDf
WHERE
   X.PersonIDf IS NULL

@Gasman: For longer periods, almost every payment will not fall within your narrow period, including those made two or three years ago. So you cannot effectively identify active non-payers with this.
Click to expand...
Can you please tell me how to pass the start and end dates, which are chosen from a form, to the sql query?
 
Very gladly. Passing parameters to a query is the basics that you just have to know on the fly. Therefore, some variants in which the completed query is assigned to a form as a data source.

1) Real parameter query and transfer of parameters via VBA
Code: 
' Helper functions in a standard module
Public Function GetRecordsetParamQuery(ByVal MyDB As DAO.Database, ByVal AnyQuery, _
                                       ParamArray QueryParams() As Variant) As DAO.Recordset
    Dim qd As DAO.QueryDef
    Dim i As Long

    If UBound(QueryParams) Mod 2 = 1 Then             ' pairs parameter field-value present
        If QueryExists(MyDB, AnyQuery) Then
            ' saved query

            Set qd = MyDB.QueryDefs(AnyQuery)
        Else
            ' SQL statement

            Set qd = MyDB.CreateQueryDef(vbNullString, AnyQuery)
        End If
        For i = 0 To UBound(QueryParams) Step 2
            qd.Parameters(QueryParams(i)) = QueryParams(i + 1)
        Next
        Set GetRecordsetParamQuery = qd.OpenRecordset(dbOpenDynaset)
        qd.Close
        Set qd = Nothing
    End If
End Function

Private Function QueryExists(ByVal MyDB As DAO.Database, ByVal QueryName As String) As Boolean
    Dim qd As DAO.QueryDef
    For Each qd In MyDB.QueryDefs
        If qd.Name = QueryName Then
            QueryExists = True
            Exit For
        End If
    Next
End Function
SQL: 
' Saved Query ("MyQuery")
PARAMETERS parStart Date, parEnd Date;
SELECT P.PersonID, P.LastName
FROM (SELECT PersonID, LastName FROM tblPersons WHERE active = true) AS P
LEFT JOIN (SELECT PersonIDf FROM tblPayments WHERE PaymentDay BETWEEN parStart AND parEnd) AS X
ON P.PersonID = X.PersonIDf WHERE X.PersonIDf IS NULL
Code: 
' call
Set Forms("frmResult").Recordset = _
      GetRecordsetParamQuery(CurrentDb, "MyQuery", _
           "parStart", Forms.frmInput.txtStartDate, _
           "parEnd", Forms.frmInput.txtEndDate)


2) Public function
Code: 
' functions in a standard module
Public Function getStartDate() As Date
   getStartDate = Forms.frmInput.txtStartDate
End Function

Public Function getEndDate() As Date
   getEndDate = Forms.frmInput.txtEndDate
End Function
SQL: 
' Saved Query ("MyQuery")
SELECT P.PersonID, P.LastName
FROM (SELECT PersonID, LastName FROM tblPersons WHERE active = true) AS P
LEFT JOIN (SELECT PersonIDf FROM tblPayments WHERE PaymentDay BETWEEN getStartDate() AND getEndDate()) AS X
ON P.PersonID = X.PersonIDf WHERE X.PersonIDf IS NULL
Code: 
' call
Forms("frmResult").RecordSource = "MyQuery"


3) Changing the definition of an existing saved query "MyQuery"
Code: 
CurrentDb.QueryDefs("MyQuery").SQL = _
    "SELECT P.PersonID, P.LastName FROM (SELECT PersonID, LastName FROM tblPersonsWHERE active = true) AS P" & _
    " LEFT JOIN (SELECT PersonIDf FROM tblPayments" & _
    " WHERE PaymentDay BETWEEN " & Format(Forms.frmInput.txtStartDate, "\#yyyy\-mm\-dd\#") & _
    " AND " & Format(Forms.frmInput.txtEndDate, "\#yyyy\-mm\-dd\#") & ") AS X" & _
    " ON P.PersonID = X.PersonIDf WHERE X.PersonIDf IS NULL"
Code: 
' call
Forms("frmResult").RecordSource = "MyQuery"


4) Using a parameter table
SQL: 
' Saved Query ("MyQuery")
SELECT P.PersonID, P.LastName
FROM (SELECT PersonID, LastName FROM tblPersons WHERE active = true) AS P
LEFT JOIN (SELECT PersonIDf FROM tblPayments AS P1, tblParameters AS P2
WHERE P1.PaymentDay BETWEEN P2.parStart AND P2.parEnd) AS X
ON P.PersonID = X.PersonIDf WHERE X.PersonIDf IS NULL
Code: 
' call
Dim rs As DAO.Recordset
Set rs = CurrentDb.Openrecordset("tblParameters")
With rs
   .Edit
   !parStart = Forms.frmInput.txtStartDate
   !parEnd = Forms.frmInput.txtEndDate
   .Update
   .Close
End With

Forms("frmResult").RecordSource = "MyQuery"


5) DynamicSQL - composing the SQL statement using VBA
Code: 
Forms("frmResult").RecordSource = _
    "SELECT P.PersonID, P.LastName FROM (SELECT PersonID, LastName FROM tblPersonsWHERE active = true) AS P" & _
    " LEFT JOIN (SELECT PersonIDf FROM tblPayments" & _
    " WHERE PaymentDay BETWEEN " & Format(Forms.frmInput.txtStartDate, "\#yyyy\-mm\-dd\#") & _
    " AND " & Format(Forms.frmInput.txtEndDate, "\#yyyy\-mm\-dd\#") & ") AS X" & _
    " ON P.PersonID = X.PersonIDf WHERE X.PersonIDf IS NULL"


6) Form references and TempVars
In some simple cases, you can use the form references (Forms.frmInput.txtStartDate, Forms.frmInput.txtEndDate) directly in the query. In a case like this, where the parameters would have to be passed into a subquery, this fails. Sometimes you can help yourself by explicitly forcing the evaluation of the text field content with Eval:
Code: 
Forms.frmInput.txtStartDate => Eval(Forms.frmInput.txtStartDate)

TempVar works similar to 2) Public Function: The content of a form text field is passed to a TempVar. Like the form reference above, the TempVar is directly in the query. This variant has comparable problems, e.g. in subqueries.
 
Last edited:
ebs17 said:
Very gladly. Passing parameters to a query is the basics that you just have to know on the fly. Therefore, some variants in which the completed query is assigned to a form as a data source.

1) Real parameter query and transfer of parameters via VBA
Code: 
' Helper functions in a standard module
Public Function GetRecordsetParamQuery(ByVal MyDB As DAO.Database, ByVal AnyQuery, _
                                       ParamArray QueryParams() As Variant) As DAO.Recordset
    Dim qd As DAO.QueryDef
    Dim i As Long

    If UBound(QueryParams) Mod 2 = 1 Then             ' pairs parameter field-value present
        If QueryExists(MyDB, AnyQuery) Then
            ' saved query

            Set qd = MyDB.QueryDefs(AnyQuery)
        Else
            ' SQL statement

            Set qd = MyDB.CreateQueryDef(vbNullString, AnyQuery)
        End If
        For i = 0 To UBound(QueryParams) Step 2
            qd.Parameters(QueryParams(i)) = QueryParams(i + 1)
        Next
        Set GetRecordsetParamQuery = qd.OpenRecordset(dbOpenDynaset)
        qd.Close
        Set qd = Nothing
    End If
End Function

Private Function QueryExists(ByVal MyDB As DAO.Database, ByVal QueryName As String) As Boolean
    Dim qd As DAO.QueryDef
    For Each qd In MyDB.QueryDefs
        If qd.Name = QueryName Then
            QueryExists = True
            Exit For
        End If
    Next
End Function
SQL: 
' Saved Query ("MyQuery")
PARAMETERS parStart Date, parEnd Date;
SELECT P.PersonID, P.LastName
FROM (SELECT PersonID, LastName FROM tblPersons WHERE active = true) AS P
LEFT JOIN (SELECT PersonIDf FROM tblPayments WHERE PaymentDay BETWEEN parStart AND parEnd) AS X
ON P.PersonID = X.PersonIDf WHERE X.PersonIDf IS NULL
Code: 
' call
Set Forms("frmResult").Recordset = _
      GetRecordsetParamQuery(CurrentDb, "MyQuery", _
           "parStart", Forms.frmInput.txtStartDate, _
           "parEnd", Forms.frmInput.txtEndDate


2) Public function
Code: 
' functions in a standard module
Public Function getStartDate() As Date
   getStartDate = Forms.frmInput.txtStartDate
End Function

Public Function getEndDate() As Date
   getEndDate = Forms.frmInput.txtEndDate
End Function
SQL: 
' Saved Query ("MyQuery")
SELECT P.PersonID, P.LastName
FROM (SELECT PersonID, LastName FROM tblPersons WHERE active = true) AS P
LEFT JOIN (SELECT PersonIDf FROM tblPayments WHERE PaymentDay BETWEEN getStartDate() AND getEndDate()) AS X
ON P.PersonID = X.PersonIDf WHERE X.PersonIDf IS NULL
Code: 
' call
Forms("frmResult").RecordSource = "MyQuery"


3) Changing the definition of an existing saved query "MyQuery"
Code: 
CurrentDb.QueryDefs("MyQuery").SQL = _
    "SELECT P.PersonID, P.LastName FROM (SELECT PersonID, LastName FROM tblPersonsWHERE active = true) AS P" & _
    " LEFT JOIN (SELECT PersonIDf FROM tblPayments" & _
    " WHERE PaymentDay BETWEEN " & Format(Forms.frmInput.txtStartDate, "\#yyyy\-mm\-dd\#") & _
    " AND " & Format(Forms.frmInput.txtEndDate, "\#yyyy\-mm\-dd\#") & ") AS X" & _
    " ON P.PersonID = X.PersonIDf WHERE X.PersonIDf IS NULL"
Code: 
' call
Forms("frmResult").RecordSource = "MyQuery"


4) Using a parameter table
SQL: 
' Saved Query ("MyQuery")
SELECT P.PersonID, P.LastName
FROM (SELECT PersonID, LastName FROM tblPersons WHERE active = true) AS P
LEFT JOIN (SELECT PersonIDf FROM tblPayments AS P1, tblParameters AS P2
WHERE P1.PaymentDay BETWEEN P2.parStart AND P2.parEnd) AS X
ON P.PersonID = X.PersonIDf WHERE X.PersonIDf IS NULL
Code: 
' call
Dim rs As DAO.Recordset
Set rs = CurrentDb.Openrecordset("tblParameters")
With rs
   .Edit
   !parStart = Forms.frmInput.txtStartDate
   !parEnd = Forms.frmInput.txtEndDate
   .Update
   .Close
End With

Forms("frmResult").RecordSource = "MyQuery"


5) DynamicSQL - composing the SQL statement using VBA
Code: 
Forms("frmResult").RecordSource = _
    "SELECT P.PersonID, P.LastName FROM (SELECT PersonID, LastName FROM tblPersonsWHERE active = true) AS P" & _
    " LEFT JOIN (SELECT PersonIDf FROM tblPayments" & _
    " WHERE PaymentDay BETWEEN " & Format(Forms.frmInput.txtStartDate, "\#yyyy\-mm\-dd\#") & _
    " AND " & Format(Forms.frmInput.txtEndDate, "\#yyyy\-mm\-dd\#") & ") AS X" & _
    " ON P.PersonID = X.PersonIDf WHERE X.PersonIDf IS NULL"


6) Form references and TempVars
In some simple cases, you can use the form references (Forms.frmInput.txtStartDate, Forms.frmInput.txtEndDate) directly in the query. In a case like this, where the parameters would have to be passed into a subquery, this fails. Sometimes you can help yourself by explicitly forcing the evaluation of the text field content with Eval:
Code: 
Forms.frmInput.txtStartDate => Eval(Forms.frmInput.txtStartDate)

TempVar works similar to 2) Public Function: The content of a form text field is passed to a TempVar. Like the form reference above, the TempVar is directly in the query. This variant has comparable problems, e.g. in subqueries.
Click to expand...
Thanks so much for your help, it's really appreciated. Hopefully I'll be able get things working now.
 
You must log in or register to reply here.

Similar threads

medihub_guy
Solved Searching between two dates just doesnt seem to work properly
Replies
15
Views
390
Pat Hartman
P
G
Filtering on Date fields problem
Replies
8
Views
272
azhar2006
A
H
Case Statement to get value in Access Query
Replies
11
Views
326
Pat Hartman
P
K
Solved Insert Into - Select
Replies
18
Views
259
arnelgp
arnelgp
H
Solved Finding names from table of not duplicated rows
Replies
11
Views
216
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom