Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Posts
    13

    Unanswered: Pop up screen for Date Range

    Hi Team,

    I have a database, there is a form when agent click on button it populate data on a query, all this is inside VBA using SQL, I would like to add date filter, when someone click on the button pop up screen request firstdate and enddate, the field name is dtDate, how can I write inside VBA, below is my code thank you


    Dim db As DAO.Database
    Dim qd As DAO.QueryDef

    Set db = CurrentDb()
    Set qd = db.QueryDefs("Table1")

    qd.SQL = "SELECT [Type],SUM([Total]) AS 'Total Type Amount' " & _
    " ,COUNT(*) AS 'Total Count' " & _
    "FROM rpt.vwtable2" & _
    "GROUP BY [Type]"


    qd.Close

    Set qd = Nothing
    Set db = Nothing

    'Debug.Print qd.SQL
    DoCmd.OpenQuery "table1"

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    look at the InputBox() function and the WHERE clause for an sql.
    Me.Geek = True

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Or consider a custom dialog box.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Sep 2008
    Posts
    13
    Thank you guys i used the inputbox(), below my changes and it work very good

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim sStartDate as String
    Dim sEndDate as String

    sStartDate = InputBox("Enter Start Date", "StartDate", Now() - 30)
    sEndDate = InputBox("Enter End Date", "StartDate", Now())

    Set db = CurrentDb()
    Set qd = db.QueryDefs("Table1")

    qd.SQL = "SELECT [Type],SUM([Total]) AS 'Total Type Amount' " & _
    " ,COUNT(*) AS 'Total Count' " & _
    "FROM rpt.vwOracleTransactions " & _
    "Where [GeneralDate] between '" & sStartDate & "' and '" & sEndDate & "' " & _
    "GROUP BY [Type]"


    qd.Close

    Set qd = Nothing
    Set db = Nothing

    'Debug.Print qd.SQL
    DoCmd.OpenQuery "table1"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •