Results 1 to 4 of 4

Pass listbox value into query criteria

  1. #1
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    278

    Pass listbox value into query criteria

    Hello Experts:

    I hope this is a simple one... yet, I need some help with modifying my VBA so that a selected (numeric) value from a listbox is being passed into a query's criteria.

    Background:
    - Please find attached database which contains 2 tables, 1 query, 1 form

    Process:
    - Upon opening the database, please open the form "F01_Test"
    - Select any values (e.g., 6) from the listbox and then click "Export"
    - Now, open the query "Query1". Given the selected value of "6" (in form), I want the criteria to automatically show: Is Null Or Between Date() And DateAdd('m',6,Date())
    - Alternatively, if I had selected e.g., "3" in the form, I would expect the query criteria to show: Is Null Or Between Date() And DateAdd('m',3,Date())

    My question:
    - How do I modify the VBA in my form so that the select value is passed correctly into the query criteria?

    Thank you,


    EEH
    Attached Files Attached Files

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    380
    try this

    Code:
    Private Sub cmdExportReport_Click()
    
    'Declare variables
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim varItem As Variant
        Dim strCriteria As String
        Dim strSQL As String
        Dim NewDate As Date
    
    
        'Get the database and stored query
        Set db = CurrentDb()
        Set qdf = db.QueryDefs("Query1")
    
    
        strCriteria = Me!lstCFTs
    
    
        NewDate = DateAdd("m", strCriteria, Date)
    
    
        strSQL = "SELECT All_LastName, Mil_PRD FROM T01_StaffMembers WHERE Mil_PRD Is Null Or Mil_PRD Between #" & Date & "# And #" & NewDate & "#"
    
    
    
    
        'Apply the new SQL statement to the query
        qdf.SQL = strSQL
    
    
        DoCmd.OpenQuery "Query1"
    
    
    End Sub

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,253
    Why use VBA to modify query? Set dynamic parameter in query:

    Is Null Or Between Date() And DateAdd("m",[Forms]![F01_Test]![lstCFTs],Date())

    Code should first verify input is provided and only then run procedure.

    If Not IsNull(Me.lstCFTs) Then DoCmd.OpenQuery "Query1"

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    278
    moke123 -- wow... you provided a much more elegant solution. Thank you for assisting me w/ this question. I very much appreciate it!

    Cheers,
    Tom

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-27-2018, 12:30 PM
  2. pass criteria using LIKE and % in VBA query
    By megatronixs in forum Queries
    Replies: 5
    Last Post: 05-19-2016, 11:57 PM
  3. Replies: 10
    Last Post: 11-24-2015, 03:30 PM
  4. Replies: 12
    Last Post: 05-05-2014, 09:23 PM
  5. Replies: 4
    Last Post: 02-14-2013, 09:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums