Checkbox form to allow user to select query output fields

[dizzydangler]

Hi, I'm new to access (2007, running on XP), but I've gotten some great help from this site on my project so far. However, the more I know, the more complex the problems seem to become....

I'm working with all of my data in a single table and, right now, I've got a form linked to a query that allows the user to input whatever search criteria they want through a variety of controls. I've also created a series of check boxes on the form representing every possible field (columns in the table) that the query could return. Since not every user needs or wants to see every field contained in the records, I would like the user to be able to limit the fields returned by the query to only the checkboxes he/she ticks. After the fields are selected, the query should run as usual, returning only the records that satisfy the specified criteria, but only displaying the fields ticked off in the boxes.

I'm building the query in SQL View, and I figure that the key to this must have something to do with the "SELECT" line. Since a normal "fixed-field" query has the fields to be return specified by

SELECT [NameOfTable]![NameOfField1], [NameOfTable]![NameOfField2], etc...

I've tried to build a "dynamic" SELECT statement using the IIF function:

SELECT IIF([Form]![chkbx1]=True, [NameOfTable]![NameOfField1], Null), IIF([Form]![chkbx2]=True, [NameOfTable]![NameOfField2], Null), etc...

This is not working, of course--I don't think "Null" is the proper argument to enter for the condition "chkbx1=Fal se", but I don't know what else to do. Are there any other conditional functions that could help me build a list of SELECT fields based on the Yes/No value of the checkbox controls in the form?

Or am I going about this in completely the wrong way. I think there must be a way to do this, because what I'm trying to get the query to do doesn't seem to be anything too out of the ordinary. Any help would be appreciated!

Thanks in advance,

Joe

[dizzydangler]

Not the Name of the Database but the Name of a Table.

OK--got it! That's what I meant, still learning the syntax.

Needs modification:

rSQL_2 ="SELECT" & Left$(strSQL, Len(strSQL)-2) & "FROM <Table Name> WHERE " & 'Criteria to follow

Still not sure I'm getting this right. Should <Table Name> be in brackets: [Table Name]? I've had to do that in the SQL view of the "old" query because the actual table name has a space in it. I'm assuming that this line of code defines the string "strSQL_2" which is then pasted by the "Set qdfDemo" command on the next line into an SQL statement much like what I constructed manually to build my "old" query in the SQL view. If this is true, I would assume that it needs the brackets, or I need to remove the space from the table name--something I'd rather not do because I have so much other code written which refers to it with the space in it.

Also, it looks like you've got everything between the FROM and the WHERE statement inside of double quotes. Then you put the & symbol, followed by single quotes, all the criteria code then...? a semi-colon, then double quotes?

Finally, I'm going to have to paste quite a few lines of code into the 'Criteria to follow...' section, because I've got about 20 parameters which are used in the query. Is there a length limit to strSQL_2 and, if so, is it possible to define multiple strings and them use them in the "Set qdfDemo" command line?

I will play around with different combinations to see what works--just asking the questions in advance in case I can't crack it.

Thanks again for your time! No way I'd even be close without your help.

Joe

[ADezii]

Still not sure I'm getting this right. Should <Table Name> be in brackets:

If the Table Name contains a Space it must be in Brackets, if not no, as in:

[Table Name] <==> TableName

Also, it looks like you've got everything between the FROM and the WHERE statement inside of double quotes. Then you put the & symbol, followed by single quotes, all the criteria code then...? a semi-colon, then double quotes?

