User-defined query

[xiaochou]

Hello all,
I am trying to wrap my mind around a way to create a user-defined query/report using checkboxes rather than text boxes. I'm sure I can create a drop down list, etc. and create a query dependent upon the values of those text boxes, but I would like to have a simple label next to a check box that refers to specific fields within multiple tables. All the user would have to do is click the checkbox to specify what fields he/she wants and from what tables. For example:
Table 1:
Field 1A
Field 1B
Table 2:
Field 2A
Field 2B
Can anybody give me just a little direction on how to accomplish this? Is there a way in the criteria of a query to do this using the "true" value of the checkbox? Or...would VBA be the best way to go rather than SQL?

Thanks for your time....

[Norie]

I don't really think there is a way to do this without VBA.

What you need to do is dynamically create SQL based on the user's choices.

How to do that depends on what you actually want the user to be able to do and also how you want to combine the 2 tables to get the required results.

[OBP]

Hello, for a few fields you can do this with VBA and using the criteria rows of the query.
If you want to choose a lot of fields then you would probably have to use VBA to write a query definition (def).
I have an example of using options which are stored in a temproray table to control which fields are filtered in the query.
I can post it if you want or talk you through creating one.

[xiaochou]

OBP,
If you wouldn't mind posting that example, I would sincerely appreciate it. I will do some more hunting myself for a solution in the meantime so I can understand exactly what you might have in mind...
Cheers

[OBP]

xiaochou, here is an example of a search form, I have sanitized the data, but left enough there for you to "play" with.
In the database there are 2 forms, some tables and queries.
The Search form opens automatically and can be searched using any combination of the fields, dates 8D options from the "Option Group". The Option Group selection is stored in the "Option" table via the Option form using simple VBA. The main power of the search is simple and is all contained within the search query using "wild card" criteria in the Criteria row for each column/field.

[xiaochou]

OBP,
Sorry for the lengthy delay...I've been out of the office.
What you've posted is very slick and I plan on creating something very similar for another piece of my current project. Thank you very much. I'm still learning....
I believe my problem, however, lies in the fact that I can not seem to figure out how to dynamically query FIELDS only. I can dynamically query the VALUE of a field or fields, but finding a SQL statement that allows me to choose only the fields to search and order by is problematic thus far.
For example:
Let's say I have a form that allows the user to filter by FIELDs only. I supply them with a combo/list box and they choose multiple FIELDS they want to search for (assume all values for these fields will be the result and then I can throw an 'ORDER BY' in there so that they're filtered..). Now, if I define (dim) a string for the multiple (or single) fields, how might I place that string into the sql SELECT statement?

SELECT (something in here) FROM tblEmployee ORDER BY SSN;
(let's say the possible fields could be last name, SSN and sex code)
In other words, the "something in here" part is a variable or combination of variable fields....
Am I making any sense? Sorry if I'm not specific enough...as you know, I'm a beginner.

[OBP]

I have just developed some SQL using field names in a string.
The field names are in a list box that the user clicks on, each click adds the field to the string with the correct seperator. When the user is finished there is a seperator on the end that has to be removed.
This part builds the string.
inputTitles = inputTitles & Me.lstColSource.Column(1) & ", "
This part trims the String
inputTitles = Left(inputTitles, (Len(inputTitles) - 2))
This part uses it in an update query
CurrentDb.Execute "INSERT INTO tbl_Address ( " & outputTitles & ") SELECT " & inputTitles & " FROM tbl_Temp;"

To see the correct Syntax for the SQL statement Create a Select Query and then in design view change the "view" to SQL View. It will look like this
SELECT tbl_X_Ref_Address.AddressID, tbl_X_Ref_Address.CategoryID, tbl_X_Ref_Address.SubCategoryID
FROM tbl_X_Ref_Address;

So you will have to build your string as I did using the ", "

If you need help building it post a copy of the Table and form in a zipped database and I will put it together for you.

[xiaochou]

I can't give up yet OBP...
I'm realizing the problem (based on what you've shown me) is in how I'm building my string. What is shown below was actually created for field values I'm guessing. I probably have to alter how my string is built.
This is what is shown right now where lstEEInfo is the list box and strEE is the string I'm putting into the SELECT statement:

' Build criteria string for Employee List Box based on (tblEE)
For Each varItem In Me.lstEEInfo.ItemsSelected
strEE = strEE & ",'" & Me.lstEEInfo.ItemData(varItem) & "'"
Next varItem
If Len(strEE) = 0 Then
strEE = "Like '*'"
Else
strEE = Right(strEE, Len(strEE) - 1)
strEE = "IN(" & strEE & ")"
End If

I don't pretend to completely understand this, but it looks as though this code basically says that for each item selected, create a string defined by those items selected and add quotes. If nothing is selected, then choose "Like'*'" ....
I'm guessing that the code and/or syntax is really wrong for what I'm trying to do as this results in a pop up box asking for a parameter value for "Like '*'" if I don't choose anything. I probably don't need anything past the 2nd line....

[OBP]

I am surprised that the Like * asks for a Parameter as it a "wild card" search, i.e. find anything.
I take it the code is not yours?:)
It looks as if it designed to be used with a list box with the "multi select" property set to "yes".
Does it work if you do select something?

