Applying the same filter to columns in multiple Access tables

Question

Suppose I have two tables in Access that are not joined:

Person
Columns: ID, Firstname, Birthdate
1, Janet, 2/27/2000
2, David, 3/5/2012

Cat
Columns: ID, Catname, Birthdate
1, Paws, 6/20/2009
2, Sparkles, 10/1/2013

How can I set up a macro that (1) inputs a date from the user and (2) in each table (or two queries, one from each table) filters only the people and cats, in two separate queries, whose birthdates are after that date?

You can setup the parameters in each query and write your VBA macro using QueryDefs? See also here: https://stackoverflow.com/questions/16568461/is-it-possible-to-pass-parameters-programmatically-in-a-microsoft-access-update — mtholen, Aug 31 '20 at 00:19
Don't really see need for QueryDefs. Basic parameterized query(s) referencing controls on form for input. Or apply filter criteria to forms or reports. — June7, Aug 31 '20 at 01:19

score 1 · Accepted Answer · answered Aug 31 '20 at 08:21

Have a try one below sub.

Private Sub cmdFilter_Click()
Dim bDate As Date
Dim strSql1 As String
Dim strSql2 As String
Dim qdfPerson As QueryDef
Dim qdfCAT As QueryDef
    
    bDate = InputBox("Enter Birth Date.", "Birth Date", Date)
    strSql1 = "SELECT * FROM Person WHERE BirthDate>=#" & bDate & "#"
    strSql2 = "SELECT * FROM Cat WHERE BirthDate>=#" & bDate & "#"
    
    On Error Resume Next
    'Delete the query if it already exists
    DoCmd.DeleteObject acQuery, "qryPerson"
    DoCmd.DeleteObject acQuery, "qryCat"
    
    Set qdfPerson = CurrentDb.CreateQueryDef("qryPerson", strSql1)
    Set qdfCAT = CurrentDb.CreateQueryDef("qryCat", strSql2)
    
    DoCmd.OpenQuery qdfPerson.Name
    DoCmd.OpenQuery qdfCAT.Name
    
    qdfPerson.Close
    qdfCAT.Close
    Set qdfPerson = Nothing
    Set qdfCAT = Nothing
    
End Sub

Applying the same filter to columns in multiple Access tables

1 Answers1