SQL code to select all (*) except for a few fields

[J360]

Access 2003/Windows XP

I have several multi select listboxes on a form. No selection by the user is currently coded as if the user selected all:

If Len(strAVP) = 0 Then
strAVP = "Like '*'"

this is repeated for all the list boxes and a query is then run using:

strSQL = "SELECT tbl.* tbl " &
"Where AVP " & strAVP & " AND " & _
";"

The code works perfectly.

For this case, it would be great if a len(0) actually meant to exclude that field from the query. What would the SQL string need to read? "Select tbl.* from tbl EXCEPT/BUT/NOT ?? None of these commands seem to work. Any suggestions would be great.

[Stewart Ross]

Hi. SQL does not work this way at all....

'SELECT * FROM ' means select all fields. The wildcard is like any other all inclusive operator - it cannot be overridden or subtracted from in the way you suggest, and there is no EXCEPT operator you can call upon.

-Stewart

[J360]

Hi. SQL does not work this way at all....

'SELECT * FROM ' means select all fields. The wildcard is like any other all inclusive operator - it cannot be overridden or subtracted from in the way you suggest, and there is no EXCEPT operator you can call upon.

-Stewart

Thanks. I realize that will select all fields. I didn't realize there is no way to subtract a field once all is selected. Any suggestions how to overcome this? The only other method I can think of is to write a large If statement to cycle through all the possible parameters for the query.

I.E. something like:

IF Len(str1) = 0 ... Then strSQL = Select str2, str 3, ..., strN from... where...

Else If len(str1) > 0 ....
Then strSQL = Select str

and so on and so forth for every multiselect listbox. This just seems so inefficient. Any ideas?

[Stewart Ross]

The norm is to specify the fields you want to include, not the ones you don't want. It's just a list of fields immediately following SELECT. I'm sure you know this already.

SELECT field1, field2, field3, ... fieldn FROM ...

This really is no big deal to do - which is I'm sure why SQL has no 'except this field' facility in the first place...

The order in which you select the fields is the order you see in the resultant query, unlike with the use of the wildcard where the order is not variable. Also, any field can be given an alias name to aid readability - again something that cannot be done with the wildcard. SELECT * is more rarely used than you might imagine.

In your particular application, you can build your SQL string in stages, using IFs or CASE statements to add the remaining fields to the string by testing for non-zero length controls you mentioned in post 1. There is no need to replicate the whole SELECT every time; you define the main parts of it just once, and add the fields that may or may not be selected using IF or Case to test the relevant controls as you go.

This kind of approach is commonly used in search by form applications; the SQL string is built up in stages, reflecting the values filled in by users in the form template.

-Stewart

[J360]

Thank you. I'll give that approach a try.

[J360]

What if the user is not linear in her selections? I.E. makes a selection in boxes 2 & 9 but not in 1 & 3-8? How would you plan for that?

What I know I can do is use CASE to say
CASE 1 - user makes selection in box 1 and no other
Case 2 - user makes selection in box 1&2

What am I missing? Would I need to loop to check which boxes have selections, what those selections are, and then enter the case statements? The amount of possible combinations and therefore CASE statements seems huge. I think I'm way out of my league on this assignment. If so, I apologize for wasting your time with these questions.

[J360]

What if I did something similar to this:

After getting the inputs from the list boxes:

For each str:
If len(str) = 0 then
str = ""
Else

I would then set

strXXX = str1 + str 2 +...+strN

CASE 1 len(strXXX) = 0
MsgBox "you must make selection"

Case 2 len(strXXX) <>0

This is where it breaks down in my head. I think I need to write a loop to check which strings are not = "" and add those values and their corresponding fields in my SQL so that:

strSQL = select Field1 corresponding to str1 etc from tbl where Field 1 = [str1]

A) Can I write a for each loop and specify the strings in the VBA as the items?
B) Am I on the right track?

[Stewart Ross]

Hi. It's not a problem trying to help with your questions, so don't worry about that!

I think you need to step back a little and ask yourself what you really want to achieve. You haven't actually told us what the listboxes select for your users. Normally, for search-by-form applications, no user entry in a field is taken to mean 'show all' (just as you said in post 1). It is not normally taken by users to mean 'exclude this field'. That would be counter-intuitive, and I suggest you reconsider this aspect unless you are prepared for much confusion on the part of your users as to how to use your form.

Anyhow, you need to define what fields will always be included regardless of user selections, and then define what the optional fields will be that depend on the user selections. There is no need to consider combinations of listbox selections here, as effectively you will simply add in fields corresponding to whatever tests you make (so up to 9 tests at most I guess). I can't guess at what these might be, as you have not given any details that would allow me to provide skeleton SQL for you.

Sure, depending on how you name your listboxes you can process them within a loop - perhaps storing the field names in an array. There are many ways to do what you need, but I can't really advise further without knowing the details of what fields should/should not be included.

Cheers

Stewart

[J360]

I have a table with trended monthly financial data for a full year. The basic layout of the table is:

Fields: Manager I Branch I Product I Month 1 I Month 2 ... Month 12

I then have a form with 9 multiselect listboxes corresponding to the 9 fields that aren't the monthly data (user must see all Months in the qry and subsequent report).

Ideally, I would like to allow the user full freedom to select any or all of the 9 fields, run a query, and then display the results in a report.

So for instance, a manager may only want to see her name and the performance data for 12 months. She would click on her name only in the list box. All other list boxes would be understood to be empty. The Query would then spit out the fields Manager I Month 1 I...I Month 12 and there would only be one record - Her name plus the average of the data for each month.

I can currently run a query, but no selection in a list box is taken to mean select all. The query then displays all the fields and all the data (not averaged).