[xiaochou]

That's exactly what it was originally desinged for. I was hoping to work my way back from that code to what I needed. Yes, the multi select property is supposed to be set to yes. It does not work if I select something.
Here is a link to the full code I was using...I just renamed the specifics and tried to integrate the field variable:
http://www.fontstuff.com/access/acctut18code2DAO.htm
I should probably not be messing with what I don't fully understand but feel like I can get it if I keep trying....
You certainly don't have to keep messing with this. If you're up for it though, I can post the full code that I am actually using right now:

' This code uses DAO and is suitable for Access 97. If using this code in
' Access 2000 (and later) a reference must be set to the Microsoft DAO 3.x
' Object Library (where x is the highest available number).
Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim blnQueryExists As Boolean
Dim varItem As Variant
Dim strEE As String
Dim strER As String
Dim strERCondition As String
Dim strSQL As String

' Check for the existence of the stored query
Set db = CurrentDb
blnQueryExists = False
For Each qdf In db.QueryDefs
If qdf.Name = "qryBuildQuery" Then
blnQueryExists = True
Exit For
End If
Next qdf
' Create the query if it does not already exist
If blnQueryExists = False Then
Set qdf = db.CreateQueryDef("qryBuildQuery")
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "qryBuildQuery") = acObjStateOpen Then
DoCmd.Close acQuery, "qryBuildQuery"
End If

' Build criteria string for Employee(tblEE)
For Each varItem In Me.lstEEInfo.ItemsSelected
strEE = strEE & ",'" & Me.lstEEInfo.ItemData(varItem) & "'"
Next varItem
If Len(strEE) = 0 Then
strEE = "Like '*'"
Else
strEE = Right(strEE, Len(strEE) - 1)
strEE = "IN(" & strEE & ")"
End If

' Build criteria string for Department(tblER)
For Each varItem In Me.lstERInfo.ItemsSelected
strER = strER & ",'" & Me.lstERInfo.ItemData(varItem) & "'"
Next varItem
If Len(strER) = 0 Then
strER = "Like '*'"
Else
strER = Right(strER, Len(strER) - 1)
strER = "IN(" & strER & ")"
End If


' Get Employer condition
If Me.optAndER.Value = True Then
strERCondition = " AND "
Else
strERCondition = " OR "
End If

' Build SQL statement
strSQL = "SELECT " & strEE & " FROM tblEE " & _
"ORDER BY [SSN];"
'This is what I am trying...
'SQL = "Select * from [" & TableSource & "]"
'SQL = SQL & " Where [" & SearchField & "] Like "*'" & SearchString & "*"""
'SQL = SQL & " Order by [" & SortField & "]"

' Apply the SQL statement to the stored query
Set db = CurrentDb
Set qdf = db.QueryDefs("qryBuildQuery")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing
' Open the Query
DoCmd.OpenQuery "qryBuildQuery"
' If required the dialog can be closed at this point
' DoCmd.Close acForm, Me.Name
' Restore screen updating
DoCmd.Echo True
End Sub

[OBP]

I am not surprised that you are struggling to make it work, it is very VBA intensive. I think that you should start with something simple.
Can you post your tables and search form on here, less data if necessary?
If so I will create your SQL string based on just a couple of selections with simple code and you can take it from there.
Or email me a zipped copy.
How do you control matching the field selection with the search field text that you will search for.
If someone ticks field 1 but puts a search item in field 2 for instance.

[OBP]

I would have the search field text inputs hidden until the user ticks the box for it and also check that there is something in them before trying to run the Query.

[xiaochou]

Here's the file...stripped down.

[OBP]

There doesn't appear to be a database in the zip file.
I know you said it was stripped LOL.

[xiaochou]

My employer's email system won't let me send it and must've stripped it when I sent it the last time;
I will have to try to send it home (which they strip as well sometimes) and try to send it to you.
If that doesn't work this evening I should be able to use my micro drive to take it home with me and send it from my home addy.
Sorry for the trouble....

[xiaochou]

Ok, I actually have it working for fields that have no spaces (SSN, Reason, etc), but those with spaces in the field name like "Employer Name" don't work. I know naming fields with spaces probably isn't good practice.
This is only my 2nd database so I'm learning...
I'll see if I can fix it...

[xiaochou]

Here's the building of the string that actually works. I had to add brackets to allow SQL to use those fields with spaces in them. A better method, of course, would be not to use spaces at all, but....

' Build criteria string for Employee(tblEE)
For Each varItem In Me.lstEEInfo.ItemsSelected
strEE = strEE & "[" & Me.lstEEInfo.ItemData(varItem) & "]" & ","
Next varItem
strEE = Left(strEE, Len(strEE) - 1)

OBP, thank you so much for your help with this and being so patient. I sincerely appreciate your time.

[OBP]

I am glad that you got it to work, I found that same problem with names with spaces, but as I was importing the field titles from Excel I just wrote some code to take out the spaces. I will take a copy of your code for future reference.

[mdmackillop]

I should probably not be messing with what I don't fully understand but feel like I can get it if I keep trying....
This is known as the "learning process" ...and we've all been there.:doh: