Help w/ Coding Logic

[moonsmile66]

Hi guys/gals,

I have been forced to use Access to build a project database for my group not long ago. I didn't have any Access or programming experiences before so please bare with me if I'm asking silly questions.

Brief background:
Bunch tables to store the data; bunch forms for data entering; a form and query for search. I built the search form following an old example I found from this forum.

Question:
For the search form, the logic of searching by "Process #1" and "Process #2" are not working correct. Coding as below.
I'm trying to search the record which contains "comP1" entry in either [Process #1] or [Process #2], and this criteria has to be combined with other criteria. Same wish for "comP2"
The problem is both searching with "Process #1" and "Process #2" are not doing "AND" with other criteria, need help!

Thanks many!

Private Sub btnSearch_Click()

 ' Update the record source
    Me.frmsubSearch.Form.RecordSource = "SELECT * FROM Query1 " & BuildFilter
    
    ' Requery the subform
    Me.frmsubSearch.Requery

End Sub



Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim intIndex As Integer

    varWhere = Null  ' Main filter
   
    
    ' Check for LIKE State
    If Me.comState > "" Then
        varWhere = varWhere & "[State] LIKE """ & Me.comState & "*"" AND "
    End If
    
    ' Check for LIKE Source Water
    If Me.comSourceWater > "" Then
        varWhere = varWhere & "[SourceWater] LIKE """ & Me.comSourceWater & "*"" AND "
    End If
    
    ' Check for min design flow
    If Me.txtlowflow > "" Then
        varWhere = varWhere & "[Designflow] > " & Me.txtlowflow & " AND "
    End If
    
    ' Check for max design flow
    If Me.txthighflow > "" Then
        varWhere = varWhere & "[Designflow] < " & Me.txthighflow & " AND "
    End If
    
    ' Check for process #1
    If Me.comP1 > "" Then
        varWhere = varWhere & "[Process #1] LIKE """ & Me.comP1 & "*"" OR [Process #2] LIKE """ & Me.comP1 & "*"" AND "
    End If

    ' Check for process #2
    If Me.comP2 > "" Then
        varWhere = varWhere & "[Process #1] LIKE """ & Me.comP2 & "*"" OR [Process #2] LIKE """ & Me.comP2 & "*"" AND "
    End If
       
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
    
End Function

 

[boblarson]

The best fix is to normalize your data so you don't have repeating fields and the processes are RECORDS in a junction table. Then searching becomes a lot easier.

 

[moonsmile66]

Thanks for the quick reply.

Is there anyway to normalize data automatically? My data entries are coming from bunch forms which we built user friendly for people don't have any Access background to entering data.

The best fix is to normalize your data so you don't have repeating fields and the processes are RECORDS in a junction table. Then searching becomes a lot easier.

 

[namliam]

The best fix is to normalize your data so you don't have repeating fields and the processes are RECORDS in a junction table. Then searching becomes a lot easier.

Hail and all Hail Bob, what happens if you have Proces #3? #4 ? #5??

Meanwhile, when stuck for now with this design, you basicaly have to TELL access what to do by using brackets...

varWhere = varWhere & " ( [Process #1] LIKE """ & Me.comP1 & "*"" OR [Process #2] LIKE """ & Me.comP1 & "*"") AND "

While I am here, dont make such rediculous IMHO unreadable long lines of code....

This would be my way of making it a bit more readable....

varWhere = varWhere & " (  [Process #1] LIKE """ & Me.comP1 & "*"""
varWhere = varWhere & " OR [Process #2] LIKE """ & Me.comP1 & "*"") AND "

 

[moonsmile66]

Thanks! and you made me LOL...

Smart thinking, we actually have Process #3, #4...till #20! It was designed by my supervisor a year back, my job is to develop the search function... I sure can't Hail at my boss, but to stuck with the long ridiculous code with this design

 

[namliam]

Thanks! and you made me LOL...

Smart thinking, we actually have Process #3, #4...till #20! It was designed by my supervisor a year back,

Tell your supervisor he is a piece of shyte and he made a crappy design which needs to be redesigned sooner or later.

HAIL in my sence is PRAISE, I fully support Bob's comment of a redesign being required.

 

[moonsmile66]

Tell your supervisor he is a piece of shyte and he made a crappy design which needs to be redesigned sooner or later.

HAIL in my sence is PRAISE, I fully support Bob's comment of a redesign being required.

hehe...thank you. I'll see how much work it involved to redesign and modify the existing data