• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 807
  • Last Modified:

Customizing Prompt on Parameter Box for MS Access Query

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

1 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)]
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

pilk123Author Commented:
Worked perfectly for the Parameter Box.  Thanks!

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now