use more than one criteria in a vba query

[megatronixs]

Hi all,

I have the below code that I use in a form to do a search on more than one field. I need to find one account nr that could be in various fields (up to 10 fields)
How can the below be adjust to look in account_no1 up to account_no10 ?

Private Sub btn_search_Click()
    Dim sSQL As String
    On Error GoTo errr
        sSQL = "SELECT * FROM qry_search_accounts_2 " & BuildFilter & " ORDER BY qry_search_accounts_2.ccr_code"
    
        Me.frm_search_accounts_2_sub.Form.RecordSource = sSQL
    
        Me.frm_search_accounts_2_sub.Form.RecordSource = Me.frm_search_accounts_2_sub.Form.RecordSource
    
Exit Sub
errr:
MsgBox Err.Description

End Sub
 

Private Function BuildFilter() As Variant
    
Dim varWhere As Variant
Dim Itm As Variant
Dim Criteria As String
Dim CriteriaPrority As String
Dim CriteriaStatus As String
Dim tmp As String
Dim ShortDate As String
tmp = """"
star = "*"
datos = "#"
'Const conJetDate = "\#mm\/dd\/yy\#"
'ShortDate = "#dd/mm/yy#"
varWhere = Null
Criteria = ""
CriteriaPrority = ""
If Me.search_crr_code_text <> "" Then
    varWhere = varWhere & "([account_no1]) OR ([account_no2]) OR ([account_no3]) like " & tmp & Me.search_crr_code_text & tmp & " AND "
End If
 
If IsNull(varWhere) Then
varWhere = ""
Else
    varWhere = "WHERE " & varWhere
    If Right(varWhere, 5) = " AND " Then
        varWhere = Left(varWhere, Len(varWhere) - 5)
    ElseIf Right(varWhere, 4) = " Or " Then
        varWhere = Left(varWhere, Len(varWhere) - 4)
    End If
End If
    BuildFilter = varWhere
End Function

Greetings.

 

[Minty]

You need to store your data properly - having 10 fields call acctNo1 - 10 means you have not got a normalised structure.

To be honest with 600 + posts you should know that isn't correct?

 

[megatronixs]

Hi Minty,

The names come from excel sheet and they are called account_no1, account_no2, account_no3 and so on. I need to do the search on those fields.
I simply don't get to choose what I want to work with.

Sorry.

Greetings.

 

[Minty]

Actually you do, you can move those records into a normalised data structure then work on them.
Just because it's stored that way externally doesn't mean you need to work with it that way. Import the data into a staging table, manipulate it to the correct format then store it properly.
If you do that your search becomes very straight forward.

If not you will have to build a nasty query in the query designer, and adapt your code to match the SQL it generates.

 

[megatronixs]

Hi,

I think I will give it a try like this tomorrow:

Private Sub btn_search_Click()
    Dim sSQL As String
    On Error GoTo errr
        sSQL = "SELECT * FROM qry_search_accounts_2 " & BuildFilter & " ORDER BY qry_search_accounts_2.ccr_code"
    
        Me.frm_search_accounts_2_sub.Form.RecordSource = sSQL
    
        Me.frm_search_accounts_2_sub.Form.RecordSource = Me.frm_search_accounts_2_sub.Form.RecordSource
    
Exit Sub
errr:
MsgBox Err.Description

End Sub
 

Private Function BuildFilter() As Variant
    
Dim varWhere As Variant
Dim Itm As Variant
Dim Criteria As String
Dim CriteriaPrority As String
Dim CriteriaStatus As String
Dim tmp As String
Dim ShortDate As String
tmp = """"
star = "*"
datos = "#"
'Const conJetDate = "\#mm\/dd\/yy\#"
'ShortDate = "#dd/mm/yy#"
varWhere = Null
Criteria = ""
CriteriaPrority = ""

If Me.search_crr_code_text <> "" Then
    varWhere = varWhere & "[account_no1] like " & tmp & Me.search_crr_code_text & tmp & " OR "
End If

If Me.search_crr_code_text <> "" Then
    varWhere = varWhere & "[account_no2] like " & tmp & Me.search_crr_code_text & tmp & " OR "
End If

If Me.search_crr_code_text <> "" Then
    varWhere = varWhere & "[account_no3] like " & tmp & Me.search_crr_code_text & tmp & " OR "
End If

 
If IsNull(varWhere) Then
varWhere = ""
Else
    varWhere = "WHERE " & varWhere
    If Right(varWhere, 5) = " AND " Then
        varWhere = Left(varWhere, Len(varWhere) - 5)
    ElseIf Right(varWhere, 4) = " Or " Then
        varWhere = Left(varWhere, Len(varWhere) - 4)
    End If
End If
    BuildFilter = varWhere
End Function

Maybe it will work out, if not, then I will check out the staging table.

Greetings.

 

[megatronixs]

Hi Minty,

It is working like that very nice. I will now create an import button to make sure they get the data into the tables easy.

Greetings.