StrWhere error runtime 2448

I am trying to have a specific form to be used as a quick search but i am
having a difficult time & I just give up. Anyone pls help me get this form
going. Thx.

My Formula is as followed on the event:
Private Sub Ctl_Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Primary Code
If Nz(Me.Primary_Code) "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "HPG Members Overview.Primary Code =
" & Me.Primary_Code & ""
End If


' If Facility
If Nz(Me.Facility) "" Then
'Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "HPG Members Overview.Facility Like
" * " & Me.Facility & " * ""
End If


' If City
If Nz(Me.City) "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "HPG Members Overview.City = """ &
Me.City & """"
End If


' If State
If Nz(Me.State) "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "HPG Members Overview.State = """ &
Me.State & """"
End If


' If MM Name
If Nz(Me.MM_Name) "" Then
'Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "HPG Members Overview.MM Name Like "
* " & Me.MM_Name & " * ""
End If


' If Agent Contact Name
If Nz(Me.Agent_Contact_Name) "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "HPG Members Overview.Agent Contact
Name = '" & Me.Agent_Contact_Name & "'"
End If

' If Date of Contact
If IsDate(Me.Date_of_Contact) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "HPG Members Overview.Date of
Contact = " & GetDateFilter(Me.Date_of_Contact)
ElseIf Nz(Me.Date_of_Contact) "" Then
strError = cInvalidDateError
End If

If strError "" Then
MsgBox strError
Else
'DoCmd.OpenForm ".frmBrowseMembers", acFormDS, , strWhere,
acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If

(The lines below are highlighted yellow)
Me.Browse_Members.Form.Filter = strWhere
Me.Browse_Members.Form.FilterOn = True

End If
End Sub

Since your table/query name has spaces in it, you need to wrap it in
brackets. Replace:

HPG Members Overview.

with:

[HPG Members Overview].

You will also need to do this with your fields:
[MM Name], [Agent Contact Name], [Date of Contact]

This is one of the reasons I NEVER include spaces in the name of tables or
fields. If I feel I need separation for readability, I separate words with
an underscore (_).

