Query to find records not between two dates (1 Viewer)

LarryE

LarryE

Active member
Local time
Today, 03:22
Joined
Aug 18, 2021
Messages
591
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)
 
T

Tangawizi

New member
Local time
Today, 11:22
Joined
Aug 11, 2023
Messages
7
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?
 
E

ebs17

Well-known member
Local time
Today, 12:22
Joined
Feb 7, 2020
Messages
1,946
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:
T

Tangawizi

New member
Local time
Today, 11:22
Joined
Aug 11, 2023
Messages
7
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

P
VBA query code to select a date between a start and finished date
Replies
5
Views
267
cheekybuddha
cheekybuddha
G
EXACT NUMBER OF HOURS BETWEEN TWO DATES AND TIME
Replies
13
Views
531
The_Doc_Man
The_Doc_Man
E
Solved Query to provide annual dates in an interval
Replies
16
Views
477
arnelgp
arnelgp
T
Date Filter: Between Yesterday and 365 days
Replies
6
Views
289
Pat Hartman
P
B
I'm too stupid to work this out
Replies
5
Views
511
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

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