Public Function fnStartDate(Optional varDate As Variant = Null, Optional Reset As Boolean) As Date
Static myDate As Variant
Dim strDate As String
If Reset = True Then myDate = Null
Do
If IsNull(varDate) = False Then
strDate = varDate
ElseIf IsNull(myDate) = False Then
strDate = Format(myDate, "mm/dd/yy")
Else
strDate = InputBox("Enter start date (mm/dd/yy)")
End If
'you could insert multiple tests here, if needed
If IsDate(strDate) = False Then
MsgBox "Enter a valid date!"
Else
myDate = CDate(strDate)
End If
Loop While IsNull(myDate)
fnStartDate = myDate
End Function
and
Public Function fnEndDate(Optional varDate As Variant = Null, Optional Reset As Boolean) As Date
Static myDate As Variant
Dim strDate As String
If Reset = True Then myDate = Null
Do
If IsNull(varDate) = False Then
strDate = varDate
ElseIf IsNull(myDate) = False Then
strDate = Format(myDate, "mm/dd/yy")
Else
strDate = InputBox("Enter end date (mm/dd/yy)")
End If
'you could insert multiple tests here, if needed
If IsDate(strDate) = False Then
MsgBox "Enter a valid date!"
Else
myDate = CDate(strDate)
End If
Loop While IsNull(myDate)
fnEndDate = myDate
End Function
This code is designed to retain the values of myDate between calls to the function, so it will ask you for the Start date once, and the End date once using this syntax. You can also pass a value to each of the functions which would be useful if you enter a value in control, you could use the AfterUpdate of that control to set the value of the function, so that the query would not even ask you for the value. Notice that in the above SQL string, I passed the Reset value of True to each of the functions to force the function to ask you for a date, but in the second part of the query, I left the optional parameters out, so that it will use the values that were already entered.
PARAMETERS [enter beg date] DateTime, [enter end date] DateTime;
Select * from qry1
Where Date between [enter beg date] and [enter end date]
union
Select * from qry2
where date between [enter beg date] and [enter end date] and part = 'M'
(users can/will type invalid dates, leave things blank, ...etc)
Instead, create a form with two date boxes.
Then make the source for your query:
Select * from qry1
Where Date between Forms!YourForm!txtBegDate AND Forms!YourForm!txtEndDate
union
Select * from qry2
where date between Forms!YourForm!BegDate AND Forms!YourForm!EndDate AND and part = 'M'
JeffCoachman