Help with Coding Search Form

  • Thread starter Thread starter Coleman
  • Start date Start date
C

Coleman

I have a Search form that allows the user to put in criteria and a record set
is returned. I have everything working except...I have a combox that
provides for a list of "Positions". I would like to use this box to return
all records in the table where the fields "PositionA", "PositionB", OR
"PositionC" contain the SAME criteria pulled down from the List. I have the
following :

If Not IsNull(Me.strPosition) Then
'Add the Predicate
strWhere = strWhere & " AND " & "tblQAR.[strPositionA] = " &
Me.strPosition & ""
End If

This works great for a single field, but how do I extend this to cover the
"OR" Position B "OR" Position C ? I am novice at this coding and seek some
coding advice.
 
strWhere = strWhere & " AND (([PositionA] = '" & Me.strPosition & "') OR
([PositionB] = '" & Me.strPosition & "') OR ([PositionC] = '" &
Me.strPosition & "'))"

The goal is to produce an end result like:
strWhere = "Somefield = Somevalue AND (([PositionA] = 'myValue') OR
([PositionB] = 'myValue') OR ([PositionC] = 'myValue'))"
Note the parentheticals around each part of the 3 ORs and then again around
the whole, since you want the AND to treat the combined ORs as a single
entity (at least one of the ORs needs to be true AND so does the first part
to meet the criteria).
----------
As far as data structure goes, PositionA, PositionB, PositionC should
probably all be in a separate table, as individual records, and joined to
tblQAR. They appear to violate data normalization rules as they are now. The
difficulty of retrieving data stored this way is the primary reason why (as
you have discovered) as well as: what happens when someone wants to add
PositionD?.

This, of course, assumes that you have control over the data structure in
the first place.
 
George,
Thank you for the response and suggestion. I agree with the normalization
comment and hope to resolve that, too.
Again thanks for replying to me and the suggestion.

All the best,
Coleman

George Nicholson said:
strWhere = strWhere & " AND (([PositionA] = '" & Me.strPosition & "') OR
([PositionB] = '" & Me.strPosition & "') OR ([PositionC] = '" &
Me.strPosition & "'))"

The goal is to produce an end result like:
strWhere = "Somefield = Somevalue AND (([PositionA] = 'myValue') OR
([PositionB] = 'myValue') OR ([PositionC] = 'myValue'))"
Note the parentheticals around each part of the 3 ORs and then again around
the whole, since you want the AND to treat the combined ORs as a single
entity (at least one of the ORs needs to be true AND so does the first part
to meet the criteria).
----------
As far as data structure goes, PositionA, PositionB, PositionC should
probably all be in a separate table, as individual records, and joined to
tblQAR. They appear to violate data normalization rules as they are now. The
difficulty of retrieving data stored this way is the primary reason why (as
you have discovered) as well as: what happens when someone wants to add
PositionD?.

This, of course, assumes that you have control over the data structure in
the first place.

--
HTH,
George


Coleman said:
I have a Search form that allows the user to put in criteria and a record
set
is returned. I have everything working except...I have a combox that
provides for a list of "Positions". I would like to use this box to
return
all records in the table where the fields "PositionA", "PositionB", OR
"PositionC" contain the SAME criteria pulled down from the List. I have
the
following :

If Not IsNull(Me.strPosition) Then
'Add the Predicate
strWhere = strWhere & " AND " & "tblQAR.[strPositionA] = " &
Me.strPosition & ""
End If

This works great for a single field, but how do I extend this to cover the
"OR" Position B "OR" Position C ? I am novice at this coding and seek
some
coding advice.
Click to expand...
Click to expand...
 
Marsh,
Thanks a bunch for replying and providing a suggestion to resolve my
question. I concur with the normalization comment. I hope to resolve that
too. I am amazed at the energy and willingness of people here to step up and
help.

Regards,
Coleman


Marshall Barton said:
Coleman said:
I have a Search form that allows the user to put in criteria and a record set
is returned. I have everything working except...I have a combox that
provides for a list of "Positions". I would like to use this box to return
all records in the table where the fields "PositionA", "PositionB", OR
"PositionC" contain the SAME criteria pulled down from the List. I have the
following :

If Not IsNull(Me.strPosition) Then
'Add the Predicate
strWhere = strWhere & " AND " & "tblQAR.[strPositionA] = " &
Me.strPosition & ""
End If

This works great for a single field, but how do I extend this to cover the
"OR" Position B "OR" Position C ? I am novice at this coding and seek some
coding advice.
Click to expand...


If the positiuon fields are a numeric type, the you could
use:

strWhere = strWhere _
& " AND (tblQAR.strPositionA = " & Me.strPosition _
& " OR tblQAR.strPositionB = " & Me.strPosition _
& " OR tblQAR.strPositionC = " & Me.strPosition & ")"

On the other hand, you should not have fields like
strPositionA, strPositionB and strPositionC in a table.
That is a clear violation of the rules of relational
database normalization.

Instead you should have a separate table like:
PeoplePositions with one field for the personID and another
for the positionID. This kind of "many to many"
relationship would have made your question moot and save you
from all kinds of other problems.
Click to expand...
 
Back
Top