Need to search 30 columns, how to do that?

M

merika

Registered User.
Local time
Today, 18:41
Joined
Jun 18, 2012
Messages
16
I´m having a search form with mutliple fields which is working great (implemented the search solution of Allen Browne) except for one part.

I need to have one text box that searches 30 (!) columns of the attached query. No matter what I try in VBA, I can´t get it to work properly. I have no trouble having it sarch over 1 column but how to make that one text box search over 30 columns to display the result?

What I have to search over 1 column:

Code: 
    If Not IsNull(Me.txtFiltersite1) Then
        strWhere = strWhere & "([D1_1] Like ""*" & Me.txtFiltersite1 & "*"") AND "
    End If

If I just add to this code with OR it doesn´t seem to do the trick. Any suggestions?
 
Could you post a bit bigger picture of what you say you have tired.
 
You need to use OR, not AND. With AND all fields must match. Probably you want to return a record even if only one field matches the search text. But you didn't really describe the problem either. To say that what you've tried 'doesn't do the trick' is not that informative.
Cheers,
 
The fact that you have 30 columns is not in and of itself something that raises a red flag. But the fact that you are attempting to search ALL 30 for something does. It suggests that your database may not be properly normalized.

So, if that is the case then a search would be much easier to accomplish if you have the correct structure.
 
Hi bob,

Understand where you´re coming from. LEt me expalin a bit more.
I´m creating a database with in it routes for various cruises. I have a table with all the sites they visit which is linked to the various boats. The itineraries are a maximum of 15 days with 2 site visits a day.

The query attached to the search form is a compilation of it all and because of the maximum number of sites and days, ends up to be 30 columns. As my users have no clue when a certain site is being visited but their clients would like to know if a site is included ,they need to be able to search all the columns form one textbox (do I still make sense here?). Hence the 30 columns....

If you know of another way to do it, I´m open for suggestions :)
 
This is the complete code I have now...

Code: 
Private Sub cmdFilter_Click()
    Dim strWhere As String                  
    Dim lngLen As Long                     
    Const conJetDate = "\#mm\/dd\/yyyy\#"   

    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([SailDate] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    End If

    If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
        strWhere = strWhere & "([SailDate] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
    End If
       
 
    If Not IsNull(Me.txtFilterBoat) Then
        strWhere = strWhere & "([Barco] Like ""*" & Me.txtFilterBoat & "*"") AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtFilterClass) Then
        strWhere = strWhere & "([Clase] Like ""*" & Me.txtFilterClass & "*"") AND "
    End If
    
        'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtFilterType) Then
        strWhere = strWhere & "([Tipo] Like ""*" & Me.txtFilterType & "*"") AND "
    End If

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

      If Not IsNull(Me.txtFilterDays1) Then
        strWhere = strWhere & "([Días] >= " & Me.txtFilterDays1 & ") AND "
    End If
    
      If Not IsNull(Me.txtFilterDays2) Then
        strWhere = strWhere & "([Días] < " & Me.txtFilterDays2 & ") AND "
    End If
     
     If Me.vrfFilterDive = -1 Then
        strWhere = strWhere & "([Buseo] = True) AND "
    ElseIf Me.vrfFilterDive = 0 Then
        strWhere = strWhere & "([Buseo] = False) AND "
    End If
[COLOR="Red"]
      If Not IsNull(Me.txtFiltersite1) Then
        strWhere = strWhere & "([D1_1] Like ""*" & Me.txtFiltersite1 & "*"") AND "
    End If[/COLOR]

    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
         'Debug.Print strWhere

        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

Private Sub cmdPrintReport_Click()
Dim stDocName

stDocName = "rptUnionBoat_TF"

DoCmd.OpenReport stDocName, acPreview, , Me.Filter

End Sub

Private Sub cmdReset_Click()
    Dim ctl As Control

    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next

    Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
    Cancel = True
    MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub

For the part in red, I need a way to search not only D1_1 but also D1_2, D2_1 etc up until D15_2
 
merika said:
Hi bob,

Understand where you´re coming from. LEt me expalin a bit more.
I´m creating a database with in it routes for various cruises. I have a table with all the sites they visit which is linked to the various boats. The itineraries are a maximum of 15 days with 2 site visits a day.

The query attached to the search form is a compilation of it all and because of the maximum number of sites and days, ends up to be 30 columns. As my users have no clue when a certain site is being visited but their clients would like to know if a site is included ,they need to be able to search all the columns form one textbox (do I still make sense here?). Hence the 30 columns....

If you know of another way to do it, I´m open for suggestions :)
Click to expand...
1. Yes, your structure is incorrect. You have, what is quite common for new people to access (and even some who've used it for a while). That is, "spreadsheet" thinking.

2. I would suggest reading this article on normalization. Hopefully it woiuld help.

3. Here's a couple of sample models for a cruise line:
http://www.databaseanswers.org/data_models/passengers_on_a_cruise/index.htm

http://www.databaseanswers.org/data_models/cruise_ship_administration/index.htm
 
I´ve actually looked at that cruiseline example before and it gave me a good head start. The problem I ran into however is that I have over 150 different boats whom each visit 2 different sites per day (1 in the morning, 1 in the afternoon and they need to be listed seperately) on different dates and have each about 15 different routes they can take.

I haven´t done anything in Access for over 10 years and coding and DB design never have been amongst my strong points, I admit to it ;)

