Avatar of bcbuchanan
bcbuchanan
 asked on

Using a for next loop to assemble a search string from split array

Hi all -- I'm new (back) to EE and need some help. I have a textbox on a form (TxtAttribute1) and I want to pull the string out of it and then do a split based on ;. That part seems to be fine. However, I have a search string (strWhere) to which I want to add each member of the array, along with the appropriate filtering language for each individual member. There are other boxes that add similar filtering language to strWhere (date ranges, attributes, etc) but which aren't lited here. The code below doesn't cause any errors but it only returns the first member of the array (nothing else). I've tried everything I can think of with the loop and am at wit's end. so this is max points. I'm sure it's something small, so help me out, y'all....!

    Dim strWhere As String
    strWhere = "1=1"

    Dim i As Integer
    Dim sparts() As String


    If Nz(Me.TxtAttribute1) <> "" Then

        sparts = Split(Me.TxtAttribute1, ";")
        For i = LBound(sparts) To UBound(sparts)

                        strWhere = strWhere & " AND " & "BaseDataTableItemA.TxtAttribute1 Like '*" & sparts(i) & "*'"

            Exit For
            Next i
       
    End If

Microsoft AccessVB ScriptProgramming

Avatar of undefined
Last Comment
bcbuchanan

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
bcbuchanan

ASKER
It pulled the correct values from the array (huge) but unfortunately I need it formatted like the string in the attached image. I think we're really close -- !


output.png
Rey Obrero (Capricorn1)

<It pulled the correct values from the array (huge) but unfortunately I need it formatted like the string in the attached image> Why?

what are the values in

BaseDataTableItemA.TxtAttribute1?

and the values from Me.TxtAttribute1?
bcbuchanan

ASKER
strWhere is used to filter records in a subform that only displays after the search button is clicked. The subform's recordsource is BaseDataTableItemA, so whatever string criteria you enter in Me.TxtAttribute1 go to filter the records in the same named column in BaseDataTableItemA. I tried the code you provided and while it did get the array right, the filter didn't know what to do with the ( ) or lack of a second string.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Rey Obrero (Capricorn1)

better upload a copy of the db
bcbuchanan

ASKER
OK - here it is.
ComplaintsTest.mdb
Rey Obrero (Capricorn1)

bcbuchanan,
do you know that the table that you have is not normalized?

what is the purpose of this table?

when you have repeating field, like what you have in your table, you need to break them out into separate tables..

have a read on this link

Description of the database normalization basics
http://support.microsoft.com/?kbid=283878

see also this demo about creating a third table to break a many to many relationship between two tables

http://office.microsoft.com/en-us/access-help/demo-set-the-relationship-between-two-tables-HA010254901.aspx?pid=CH100739911033

there are also lots of information on that link regarding table normalization


Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
bcbuchanan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
bcbuchanan

ASKER
The solution is considered partial b/c capricorn1 provided the basis for the solution but with some different syntax. I made a couple of modifications to the syntax and the solution worked.