Modifying IF help

[don]

Hi Group,

I'm using a piece of VBA I got from Allen Brows web site to construct
a search. It works fine but my needs have changed a little and I'm
trying to modify it. I have an address Db with a local area and town
field unfortunatley what the user might consider is the town is the
local area and possibly vice versa. I would therefore like to be able
to search using the same system but be able to check in both local
area and town. The original VBA is below along with a couple of my
attempts. Can I do it like this or is my thinking wrong. At present
my knowledge of VBA as can probably be seen from my attempts is very
limited.



If Not IsNull(Me.txtTown) Then
strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND "
End If

If Not IsNull(Me.txtTown) Then
strWhere = strWhere & "([Locality] = """ & Me.txtTown & """)
AND "
'''''' ElseIf Not IsNull(Me.txtTown) Then
strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND "
End If


Don

 

[don]

Hi Group,

I'm using a piece of VBA I got from Allen Brows web site to construct
a search.  It works fine but my needs have changed a little and I'm
trying to modify it. I have an address Db with a local area and town
field unfortunatley what the user might consider is the town is the
local area and possibly vice versa.  I would therefore like to be able
to search using the same system but be able to check in both local
area and town.  The original VBA is below along with a couple of my
attempts.  Can I do it like this or is my thinking wrong.  At present
my knowledge of VBA as can probably be seen from my attempts is very
limited.

    If Not IsNull(Me.txtTown) Then
        strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND "
    End If

     If Not IsNull(Me.txtTown) Then
        strWhere = strWhere & "([Locality] = """ & Me.txtTown & """)
AND "
''''''        ElseIf Not IsNull(Me.txtTown) Then
        strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND "
    End If

Don

Sorry found answer. Had done Help search for OR and came up with
nothing. JUst found it in a book. Now using

If Not IsNull(Me.txtTown) Then
strWhere = strWhere & "([Locality] = """ & Me.txtTown & """)
OR "

strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND "
End If

And it seems to work

Don

 

[don]

Hi Group,

I'm using a piece of VBA I got from Allen Brows web site to construct
a search.  It works fine but my needs have changed a little and I'm
trying to modify it. I have an address Db with a local area and town
field unfortunatley what the user might consider is the town is the
local area and possibly vice versa.  I would therefore like to be able
to search using the same system but be able to check in both local
area and town.  The original VBA is below along with a couple of my
attempts.  Can I do it like this or is my thinking wrong.  At present
my knowledge of VBA as can probably be seen from my attempts is very
limited.

    If Not IsNull(Me.txtTown) Then
        strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND "
    End If

     If Not IsNull(Me.txtTown) Then
        strWhere = strWhere & "([Locality] = """ & Me.txtTown & """)
AND "
''''''        ElseIf Not IsNull(Me.txtTown) Then
        strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND "
    End If

Don

Sorry found answer.  Had done Help search for OR and came up with
nothing.  JUst found it in a book.  Now using

If Not IsNull(Me.txtTown) Then
        strWhere = strWhere & "([Locality] = """ & Me.txtTown & """)
OR "

        strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND "
    End If

And it seems to work

Don- Hide quoted text -

- Show quoted text -

OK, I should of kept quiet that doesn't work as it return all
addresses within town /locality.

Any help would be appreciated.

Don

 

[BruceM]

Hi Group,

I'm using a piece of VBA I got from Allen Brows web site to construct
a search. It works fine but my needs have changed a little and I'm
trying to modify it. I have an address Db with a local area and town
field unfortunatley what the user might consider is the town is the
local area and possibly vice versa. I would therefore like to be able
to search using the same system but be able to check in both local
area and town. The original VBA is below along with a couple of my
attempts. Can I do it like this or is my thinking wrong. At present
my knowledge of VBA as can probably be seen from my attempts is very
limited.

If Not IsNull(Me.txtTown) Then
strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND "
End If

If Not IsNull(Me.txtTown) Then
strWhere = strWhere & "([Locality] = """ & Me.txtTown & """)
AND "
'''''' ElseIf Not IsNull(Me.txtTown) Then
strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND "
End If

Don

Sorry found answer. Had done Help search for OR and came up with
nothing. JUst found it in a book. Now using

If Not IsNull(Me.txtTown) Then
strWhere = strWhere & "([Locality] = """ & Me.txtTown & """)
OR "

strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND "
End If

And it seems to work

Don- Hide quoted text -

- Show quoted text -

OK, I should of kept quiet that doesn't work as it return all
addresses within town /locality.

Any help would be appreciated.

Don


You are checking for one thing, which is whether txtTown is null, but you
have two possible values for strWhere. Maybe you could cobble them together
with something such as:

If Not IsNull(Me.txtTown) Then
strWhere = strWhere & "([Locality] = """ & Me.txtTown & _
""") OR ([Town] = """ & Me.txtTown & """) AND "
End If

I am not completely sure that will work, but even if it does I think it
would be better if the user has no choice but to enter a valid town name and
locality name (unless a town and locality could have the same name?). If
the Locality is dependent on the town it may be better if the user does not
enter a locality at all, but rather that there is a lookup of locality based
on the town. Some specific examples may help get to the bottom of this.

 

[don]

Hi Group,
I'm using a piece of VBA I got from Allen Brows web site to construct
a search. It works fine but my needs have changed a little and I'm
trying to modify it. I have an address Db with a local area and town
field unfortunatley what the user might consider is the town is the
local area and possibly vice versa. I would therefore like to be able
to search using the same system but be able to check in both local
area and town. The original VBA is below along with a couple of my
attempts. Can I do it like this or is my thinking wrong. At present
my knowledge of VBA as can probably be seen from my attempts is very
limited.
If Not IsNull(Me.txtTown) Then
strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND "
End If
If Not IsNull(Me.txtTown) Then
strWhere = strWhere & "([Locality] = """ & Me.txtTown & """)
AND "
'''''' ElseIf Not IsNull(Me.txtTown) Then
strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND "
End If
Don

Sorry found answer. Had done Help search for OR and came up with
nothing. JUst found it in a book. Now using

If Not IsNull(Me.txtTown) Then
strWhere = strWhere & "([Locality] = """ & Me.txtTown & """)
OR "

strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND "
End If

And it seems to work

Don- Hide quoted text -

- Show quoted text -

OK, I should of kept quiet that doesn't work as it return all
addresses within town /locality.

Any help would be appreciated.

Don

You are checking for one thing, which is whether txtTown is null, but you
have two possible values for strWhere.  Maybe you could cobble them together
with something such as:

If Not IsNull(Me.txtTown) Then
    strWhere = strWhere & "([Locality] = """ & Me.txtTown & _
                    """) OR ([Town] = """ & Me.txtTown & """) AND "
End If

I am not completely sure that will work, but even if it does I think it
would be better if the user has no choice but to enter a valid town name and
locality name (unless a town and locality could have the same name?).  If
the Locality is dependent on the town it may be better if the user does not
enter a locality at all, but rather that there is a lookup of locality based
on the town.  Some specific examples may help get to the bottom of this.- Hide quoted text -

- Show quoted text -

Bruce,

Thank you for your reply. I think I've just got it sorted. FYI I've
got 187 localities and 169 towns with some localities being towns. I
don't know enough about VBA to tell how close you suggestion is to
mine but the one I have is

If Not IsNull(Me.txtTown) Then
strWhere = strWhere & "([Locality] = """ & Me.txtTown & """)
OR "
strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND
"
End If

I think one of my issues might of been spaces around the AND but if
not I think it was down to the OR not being the first option. I'm
using the same thing on another form and were getting odd results till
I changed it so other criteria cam after.

f Not IsNull(Me.txtTown) Then
strWhere = strWhere & "([Locality] = """ & Me.txtTown & """)
OR "
strWhere = strWhere & "([Town] = """ & Me.txtTown & """) AND
"
End If

If Not IsNull(Me.txtFirstname) Then
strWhere = strWhere & "([Firstname] Like ""*" &
Me.txtFirstname & "*"") AND "
End If


etc


As long as it works I'll leave it as is in the hope that users wont
have to worry whether they know the place they are entering is a local
place or town.


Thanks again for your help.


Don