Search Date using a Inputbox?

[Guest]

I'm trying to use an Inputbox to search for a Date.

Function SearchOrderList_Date()
Dim strSearch As String
'------------------------------------------------------------
'Set the input Box
'------------------------------------------------------------
strSearch = InputBox("What is the Date (mm/dd/yy)?", " Order List by
Date")
If strSearch = Null Then
Exit Function
Else
'------------------------------------------------------------
'This will close the from
'------------------------------------------------------------
CloseForm
'------------------------------------------------------------
'Open Form and Filter
'------------------------------------------------------------
stDocName = "frmOrderList"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.ApplyFilter "Find", "([qryListOrder]![DATEORD] = " & strSearch
& ")"
'------------------------------------------------------------
'Maximize
'------------------------------------------------------------
DoCmd.Maximize
End If

Exit_cmdOpenForm_Click:
Exit Function

Err_cmdOpenForm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenForm_Click

End Function

 

[Graham Mandeno]

Hi Zach

You must enclose a date it # characters when it is used in a SQL expression,
otherwise the /s will be seen as division operators and a date line 06/02/05
will be "6 divided by 2 divided by 5", or 0.6!

Also, you should be using your stLinkCriteria variable to apply the filter
to the form as it is opened:
stlinkCriteria = "[DATEORD] = #" & strSearch & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Also, your test for strSearch = Null will do nothing, as Null is not a word
that is known to VB(A) and besides, clicking "Cancel" on an input box
returns an empty string, not a null value. You would be better to check
that the user has entered a valid date:
If IsDate( strSearch ) Then
...

 

[Marshall Barton]

I'm trying to use an Inputbox to search for a Date.

Function SearchOrderList_Date()
Dim strSearch As String
'------------------------------------------------------------
'Set the input Box
'------------------------------------------------------------
strSearch = InputBox("What is the Date (mm/dd/yy)?", " Order List by
Date")
If strSearch = Null Then
Exit Function
Else
'------------------------------------------------------------
'This will close the from
'------------------------------------------------------------
CloseForm
'------------------------------------------------------------
'Open Form and Filter
'------------------------------------------------------------
stDocName = "frmOrderList"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.ApplyFilter "Find", "([qryListOrder]![DATEORD] = " & strSearch
& ")"

[snip]


Several problems here.

You are assigning the result of the input box to a string
variable, which is good because InputBox returns a string.
However, you are testing for Null. Not only that, but you
are test it by trying to compare it using =, which will
always fail (use the IsNull function to test for Null).

Next, you are then using the string returned from InputBox
as a date. This can be done, but you need to use the date
delimiter (#) character so Access knows how to use the
string.

I have no idea what the "Find" FilterName argument is
supposed to do here. Normally, it is not used, so remove it
if it causes any trouble.

After all that harping on you, you only need to make two (or
three?) changes:
If strSearch = "" Then
and
DoCmd.ApplyFilter "Find", "DATEORD=#" & strSearch & "#"