You could display a custom input box in the button click event that opens the query. This form could then be of any shape/size or color that you want , and could conceivably allow input of both begin and end dates, have a calender control etc..
The form would take the value input, do any validation you want on the dates (which you can't do in the system parameter box) and store it for use in the query in a global var or property.
If the user chooses cancel, the return value/s might be blank or a default.
so the parameter [Begin Date] inthe query becomes something like =fGetBegindate()
Function fGetBegindate() As Date
fGetBegindate = glbBeginDate
end function
Function fGetEnddate() As Date
fGetEnddate = glbEndDate
end function
Sub Query_Button_OnClick()
Dim bValid As Boolean
On Error GoTo Error_err
DoCmd.OpenForm "frmDateForm", windowmode:=acDialog
If IsLoaded("frmDateform") Then ' routine to make sure it loaded
'... validate txtDate
bvalid = true ' if it is of course
If bValid Then
glbBegindate = Nz(Forms!frmdateForm.txtBeginDate)
glbEndDate = Nz(Forms!frmdateForm.txtEndDate)
' or set the variables in the form itself
DoCmd.Close acForm, "frmDateForm"
docmd.openQuery "Queryname"
end if
Else
' form closed by user
' supply default value if you want
' or just don't run the query
glbGetBegindate = Date
End If
Exit_err:
Exit sub
Error_err:
MsgBox Err.Description
GoTo Exit_err
End sub
Function IsLoaded(ByVal strFormName As String) As Integer
Const conObjStateClosed = 0
Const conDesignView = 0
' Returns True if the specified form is open in Form view or Datasheet view.
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function
Note: You can't open a user form from inside a query.
