What's causing Illegal function call??

[gchichester via AccessMonster.com]

After adding another search option in my code I started receiving
a "Illegal function call", I discovered that my strInput boxes should
be Variant data type so I made the change but I'm still receiving the
same error when any one of the search criteria is null/blank.

Private Sub cmdSearchBookings_Click()
Dim frm As Form
Dim strMsgShipper As String
Dim strMsgConsignee As String
Dim strFilter As String
Dim varInputBookingNumber As Variant
Dim varInputShipper As Variant
Dim varInputConsignee As Variant

Forms!frmHomePort.Visible = False
DoCmd.OpenForm "frmSearchEuropeNotice"

Set frm = Forms!frmSearchEuropeNotice.Form

strMsgBookingNumber = "Enter Booking Number " & "Or Leave blank if
searching by Shipper"
strMsgShipper = "Enter Shipper Name " & "followed by an asterisk."
strMsgConsignee = "Enter Consignee Name " & "followed by an asterisk."

varInputBookingNumber = InputBox(strMsgBookingNumber)
varInputShipper = InputBox(strMsgShipper)
varInputConsignee = InputBox(strMsgConsignee)


If Not IsNull(varInputBookingNumber) Then
strFilter = strFilter & " AND " & BuildCriteria("BookingNoticeBookingNo",
dbText, varInputBookingNumber)
End If

If Not IsNull(varInputShipper) Then
strFilter = strFilter & " AND & BuildCriteria("BookingNoticeShipper",
dbText, varInputShipper)
End If

If Not IsNull(varInputConsignee) Then
strFilter = strFilter & " AND & BuildCriteria("BookingNoticeConsignee",
dbText, varInputConsignee)
End If

'MsgBox Mid(strFilter, 6)

frm.Filter = Mid(strFilter, 6)

frm.FilterOn = True

End Sub


Any and all suggestions would be appreciated

Gilc

 

[Stefan Hoffmann]

hi Gilc,

If Not IsNull(varInputBookingNumber) Then
strFilter = strFilter& " AND "& BuildCriteria("BookingNoticeBookingNo",
dbText, varInputBookingNumber)

As BuildCriteria expects a String as last parameter I would try it using
an explicit cast:

BuildCriteria("BookingNoticeBookingNo", _
dbText, _
CStr(varInputBookingNumber))

Any and all suggestions would be appreciated

Where does the debugger stop in your code?


mfG
--> stefan <--

 

[gchichester via AccessMonster.com]

Thanks Stefan
I tried your suggestion but received the same error.

As to where the debugger stops it's at the first search Criteria that's null,

In this case it was booking #.

strFilter = strFilter & " AND " & BuildCriteria("BookingNoticeBookingNo",
dbText, CStr(varInputBookingNumber))

 

[Dirk Goldgar]

After adding another search option in my code I started receiving
a "Illegal function call", I discovered that my strInput boxes should
be Variant data type

No, they shouldn't. Or, rather, they don't need to be, since the value
returned by InputBox is a string, and the Expression argument of
BuildCriteria is a string.

so I made the change but I'm still receiving the
same error when any one of the search criteria is null/blank.
[...]
Dim varInputBookingNumber As Variant
[...]
varInputBookingNumber = InputBox(strMsgBookingNumber)
[...]
If Not IsNull(varInputBookingNumber) Then
strFilter = strFilter & " AND " &
BuildCriteria("BookingNoticeBookingNo",
dbText, varInputBookingNumber)
End If

varInputBookingNumber will *never*be Null, because InputBox returns a
string. If nothing is entered in the input box, than varInputBookingNumber
will have a value of "" (a zero-length string), which is *not* the same as
Null. Therefore, your code will call BuildCriteria with "" as the third
argument, which is invalid.

Change your code like this:

Dim strInputBookingNumber As String

strInputBookingNumber = InputBox(strMsgBookingNumber)

If Len(Trim(strInputBookingNumber)) > 0 Then
strFilter = strFilter & " AND " & _
BuildCriteria( _
"BookingNoticeBookingNo", _
dbText, _
strInputBookingNumber)
End If

Make the equivalent changes for the other filter fields.

 

[gchichester via AccessMonster.com]

Dirk,
After applying your code change, my search works as intended.
Thank you

After adding another search option in my code I started receiving
a "Illegal function call", I discovered that my strInput boxes should
be Variant data type

No, they shouldn't. Or, rather, they don't need to be, since the value
returned by InputBox is a string, and the Expression argument of
BuildCriteria is a string.

so I made the change but I'm still receiving the
same error when any one of the search criteria is null/blank.

[quoted text clipped - 8 lines]

dbText, varInputBookingNumber)
End If

varInputBookingNumber will *never*be Null, because InputBox returns a
string. If nothing is entered in the input box, than varInputBookingNumber
will have a value of "" (a zero-length string), which is *not* the same as
Null. Therefore, your code will call BuildCriteria with "" as the third
argument, which is invalid.

Change your code like this:

Dim strInputBookingNumber As String

strInputBookingNumber = InputBox(strMsgBookingNumber)

If Len(Trim(strInputBookingNumber)) > 0 Then
strFilter = strFilter & " AND " & _
BuildCriteria( _
"BookingNoticeBookingNo", _
dbText, _
strInputBookingNumber)
End If

Make the equivalent changes for the other filter fields.