Creating Listbox into "search form" that generates query - see code - almost there

F

fredp613

Registered User.
Local time
Today, 17:58
Joined
Jul 17, 2009
Messages
17
Creating Listbox into "search form" that generates query - see code - almost there

Hello,
What I would like to do is create a new form which will listboxes that will allow the user to pass their multiple selections into a query. So far I have the following code however this code generates an IN criteria. What I would like for it to do is generate an LIKE AND criteria. See below for details:

Current code (which generates IN criteria) - which generates the following SQL

SELECT *
FROM Master
WHERE Master.ValueTypes IN('Economic - Market Value','Economic -Non-market Value');

VB code used:

Option Compare Database
Option Explicit
Private Sub cmdOK_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!mslbValueTypesQry.ItemsSelected
strCriteria = strCriteria & ",'" & Me!mslbValueTypesQry.ItemData(varItem) & "'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM Master " & _
"WHERE Master.ValueTypes IN(" & strCriteria & ")"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "qryMultiSelect"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

--What I would like to do is generate a LIKE AND criteria instead (i.e: Like "*Aesthetic*" And Like "*Existence*") which generates the following SQL:

SELECT Master.FileID, Master.ValueTypes, Master.Focus, Master.Method, Master.Region, Master.YearID, Master.NumOfRespondents, Master.YearID2, Master.Title, Master.Comment, Master.QuestionAsked, Master.YearID3, Master.Author, Master.Results
FROM Master
WHERE (((Master.ValueTypes) Like "*Aesthetic*" And (Master.ValueTypes) Like "*Existence*"));