It can all be contained inside double quotes unless you have to refer to something explicitly. To must also use the proper Delimiters (' for Strings, # for Dates, etc.)
Numeric

"Select * From Employees Where [EmployeeID] = 9999;"
"Select * From Employees Where [EmployeeID] = " & Me![txtEmployeeID] & ";"

String

"Select * From Employees Where [LastName] = 'Smith';"
"Select * From Employees Where [LastName] = '" & Me![txtLast] & "';"

Date

"Select * From Employees Where [BirthDate] = #9/2/1954#;"
"Select * From Employees Where [BirthDate] = #" & Me![txtDOB] & "#;"

[dizzydangler]

Still not working.

First question: looking back through what the code is doing, I just realized that the Check Box tags should probably be an exact match of the field names in the table being queried--when I've written code directly into the SQL view of a query (successfully!) , what follows after "SELECT" are strings like [Database 1]![Field 1], etc.

Quote:
If the Table Name contains a Space it must be in Brackets, if not no, as in:
Expand|Select|W rap|Line Numbers [Table Name] <==> TableName

Assume that if the answer to my first question is "yes," then this rule must apply to the Tags, as well, so I either write the tag name as [Field 1], or write the line 13 code as

strSQL = strSQL & "[ctl.Tag]" & ", "
 

Does it make a difference which way I do it?

If the rest of the code was working, I would be able to solve this problem by trial and error, but I am still getting the same "Invalid SQL Statement; expected 'DELETE', etc..." when I run the query with one or more of the "field select" boxes ticked. Here is a simplified version of my code, with the criteria for only one field included in the "WHERE" statement (there are about 20 IIF statements, but I thought it would make more sense if I got the code to work with just one):

Private Sub SearchDB_Click()
On Error Resume Next
Dim ctl As Control
Dim strSQL As String
Dim strSQL_2 As String
Dim qdf As DAO.QueryDef
Dim qdfDemo As DAO.QueryDef
Const conQUERY_NAME As String = "Search_Database"
 
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox And ctl.Tag <> "" Then
If ctl.Value Then
strSQL = strSQL & ctl.Tag & ","
End If
End If
Next
 
If strSQL = "" Then Exit Sub
CurrentDb.QueryDefs.Delete conQUERY_NAME
On Error GoTo Err_SearchDB_Click
strSQL_2 = "SELECT" & Left$(strSQL, Len(strSQL) - 2) & "FROM [Database 1] WHERE” & “((IIf([Forms]![Search Database]![City] Is Null,(([Database 1].[City]) Like "*" & [Forms]![Search Database]![City] & "*" Or ([Database 1].[City]) Is Null),(([Database 1].[City]) Like ("*" & [Forms]![Search Database]![City] & "*"))))<>False);"
 
Set qdfDemo = CurrentDb.CreateQueryDef(conQUERY_NAME, strSQL_2)
 
 
Exit_SearchDB_Click:
Exit Sub
 
Err_SearchDB_Click:
MsgBox Err.Description, vbExclamation, "Error in SearchDB_Click()"
Resume Exit_SearchDB_Click
End Sub
 

I tried it again, even more simplified, with no "WHERE" criteria, so the line 25 code looks like:

strSQL_2 = "SELECT" & Left$(strSQL, Len(strSQL) - 2) & "FROM [Database 1];"
 

...same result.

There's got to be something wrong with where I'm putting the quotes (delimiters?), I just can't see it.

[MMcCarthy]

I've made two minor changes to your code which I have commented. This should get you a little further ahead.

Private Sub SearchDB_Click()
On Error Resume Next
Dim ctl As Control
Dim strSQL As String
Dim strSQL_2 As String
Dim qdf As DAO.QueryDef
Dim qdfDemo As DAO.QueryDef
Const conQUERY_NAME As String = "Search_Database"
 
    For Each ctl In Me.Controls
        If ctl.ControlType = acCheckBox And ctl.Tag <> "" Then
            If ctl.Value Then
                ' put space after comma
                strSQL = strSQL & ctl.Tag & ", "
            End If
        End If
    Next
 
 
    If strSQL = "" Then Exit Sub
    CurrentDb.QueryDefs.Delete conQUERY_NAME
    On Error GoTo Err_SearchDB_Click
 
    ' add space after SELECT, before FROM and after WHERE
    strSQL_2 = "SELECT " & Left$(strSQL, Len(strSQL) - 2) & " FROM [Database 1] WHERE ” & “((IIf([Forms]![Search Database]![City] Is Null,(([Database 1].[City]) Like " * " & [Forms]![Search Database]![City] & " * " Or ([Database 1].[City]) Is Null),(([Database 1].[City]) Like (" * " & [Forms]![Search Database]![City] & " * "))))<>False);"
 
    Set qdfDemo = CurrentDb.CreateQueryDef(conQUERY_NAME, strSQL_2)
 
 
Exit_SearchDB_Click:
    Exit Sub
 
Err_SearchDB_Click:
    MsgBox Err.Description, vbExclamation, "Error in SearchDB_Click()"
    Resume Exit_SearchDB_Click
End Sub
 

Always remember the spaces before and after when substituting values into a string building a query statement.

[dizzydangler]

IT WORKS!!! (sort of!)

after reading over my last post, I went back and re-did all the tags on the "Select Field" checkboxes to match the field names in the [Database 1] table, including brackets [] when they were more than one word. I also realized, based on your explanation, that I needed to type a space after SELECT and before FROM

"SELECT " not "SELECT" and " FROM... not "FROM...

I ran it using a very simple criteria:

strSQL_2 = "SELECT" & Left$(strSQL, Len(strSQL) - 2) & "FROM [Database 1] WHERE [City] Is Not Null;"
 

Nothing happened, but I got no error message. I went to close it out and, whaddaya know, a query table called Search_Database appears under an "Unrelated Objects" title bar in the Navigation View on the left!

I opened up the table and, sure enough, found all records listed where the field "City" was not null!

I played with the checkboxes and got it to build the query results table using with columns only showing the fields I selected. Getting a little cocky now, I went ahead and inserted one of my full-length criteria strings:

strSQL_2 = "SELECT" & Left$(strSQL, Len(strSQL) - 2) & "FROM [Database 1] WHERE ((IIF([City] Is Null, (([Database 1].[City] Like "*" & [City] & "*" Or (([Database 1].[City] Like "*" & [City] & "*")))<>False);"
 

This criteria worked fine in the original query (run from the form called <Search Database>), written like:

WHERE ((IIF([Forms]![Search Database]![City] Is Null, (([Database 1].[City] Like "*" & [Forms]![Search Database]![City] & "*" Or (([Database 1].[City] Like "*" & [Forms]![Search Database]![City] & "*")))<>False);
 

It returns all records if the control <City> is left null but if anything is entered, it returns all records containing the text entered in the control.

All I did was drop the [Forms]![Search Database]! from in front of the control fields. I ran it that way the first time, got a "Type Mismatch" error, then figured I didn't need the form reference since I was running the query straight from the form itself (and your examples didn't have it, either).

No dice, still got the "Type Mismatch" error.

So, my questions are, in order of importance:

1) what is wrong with my criteria formatting?
2) how do I get the query results to automatically pop up in a new table, instead of showing up in the navigation bar after I close the form? (when I hang the DB on the server, the navigation bar is hidden to protect the users--all excel junkies like myself--from the temptation to open up the master table and start plowing through the data to find what they want. So, I need the table results to pop up automatically)
3) Is there a way to control the order of the columns in the query results?

