Customizing Prompt on Parameter Box for MS Access Query

Posted on 2009-02-14
Last Modified: 2013-11-25
I have a table in which one of the fields is a short date.  I've created a delete query to drop out records before and after a "begin date" and "end date" input by the user in a parameter box.

To keep unnecessary errors to a minimum, I'd like to be able to instruct the user in the Parameter Box as to the correct date format to use:  eg, 01/01/2009

I suspect this requires VBA coding.  If so, how is such a thing accomplished?

The VBA coding behind the macro button to run my delete query is shown below, as well as a screenshot of what the parameter boxes looks like.

Private Sub Macro___Delete_Qry_for_Period_of_Review_Click()

On Error GoTo Err_Macro___Delete_Qry_for_Period_of_Review_Click

DoCmd.SetWarnings False

    Dim stDocName As String

    stDocName = "Delete 4 Period of Review 2-1-09"

    DoCmd.RunMacro stDocName


    Exit Sub


    MsgBox Err.Description

    Resume Exit_Macro___Delete_Qry_for_Period_of_Re


End Sub

Open in new window

Question by:pilk123
    LVL 23

    Accepted Solution

    In your delete query you've defined a parameter [Enter Begin Date] and probable one called [Enter End Date]
    Try renaming the parameters to something like [Enter Begin Date (format as MM/DD/YYYY)] and [Enter End Date (format as MM/DD/YYYY)]
    LVL 11

    Expert Comment

    You could try running the docmd.RunSQL Method which would not require a delete query at all.

    1) Add a textbox on your form namext txtDateCriteria with the appropriate date format set, so that the textbox will only accept a valid date entry.
    b) Add a button called cmdDelete
    c) Tweek the code below and linking it to the cmdDelete button's click method.

    Good Luck
       If isnull(me.txtDateCriteria)=false then
           Dim dDate as date    
           dDate = me.txtDateCriteria
           Dim sql as string
           sql = "DELETE * FROM [tablename] WHERE [datefield]=#" & Format(DateValue,"MM/DD/YYYY") & "#"
           DoCmd.SetWarnings False
           DoCmd.SetWarnings True
       End If
    End Sub

    Open in new window


    Author Closing Comment

    Worked perfectly for the Parameter Box.  Thanks!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now