We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
10 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Using a for next loop to assemble a search string from split array
Posted on 2011-03-04
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.TxtAtt
Exit For
Next i
End If
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.TxtAtt
Exit For
Next i
End If
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
4
9 Comments
Active today
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.TxtAtt
End If
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.TxtAtt
End If
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
output.png
Active today
<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.TxtAttr
and the values from Me.TxtAttribute1?
what are the values in
BaseDataTableItemA.TxtAttr
and the values from Me.TxtAttribute1?
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.
OK - here it is.
ComplaintsTest.mdb
ComplaintsTest.mdb
Active today
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
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
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.TxtAtt
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.
If Nz(Me.TxtAttribute1) <> "" Then
sparts = Split(Me.TxtAttribute1, ";")
For i = LBound(sparts) To UBound(sparts)
strLst = strLst & " AND " & "BaseDataTableItemA.TxtAtt
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.
Featured Post
The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
Suggested Courses
Course of the Month10 days, 6 hours left to enroll
764 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.