ADezii, you've helped me way more than I can tell you. This is not a life-or-death issue, by any means, and I don't want to impose on your time and patience. At this point, I'm working to provide a more convenient and user-friendly product to the users and trying to learn something useful in the process. (and, OK, I am REALLY stubborn, especially when I KNOW there's got to be a way to get this thing to do what I want it to.) Any help is appreciated, but I'll understand if you want to call it quits. I'll just take my clown show to another forum...:)

Joe

[dizzydangler]

Hey again, msquared! I caught the spacing error in the "," and the "SELECT" and "FROM... statements, but I didn't get the ...WHERE " & "((IIF... bit. Let me give it a try....

[dizzydangler]

Nope, no luck. I tried:

strSQL_2 = "SELECT " & Left$(strSQL, Len(strSQL) - 2) & " FROM [Database 1] WHERE ” & “((IIf([Forms]![Search Database]![City] Is Null,(([Database 1].[City]) Like " * " & [Forms]![Search Database]![City] & " * " Or ([Database 1].[City]) Is Null),(([Database 1].[City]) Like (" * " & [Forms]![Search Database]![City] & " * "))))<>False);"
 

and

strSQL_2 = "SELECT " & Left$(strSQL, Len(strSQL) - 2) & " FROM [Database 1] WHERE ” & “((IIf([City] Is Null,(([Database 1].[City]) Like " * " & [City] & " * " Or ([Database 1].[City]) Is Null),(([Database 1].[City]) Like (" * " & [City] & " * "))))<>False);"
 

both give me the "Type Mismatch" error. Could it be something else?

Joe

[MMcCarthy]

Hi Joe

Firstly remember to add code tags to your posts as it is confusing to follow otherwise.

I'm just looking at your criteria now and will get back shortly.

Mary

[MMcCarthy]

Joe

Your Iff statement doesn't make sense to me. Can you tell me in English what the logic of this is supposed to say.

Mary

[MMcCarthy]

OK Joe

Try this ...

strSQL_2 = "SELECT " & Left$(strSQL, Len(strSQL) - 2) & " FROM [Database 1] " & _
"WHERE [Forms]![Search Database]![City] Is Null " & _
"OR [Database 1].[City]) Like ' * ' & [Forms]![Search Database]![City] & ' * ' "
 

I don't think you should have been using IIF at all. Also I replaced the double quotes around the * with single quotes. You can't put double quotes inside a string (not easily at least) so you have to use single quotes.

Mary