Solved

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

Posted on 2011-03-04
9
327 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:bcbuchanan
  • 5
  • 4
9 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 35039075
try this


    Dim strWhere As String
    strWhere = "1=1"

    Dim i As Integer
    Dim sparts() As String
    dim strLst as string

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

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

        Next i
        strLst=mid(strLst,2)

        strWhere = strWhere & " AND " & "BaseDataTableItemA.TxtAttribute1 in (" & strLst & ")"

    End If
0
 

Author Comment

by:bcbuchanan
ID: 35039732
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35039901
<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?
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:bcbuchanan
ID: 35040177
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35040192
better upload a copy of the db
0
 

Author Comment

by:bcbuchanan
ID: 35040315
OK - here it is.
ComplaintsTest.mdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35040742
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


0
 

Assisted Solution

by:bcbuchanan
bcbuchanan earned 0 total points
ID: 35041530
I was able to go back into your first comment and reformat the filtering so it looks like this. Still needs to be optimized but it works.

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

        sparts = Split(Me.TxtAttribute1, ";")
        For i = LBound(sparts) To UBound(sparts)
             strLst = strLst & " AND " & "BaseDataTableItemA.TxtAttribute1 Like '*" & sparts(i) & "*'"

        Next i
        strLst = Mid(strLst, 2)

        strWhere = strWhere & strLst

    End If

BTW, pls don't assume that the DB isn't normalized -- this was just a test DB to figure out this filtering string issue with multiple fields on one form to search through one table's records. The actual DB I'll be using this with has four or five base tables and about 3 MTM tables.   But, you gave me the direction above to solve the rest of the problem on my own, so I'll be happy to give you the points.
0
 

Author Closing Comment

by:bcbuchanan
ID: 35081183
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.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question