I should mention that this query has all the other fields I want to query based on this criteria (i.e Im looking for all the fileID's that relate to Aesthetic & existence)

I'm guessing I just have to change the SQL string in the code, but I've been unsuccessful at doing so.

Any Ideas?

Thank you!!!!
 
Re: Creating Listbox into "search form" that generates query - see code - almost ther

Does this work?:
Code: 
strCriteria = strCriteria & "((Master.ValueTypes) Like '*" _
    & Me!mslbValueTypesQry.ItemData(varItem) & "*') AND "
Then as final modification:
Code: 
' Remove the trailing AND from the string
strCriteria = Left(strCriteria, Len(strCriteria) - 5)
 
Re: Creating Listbox into "search form" that generates query - see code - almost ther

I think i'm close, I'm still getting an error though and I know its because of this line of coding:

' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM Master " & _
"WHERE Master.ValueTypes " & strCriteria & ")"

Here is the full code:

Option Compare Database
Option Explicit
Private Sub cmdOK_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!mslbValueTypesQry.ItemsSelected
strCriteria = strCriteria & "((Master.ValueTypes) Like '*" _
& Me!mslbValueTypesQry.ItemData(varItem) & "*') AND "
strCriteria = Left(strCriteria, Len(strCriteria) - 5)
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM Master " & _
"WHERE Master.ValueTypes " & strCriteria & ")"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "qryMultiSelect"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

Thank you for your help!
 
Re: Creating Listbox into "search form" that generates query - see code - almost ther

the line:
strCriteria = Left(strCriteria, Len(strCriteria) - 5)

comes after the For...Next loop
 
Re: Creating Listbox into "search form" that generates query - see code - almost ther

Done, now the only thing I'm having trouble with is the SQL statement:

' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM Master " & _
"WHERE Master.ValueTypes IN(" & strCriteria & ")"

Because it is now a LIKE AND statement (not an IN statement) how can I modify the above to build the proper sql?

New full code:

Option Compare Database
Option Explicit
Private Sub cmdOK_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!mslbValueTypesQry.ItemsSelected
strCriteria = strCriteria & "((Master.ValueTypes) Like '*" _
& Me!mslbValueTypesQry.ItemData(varItem) & "*') AND "
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Left(strCriteria, Len(strCriteria) - 5)
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM Master " & _
"WHERE Master.ValueTypes IN(" & strCriteria & ")"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "qryMultiSelect"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub
 
Re: Creating Listbox into "search form" that generates query - see code - almost ther

strSQL = "SELECT * FROM Master WHERE " & strCriteria
 
Re: Creating Listbox into "search form" that generates query - see code - almost ther

Thank you! that worked great. Now i'm almost done, I hope you don't mind, I was going to ask you another question. I'm getting other help on this issue but it seems like i'm on the right track with you.

Now that this code works for 1 llisbox on my form, lets say I want to add more listboxess on that same form, how would I modify the code so that the command button would pass the query to reflect multiple selections from each of these listboxes?

Ideally I would like the search form to allow the user to be able to search anything from the following listboxes:

Value Types (already have) , Focus, Method, Region <---user can select multiple Items from each of these listboxes and pass it on to the query (same SQL statement for each of these "LIKE AND").

Lastly, I would also like to add listboxes for Year(from), Year(to), FileID, Date of Publication, Author, Title (these listboxes will pass SQl statement OR)

Thanks again for help!
 
Re: Creating Listbox into "search form" that generates query - see code - almost ther

I seen that as well in my research, but lets say I have multiple Listboxes, how would I modify the cmd button code to pass 1 query on all the selections I've made from each listbox?
 
Re: Creating Listbox into "search form" that generates query - see code - almost ther

just create a "For Each varItem In ... Next varItem" loop for each list box (if it's not too many list boxes)
 
Re: Creating Listbox into "search form" that generates query - see code - almost ther

Thank alot, works great!!
 
Re: Creating Listbox into "search form" that generates query - see code - almost ther

NP - best wishes for your project :)
 
Re: Creating Listbox into "search form" that generates query - see code - almost ther

Hi, I hope you can help me with this again. Everything we spoke about works perfectly, however I still cannot pass the OR criteria using the vb code below. The LIKE AND works perfectly for the data that is stored as a comma-delimited string in my table, however for the data i need to pull that is not comma-delimited I need the vb to pass an OR criteria or an IN criteria see snipet:

For Each varItem In Me!mslbFileIDQry.ItemsSelected
strCriteria = strCriteria & "((Master.FileID) OR'*" _
& Me!mslbFileIDQry.ItemData(varItem) & "*')
Next varItem
For Each varItem In Me!mslbTitleQry.ItemsSelected
strCriteria = strCriteria & "((Master.Title) OR'*" _
& Me!mslbTitleQry.ItemData(varItem) & "*') AND "
Next varItem
------------

Full Code that i am currently using

Option Compare Database
Option Explicit
Private Sub cmdOK_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!mslbValueTypesQry.ItemsSelected
strCriteria = strCriteria & "((Master.ValueTypes) Like '*" _
& Me!mslbValueTypesQry.ItemData(varItem) & "*') AND "
Next varItem
For Each varItem In Me!mslbFocusQry.ItemsSelected
strCriteria = strCriteria & "((Master.Focus) Like '*" _
& Me!mslbFocusQry.ItemData(varItem) & "*') AND "
Next varItem
For Each varItem In Me!mslbMethodQry.ItemsSelected
strCriteria = strCriteria & "((Master.Method) Like '*" _
& Me!mslbMethodQry.ItemData(varItem) & "*') AND "
Next varItem
For Each varItem In Me!mslbRegionQry.ItemsSelected
strCriteria = strCriteria & "((Master.Region) Like '*" _
& Me!mslbRegionQry.ItemData(varItem) & "*') AND "
Next varItem
For Each varItem In Me!mslbFileIDQry.ItemsSelected
strCriteria = strCriteria & "((Master.FileID) LIKE'*" _
& Me!mslbFileIDQry.ItemData(varItem) & "*') AND "
Next varItem
For Each varItem In Me!mslbTitleQry.ItemsSelected
strCriteria = strCriteria & "((Master.Title) Like '*" _
& Me!mslbTitleQry.ItemData(varItem) & "*') AND "
Next varItem
For Each varItem In Me!mslbYear1Qry.ItemsSelected
strCriteria = strCriteria & "((Master.YearID) Like '*" _
& Me!mslbYear1Qry.ItemData(varItem) & "*') AND "
Next varItem
For Each varItem In Me!mslbYear2Qry.ItemsSelected
strCriteria = strCriteria & "((Master.YearID2) Like '*" _
& Me!mslbYear2Qry.ItemData(varItem) & "*') AND "
Next varItem
For Each varItem In Me!mslbYear3Qry.ItemsSelected
strCriteria = strCriteria & "((Master.YearID3) Like '*" _
& Me!mslbYear3Qry.ItemData(varItem) & "*') AND "
Next varItem
For Each varItem In Me!mslbAuthorQry.ItemsSelected
strCriteria = strCriteria & "((Master.Author) Like '*" _
& Me!mslbAuthorQry.ItemData(varItem) & "*') AND "
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list(s)" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Left(strCriteria, Len(strCriteria) - 5)
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM Master WHERE " & strCriteria
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "qryMultiSelect"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

Thanks again for your help
 
Re: Creating Listbox into "search form" that generates query - see code - almost ther

FWIW, it sounds like you're working with some very de-normalized data, which is really going to make things incredibly/increasingly difficult for you as you try to get results from it.

I don't know what your data looks like, so I can't really speak to the type of OR criteria you would need. Some more information (such as example data) would be helpful.
 
Re: Creating Listbox into "search form" that generates query - see code - almost ther

Hi all, hoping you can help me. Sorry if I'm out of line posting a question to the end of this thread but I'm working on the same issue and thought it would be easier to refer to examples above rather than repeat them in a new thread.

I've used both the code above as well as the link posted by Thales750 but keep running into errors.

I don't have querydefs setup so I've defined Q as AllQueries (qdf in Fred's code) which I believe should work. The big error is coming when I try to define DB as Database. Database doesn't come up as an optional user-defined type in my definitions. Is there another option that I can use?

My current code is below:

Private Sub Command42_Click()
Dim Q As AllQueries
Dim DB As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant
Set ctl = Me![List40]
For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) & Chr(34)
End If
Next Itm
If Len(Criteria) = 0 Then
Itm = MsgBox("You must select at least 1 fund", 0, "No Selection Made")
Exit Sub
End If
Set DB = CurrentDb()
Set Q = DB.AllQueries("qryActual_balances3")
Q.sql = "Select * from Accounts where [Acct_Acronym] in(" & Criteria & ");"
Q.Close
DoCmd.OpenQuery "qryActual_balances3"

End Sub

Thanks for any help you can provide
 
Re: Creating Listbox into "search form" that generates query - see code - almost ther

Sorry, should have mentioned I'm using Access 2002.

Thanks again
 
Re: Creating Listbox into "search form" that generates query - see code - almost ther

Fixed it... for some reason Microsoft DAO 3.6 Object Library was de-selected in the References box.

Code works perfectly now.
 
You must log in or register to reply here.

Similar threads

S
Getting total in one line of the query
Replies
13
Views
241
silentwolf
S
N
VBA for populating a subform from data in a listbox
Replies
12
Views
686
June7
June7
H
Solved Search across multiple fields
Replies
8
Views
617
Harry Paraskeva
H
T
GPT and a Multi-Select List box.
Replies
7
Views
1,073
Pat Hartman
P
E
Solved Listbox search not working with aggregated query
Replies
7
Views
625
elannesse
E

Users who are viewing this thread

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