I´m even surprised with how far I got by having all the boats with all their specific details in my db linked to all their their different routes and the sites that are being visited on those routes and the dates with all exceptions in there as well. This forum has been a great help as I found many examples that I could tweak to suit my needs.

The only problem I just don´t seem to know how to solve is the search over all the sites in the routes. I have also been reading up on normalization and agree that there are some flaws in the design I have now but it is all I seem to be able to do for now as the db needs to go live on Monday. After going live... I´ll definitely will look into some of the issues again to see where I can improve the design (and yes, i´m well aware of the fact that it ain´t a good idea to do that after go live but better late than never....)
 
Last edited:
Sorry, I forgot to include the article:

http://support.microsoft.com/kb/283878

And good luck with this as going back and changing things to fix its structure will be painful for sure. It is necessary if you don't want to keep having to "band aid" fix it all of the time.

For your immediate issue you can use the code below (and change all of the AND to OR as lagbolt said):

Code: 
    Dim intF1 As Integer
    Dim intF2 As Integer
    For intF1 = 1 To 2
        For intF2 = 1 To 15
            If Not IsNull(Me.txtFiltersite1) Then
                strWhere = strWhere & "([D" & intF1 & "_" & intF2 & "] Like ""*" & Me.txtFiltersite1 & "*"") OR "
            End If
        Next
    Next
 
Thanks for sharing the code, Bob.

I will add to what Bob offered, at first glance I see your query as:

Code: 
WHERE Col1
AND Col2
AND Col3
AND (Col4
Or Col5
Or Col6
Or Col7
...
Or Col15)
Where the sumation of all of the 15 OR'ed columns are AND'ed to the rest of the columns.

So yes, if that seems to be what you are after, then adjust Bob's code suggestion to wrap the first/last cols within outer parentheses.
 
You must log in or register to reply here.

Similar threads

H
Solved Search across multiple fields
Replies
8
Views
640
Harry Paraskeva
H
essaytee
[SOLVED] Calculated column not sorting by largest to smallest
Replies
1
Views
210
essaytee
essaytee
E
Emmanuel Katto Dubai : Suggestions Needed for Creating a Search Text Box
Replies
2
Views
238
Edgar_
E
E
I need to be able to type in text box and for the list box to show me all similar text. No VBA )-:
Replies
19
Views
1,384
Pat Hartman
P
H
Solved How to make a form to enter multiple entries in table with same form
2
Replies
38
Views
1,780
hrdpgajjar
H

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom