Using strwhere and Like to filter form

[Ceebaby via AccessMonster.com]

Hi Folks
I have a project form where I have a button that when clicked it it opens
another form of property addresses filtered to selections made in 3 fields
called projectArea1, projectArea2 and projectArea3 which are a selection of
different roads. Each project could have 1 road involved or up to 3 roads.

The form is based on a query and I am trying to get the criteria field of the
Road field on the query to accept the strwhere clause below.

I have been trying to use the strwhere and LIKE operator to filter the form
to the roads inputted on the projectArea text fields.

Dim CaseFrm As String
Dim strWhere As String

CaseFrm = "ProjectCaseMgtF"

If Not IsNull(Me!ProjectArea1) Then
strWhere = "[Road]like Me!ProjectArea1" & " Or """
End If

If Not IsNull(Me!ProjectArea2) Then
strWhere = strWhere & "[Road]like Me!ProjectArea2" & " Or """
End If

If Not IsNull(Me!ProjectArea3) Then
strWhere = strWhere & "[Road] like Me!ProjectArea3" & " Or """
End If

If Right(strWhere, 4) = " Or" Then
strWhere = Trim(Left(strWhere, Len(strWhere) - 4))
Else
strWhere = Trim(strWhere)
End If

DoCmd.OpenForm CaseFrm, acNormal, , strWhere

I get either a syntax error message, or the form doesnt filter to the roads
indicated in each of the projectArea text fields.

I have seen examples of mulitple criteria on different text fields, but not
on 1 field.

Basically I am trying to achieve the following eg "Bowles Road or Hendon Road
or Chaplin avenue" which would be in the criteria field of the query if I
made it a static query.Is this possible? can it not be achieved from my code
above.
Any help would be appreciated.

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com

 

[Douglas J. Steele]

The reference to the control on the field needs to go outside of the quotes.
As well, there's little point in using Like unless you're also including
wild cards.

If Not IsNull(Me!ProjectArea1) Then
strWhere = "[Road] like ""*" & Me!ProjectArea1 & "*"" Or "
End If

If Not IsNull(Me!ProjectArea2) Then
strWhere = strWhere & "[Road] like ""*" & Me!ProjectArea2 & "*"" Or
"
End If

If Not IsNull(Me!ProjectArea3) Then
strWhere = strWhere & "[Road] like ""*" & Me!ProjectArea3" & "*"" Or "
End If

 

[Ceebaby via AccessMonster.com]

Hello Douglas

Thank you for your kind response. I have tried the amended code and I am
still getting a syntax error

If Not IsNull(Me!ProjectArea1) Then
strWhere = "[Road] like ""*" & Me!ProjectArea1 & "*"" Or "
End If

If Not IsNull(Me!ProjectArea2) Then
strWhere = strWhere & "[Road] like ""*" & Me!ProjectArea2 & "*"" Or "
End If

If Not IsNull(Me!ProjectArea3) Then
strWhere = strWhere & "[Road] like ""*" & Me!ProjectArea3 & "*"" Or "
End If

If Right(strWhere, 4) = " Or" Then
strWhere = Trim(Left(strWhere, Len(strWhere) - 4))
Else
strWhere = Trim(strWhere)
End If

DoCmd.OpenForm CaseFrm, acNormal, , strWhere


Have I missed something?
Cheers

The reference to the control on the field needs to go outside of the quotes.
As well, there's little point in using Like unless you're also including
wild cards.

If Not IsNull(Me!ProjectArea1) Then
strWhere = "[Road] like ""*" & Me!ProjectArea1 & "*"" Or "
End If

If Not IsNull(Me!ProjectArea2) Then
strWhere = strWhere & "[Road] like ""*" & Me!ProjectArea2 & "*"" Or
"
End If

If Not IsNull(Me!ProjectArea3) Then
strWhere = strWhere & "[Road] like ""*" & Me!ProjectArea3" & "*"" Or "
End If

Hi Folks
I have a project form where I have a button that when clicked it it opens

[quoted text clipped - 51 lines]

above.
Any help would be appreciated.

 

