Like operator in VBA

[Guest]

I am trying to get the Like operator to work in a situation where a
street address is entered. I would like someone who enters 44th to be able
to pull up all records with 44th in the string such as 44th Street, 44th ST
SW, West 44th Avenue, ect. The code I have now is designed to pull up exact
matches. I have tried changing it but cant seem to get it to work. Here is
a portion of the working code.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TblImproved"

If Not IsNull(Me![FilterStreet]) Then
stLinkCriteria = "[Street]=" & "'" & Me![FilterStreet] & "'"

End If

How would I change this? Any help would be appreciated. Thanks.

 

[Guest]

I spoke too soon, this seemed to work.

stLinkCriteria = "[TblComparable]![Street] Like ""*"" &
[Forms]![TblAddEditImproved]![FilterStreet] & ""*"""

 

[Marshall Barton]

I spoke too soon, this seemed to work.

stLinkCriteria = "[TblComparable]![Street] Like ""*"" &
[Forms]![TblAddEditImproved]![FilterStreet] & ""*"""

The use of ! in [TblComparable]![Street] baffles me, I don't
see what that syntax could be referring to. Other than
that, it might work in this case, but it is more general to
perform the concatenation in the assignment statement
instead of deferring it to the filtering mechanism. Try
this:

stLinkCriteria = "Street Like ""*" &
Forms!TblAddEditImproved!FilterStreet & "*"""

 

[Guest]

That doesn't seem to work, I get a compiling error. Heres what I have with
the changes you suggested. Am I missing something?

Private Sub Command84_Click()
On Error GoTo Err_Command84_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TblImproved"

If Not IsNull(Me![FilterStreet]) Then
stLinkCriteria = "Street Like ""*" &
Forms!TblAddEditImproved!FilterStreet & "*"""


End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command84_Click:
Exit Sub

Err_Command84_Click:

I also have another problem even when it was working. I need to have
multiple IsNot Null(____) Then Statements. I need them to add on to each
other, not cancel out. I put something like this.

If Not IsNull(Me![FilterStreet]) Then
stLinkCriteria = "Street Like ""*" &
Forms!TblAddEditImproved!FilterStreet & "*"""

End If

If Not IsNull(Me![FilterTownship]) Then
stLinkCriteria = "Township Like ""*" &
Forms!TblAddEditImproved!FilterTownship & "*"""

End If

If the township was not null it would ignore the Street and pull up
everything in the township regardless of what street it was on, effectively
canceling out the first.

 

[Marshall Barton]

That doesn't seem to work, I get a compiling error. Heres what I have with
the changes you suggested. Am I missing something?

Private Sub Command84_Click()
On Error GoTo Err_Command84_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TblImproved"

If Not IsNull(Me![FilterStreet]) Then
stLinkCriteria = "Street Like ""*" &
Forms!TblAddEditImproved!FilterStreet & "*"""

That's supposed to be all on one line. If the line is too
long, you can use line continuation to break it over
multiple lines:

stLinkCriteria = "Street Like ""*" & _
Forms!TblAddEditImproved!FilterStreet & "*"""

I also have another problem even when it was working. I need to have
multiple IsNot Null(____) Then Statements. I need them to add on to each
other, not cancel out. I put something like this.

If Not IsNull(Me![FilterStreet]) Then
stLinkCriteria = "Street Like ""*" &
Forms!TblAddEditImproved!FilterStreet & "*"""

End If

If Not IsNull(Me![FilterTownship]) Then
stLinkCriteria = "Township Like ""*" &
Forms!TblAddEditImproved!FilterTownship & "*"""

End If

If the township was not null it would ignore the Street and pull up
everything in the township regardless of what street it was on, effectively
canceling out the first.

Right, you need to accumulate the non-Null conditions.

If Not IsNull(Me![FilterStreet]) Then
stLinkCriteria = stLinkCriteria & _
" AND Street Like ""*" & _
Forms!TblAddEditImproved!FilterStreet & "*"""
End If

If Not IsNull(Me![FilterTownship]) Then
stLinkCriteria = stLinkCriteria & _
" AND Township Like ""*" & _
Forms!TblAddEditImproved!FilterTownship & "*"""
End If

stLinkCriteria = Mid(stLinkCriteria, 6)

 

[Guest]

Thanks for all the help, I finally got it to work correctly. Now I have to
add the other fields. Sorry it took so long. Heres the end product.

Private Sub Command84_Click()
On Error GoTo Err_Command84_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TblImproved"

If Not IsNull(Me![FilterStreet]) Then
stLinkCriteria = stLinkCriteria & _
" AND TblComparable!Street Like ""*" & _
Forms!TblAddEditImproved!FilterStreet & "*"""

End If

If Not IsNull(Me![FilterAuthority]) Then
stLinkCriteria = stLinkCriteria & _
" AND TblComparable!TaxAuthority Like ""*" & _
Forms!TblAddEditImproved!FilterAuthority & "*"""

End If

stLinkCriteria = Mid(stLinkCriteria, 6)
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command84_Click:
Exit Sub

Err_Command84_Click:
MsgBox Err.Description
Resume Exit_Command84_Click

End Sub