Also, your Openform line has ".frmBrowseMembers", do you mean
"frmBrowseMembers"? It is also remarked out (but I guess you probably
already knew that!

Let me know whether these changes resolve your problem. If not, copy your
corrected code and post it back here for further analysis.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Renee" wrote:

I am trying to have a specific form to be used as a quick search but i am
having a difficult time & I just give up. Anyone pls help me get this form
going. Thx.

My Formula is as followed on the event:
Private Sub Ctl_Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Primary Code
If Nz(Me.Primary_Code) "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "HPG Members Overview.Primary Code =
" & Me.Primary_Code & ""
End If


' If Facility
If Nz(Me.Facility) "" Then
'Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "HPG Members Overview.Facility Like
" * " & Me.Facility & " * ""
End If


' If City
If Nz(Me.City) "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "HPG Members Overview.City = """ &
Me.City & """"
End If


' If State
If Nz(Me.State) "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "HPG Members Overview.State = """ &
Me.State & """"
End If


' If MM Name
If Nz(Me.MM_Name) "" Then
'Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "HPG Members Overview.MM Name Like "
* " & Me.MM_Name & " * ""
End If


' If Agent Contact Name
If Nz(Me.Agent_Contact_Name) "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "HPG Members Overview.Agent Contact
Name = '" & Me.Agent_Contact_Name & "'"
End If

' If Date of Contact
If IsDate(Me.Date_of_Contact) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "HPG Members Overview.Date of
Contact = " & GetDateFilter(Me.Date_of_Contact)
ElseIf Nz(Me.Date_of_Contact) "" Then
strError = cInvalidDateError
End If

If strError "" Then
MsgBox strError
Else
'DoCmd.OpenForm ".frmBrowseMembers", acFormDS, , strWhere,
acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If

(The lines below are highlighted yellow)
Me.Browse_Members.Form.Filter = strWhere
Me.Browse_Members.Form.FilterOn = True

End If
End Sub

Renee,

In addition to Dale's excellent observations, here are a few more
problems in your code...

Consider this example:

' If Facility
If Nz(Me.Facility) "" Then
'Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "HPG Members Overview.Facility
Like " * " & Me.Facility & " * ""
End If

1. Where you are using a * wildcard, it should not also have spaces
around it, otherwise the search criteria will also be looking for spaces
in the data.

2. There is ambiguous usage of the quote marks.

3. Your use of the Nz() function is not correct. If you want it to
return a zero-length string, you need to specify the "value if null"
argument.

Thus, I think you should try it like this:
' If Facility
If Nz(Me.Facility, "") "" Then
'Add it to the predicate - match on leading characters
strWhere = strWhere & " AND [HPG Members Overview].[Facility]
Like '*" & Me.Facility & "*'"
End If

Mind you, your comment "match on leading characters" does not seem to be
what is being done here... this is matching on the entry in the Facilty
control being found *anywhere* within the data in the Facility field.

In any case, probably preferable to the Nz() function in this context
would be:
If IsNull(Me.Facility) Then
Other alternatives would be:
If Me.Facility & "" "" Then
If Len(Me.Facility & "") Then

There are similar considerations in all sections of your code.

I am not aware of the GetDateFilter function you are using, but if it
returns a date, I would also suspect that you may need to enclose that
criteria in # delimiters.

--
Steve Schapel, Microsoft Access MVP


Dale Fye wrote:
Since your table/query name has spaces in it, you need to wrap it in
brackets. Replace:

HPG Members Overview.

with:

[HPG Members Overview].

You will also need to do this with your fields:
[MM Name], [Agent Contact Name], [Date of Contact]

This is one of the reasons I NEVER include spaces in the name of tables or
fields. If I feel I need separation for readability, I separate words with
an underscore (_).

Also, your Openform line has ".frmBrowseMembers", do you mean
"frmBrowseMembers"? It is also remarked out (but I guess you probably
already knew that!

Let me know whether these changes resolve your problem. If not, copy your
corrected code and post it back here for further analysis.

Steve,

Good catches.

I thought about the NZ() function because I thought I remembered it
returning a zero as the default, not an empty string. But when I tested it
in 2007, NZ( ) without the second argument returned a NullString.

?NZ(NULL) = vbNullString
True
?NZ(Null) = ""
True
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Steve Schapel" wrote:

Renee,

In addition to Dale's excellent observations, here are a few more
problems in your code...

Consider this example:

' If Facility
If Nz(Me.Facility) "" Then
'Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "HPG Members Overview.Facility
Like " * " & Me.Facility & " * ""
End If

1. Where you are using a * wildcard, it should not also have spaces
around it, otherwise the search criteria will also be looking for spaces
in the data.

2. There is ambiguous usage of the quote marks.

3. Your use of the Nz() function is not correct. If you want it to
return a zero-length string, you need to specify the "value if null"
argument.

Thus, I think you should try it like this:
' If Facility
If Nz(Me.Facility, "") "" Then
'Add it to the predicate - match on leading characters
strWhere = strWhere & " AND [HPG Members Overview].[Facility]
Like '*" & Me.Facility & "*'"
End If

Mind you, your comment "match on leading characters" does not seem to be
what is being done here... this is matching on the entry in the Facilty
control being found *anywhere* within the data in the Facility field.

In any case, probably preferable to the Nz() function in this context
would be:
If IsNull(Me.Facility) Then
Other alternatives would be:
If Me.Facility & "" "" Then
If Len(Me.Facility & "") Then

There are similar considerations in all sections of your code.

I am not aware of the GetDateFilter function you are using, but if it
returns a date, I would also suspect that you may need to enclose that
criteria in # delimiters.

--
Steve Schapel, Microsoft Access MVP


Dale Fye wrote:
Since your table/query name has spaces in it, you need to wrap it in
brackets. Replace:

HPG Members Overview.

with:

[HPG Members Overview].

You will also need to do this with your fields:
[MM Name], [Agent Contact Name], [Date of Contact]

This is one of the reasons I NEVER include spaces in the name of tables or
fields. If I feel I need separation for readability, I separate words with
an underscore (_).

Also, your Openform line has ".frmBrowseMembers", do you mean
"frmBrowseMembers"? It is also remarked out (but I guess you probably
already knew that!

Let me know whether these changes resolve your problem. If not, copy your
corrected code and post it back here for further analysis.

Interesting, Dale. Thanks, I didn't know that.

I think you'll also find:
? Nz(Null) = 0
True

So maybe Renee's original usage of Nz would work after all.
Nevertheless, in my opinion it's too loosey-goosey without the
ValueIfNull specified, so my recommendation stands.

--
Steve Schapel, Microsoft Access MVP

Dale Fye wrote:

?NZ(NULL) = vbNullString
True
?NZ(Null) = ""
True

That is interesting, because if you just do:

?":" & NZ(Null) & ":"

You get:

::

I tend to always supply the Optional argument. Just a habit I got into.

Dale

"Steve Schapel" wrote in message
...
Interesting, Dale. Thanks, I didn't know that.

I think you'll also find:
? Nz(Null) = 0
True

So maybe Renee's original usage of Nz would work after all. Nevertheless,
in my opinion it's too loosey-goosey without the ValueIfNull specified, so
my recommendation stands.

--
Steve Schapel, Microsoft Access MVP

Dale Fye wrote:

?NZ(NULL) = vbNullString
True
?NZ(Null) = ""
True