[Ceebaby via AccessMonster.com]

Hi Douglas

I've just found out what was wrong. I needed an extra 2 sets of quotes on the
last strwhere and changed my trim code.

Here's the cleaned up code that works in case someone else needs it :

Dim CaseFrm As String
Dim strWhere As String

CaseFrm = "ProjectCaseMgtF"

If Not IsNull(Me!ProjectArea1) Then
strWhere = "[Road] like ""*" & Me!ProjectArea1 & "*"" Or "
End If

If Not IsNull(Me!ProjectArea2) Then
strWhere = strWhere & "[Road] like ""*" & Me!ProjectArea2 & "*"" Or "
End If

If Not IsNull(Me!ProjectArea3) Then
strWhere = strWhere & "[Road] like ""*" & Me!ProjectArea3 & "*"" Or """
End If

'remove the trailing OR

strWhere = Left(strWhere, Len(strWhere) - 4)

DoCmd.OpenForm CaseFrm, acNormal, , strWhere


Thanks for your help again. I dont know what I would do without the help of
this forum.
Cheers
Ceebaby

Hello Douglas

Thank you for your kind response. I have tried the amended code and I am
still getting a syntax error

If Not IsNull(Me!ProjectArea1) Then
strWhere = "[Road] like ""*" & Me!ProjectArea1 & "*"" Or "
End If

If Not IsNull(Me!ProjectArea2) Then
strWhere = strWhere & "[Road] like ""*" & Me!ProjectArea2 & "*"" Or "
End If

If Not IsNull(Me!ProjectArea3) Then
strWhere = strWhere & "[Road] like ""*" & Me!ProjectArea3 & "*"" Or "
End If

If Right(strWhere, 4) = " Or" Then
strWhere = Trim(Left(strWhere, Len(strWhere) - 4))
Else
strWhere = Trim(strWhere)
End If

DoCmd.OpenForm CaseFrm, acNormal, , strWhere

Have I missed something?
Cheers

The reference to the control on the field needs to go outside of the quotes.
As well, there's little point in using Like unless you're also including

[quoted text clipped - 18 lines]

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com

 

[Douglas J. Steele]

No, you don't need the extra two double quotes on the last strwhere.

However, your original code was checking

If Right(strWhere, 4) = " Or" Then

In other words, you were comparing the last 4 characters of strWhere to a 3
character value, hence you weren't trimming the redundant Or off the end of
the string.

With the additional quotes you added, you're actually ending strWhere with

space, letter O, letter R, space, double-quote.

It works because you're explicity trimming the last 4 characters off. Note
that means you're leaving a space at the end, which fortunately doesn't
matter.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas

I've just found out what was wrong. I needed an extra 2 sets of quotes on
the
last strwhere and changed my trim code.

Here's the cleaned up code that works in case someone else needs it :

Dim CaseFrm As String
Dim strWhere As String

CaseFrm = "ProjectCaseMgtF"

If Not IsNull(Me!ProjectArea1) Then
strWhere = "[Road] like ""*" & Me!ProjectArea1 & "*"" Or "
End If

If Not IsNull(Me!ProjectArea2) Then
strWhere = strWhere & "[Road] like ""*" & Me!ProjectArea2 & "*"" Or
"
End If

If Not IsNull(Me!ProjectArea3) Then
strWhere = strWhere & "[Road] like ""*" & Me!ProjectArea3 & "*"" Or
"""
End If

'remove the trailing OR

strWhere = Left(strWhere, Len(strWhere) - 4)

DoCmd.OpenForm CaseFrm, acNormal, , strWhere


Thanks for your help again. I dont know what I would do without the help
of
this forum.
Cheers
Ceebaby

Hello Douglas

Thank you for your kind response. I have tried the amended code and I am
still getting a syntax error

If Not IsNull(Me!ProjectArea1) Then
strWhere = "[Road] like ""*" & Me!ProjectArea1 & "*"" Or "
End If

If Not IsNull(Me!ProjectArea2) Then
strWhere = strWhere & "[Road] like ""*" & Me!ProjectArea2 & "*"" Or
"
End If

If Not IsNull(Me!ProjectArea3) Then
strWhere = strWhere & "[Road] like ""*" & Me!ProjectArea3 & "*"" Or "
End If

If Right(strWhere, 4) = " Or" Then
strWhere = Trim(Left(strWhere, Len(strWhere) - 4))
Else
strWhere = Trim(strWhere)
End If

DoCmd.OpenForm CaseFrm, acNormal, , strWhere

Have I missed something?
Cheers

The reference to the control on the field needs to go outside of the
quotes.
As well, there's little point in using Like unless you're also including

[quoted text clipped - 18 lines]

above.
Any help would be appreciated.

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com

 

[cesima via AccessMonster.com]

Hi Douglas

Thanks for the update and explanation. I cannot always get my head around the
use of quotes. If you have any suggestions where I might read up on this I
would be most grateful.

I have amended my code as you suggested.
Have a good day.

Ceebaby

No, you don't need the extra two double quotes on the last strwhere.

However, your original code was checking

If Right(strWhere, 4) = " Or" Then

In other words, you were comparing the last 4 characters of strWhere to a 3
character value, hence you weren't trimming the redundant Or off the end of
the string.

With the additional quotes you added, you're actually ending strWhere with

space, letter O, letter R, space, double-quote.

It works because you're explicity trimming the last 4 characters off. Note
that means you're leaving a space at the end, which fortunately doesn't
matter.

Hi Douglas

[quoted text clipped - 70 lines]

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com

 

[Ceebaby via AccessMonster.com]

Hi Douglas

Thanks for the update and explanation. I cannot always get my head around the
use of quotes. If you have any suggestions where I might read up on this I
would be most grateful.

I have amended my code as you suggested.
Have a good day.

Ceebaby

No, you don't need the extra two double quotes on the last strwhere.

However, your original code was checking

If Right(strWhere, 4) = " Or" Then

In other words, you were comparing the last 4 characters of strWhere to a 3
character value, hence you weren't trimming the redundant Or off the end of
the string.

With the additional quotes you added, you're actually ending strWhere with

space, letter O, letter R, space, double-quote.

It works because you're explicity trimming the last 4 characters off. Note
that means you're leaving a space at the end, which fortunately doesn't
matter.

Hi Douglas

[quoted text clipped - 70 lines]

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com

 

[Ceebaby via AccessMonster.com]

Hi Douglas

Thanks for the update and explanation. I cannot always get my head around the
use of quotes. If you have any suggestions where I might read up on this I
would be most grateful.

I have amended my code as you suggested.
Have a good day.

Ceebaby

No, you don't need the extra two double quotes on the last strwhere.

However, your original code was checking

If Right(strWhere, 4) = " Or" Then

In other words, you were comparing the last 4 characters of strWhere to a 3
character value, hence you weren't trimming the redundant Or off the end of
the string.

With the additional quotes you added, you're actually ending strWhere with

space, letter O, letter R, space, double-quote.

It works because you're explicity trimming the last 4 characters off. Note
that means you're leaving a space at the end, which fortunately doesn't
matter.

Hi Douglas

[quoted text clipped - 70 lines]

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com

 

[Douglas J. Steele]

See if what Allen Browne has at http://www.allenbrowne.com/casu-17.html
helps. There's also my May, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas

Thanks for the update and explanation. I cannot always get my head around
the
use of quotes. If you have any suggestions where I might read up on this I
would be most grateful.

I have amended my code as you suggested.
Have a good day.

Ceebaby

No, you don't need the extra two double quotes on the last strwhere.

However, your original code was checking

If Right(strWhere, 4) = " Or" Then

In other words, you were comparing the last 4 characters of strWhere to a
3
character value, hence you weren't trimming the redundant Or off the end
of
the string.

With the additional quotes you added, you're actually ending strWhere with

space, letter O, letter R, space, double-quote.

It works because you're explicity trimming the last 4 characters off. Note
that means you're leaving a space at the end, which fortunately doesn't
matter.

Hi Douglas

[quoted text clipped - 70 lines]

above.
Any help would be appreciated.

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com