Create query based on fields specified in list box of form

[Burton]

I have a combo box with 3 values which represent fields (not 3 values in a
single field) in a table. As users select one of these fields in the combo
box and hit the command button I want the query to execute the query on that
field and the criteria to be -1. However, if they enter nothing, I want it to
return all records and not have -1 as the criteria.

The table name is staffing_matrix
The combo box is called combo37 and it is in a form called form1
The field names are "PHD", "Master's" and "Bachelors". They are populated
with -1's and 0's. For example, if someone has a PHD, then it is populated
with a -1, otherwise it is a zero.

I have seen several people ask somewhat similar questions, and it seems like
for them, they have always written coding into the command button (which is
called command21). I am a novice at coding and so the code that is typically
written there is over my head. I am wondering if there is a way to do it in
the query instead as the sql makes a little more sense to me than the VBA.
For example, in the "Field" row of the query, could I put something like
[form].[form1].[combo37] to assign the field from the combo box to the
"field" value in the query (I have tried this, by the way, and it didn't
work)?

Any help would be greatly appreciated. Again, I am somewhat of a novice, so
the more detail the better.

Thanks in advance

 

[KARL DEWEY]

Try this --
Have two columns in your combo with -1 stored in first column and text in
second. Set default to 0 (zero). Set column widths to 0"; 1" to only show
text.
In query design view and criteria row enter --
[Forms]![form1]![combo37] OR [Forms]![form1]![combo37] = 0
under each of your fields - PHD, Master's, and Bachelors.

 

[John Spencer]

IF I understand correctly your wish, then the following WHERE clause
might work for you. On the other hand this might give a query too
complex error.


WHERE (((PHD = True And [Forms]![FormName]![ComboboxName] = "PHD")
OR [Forms]![FormName]![ComboboxName] <> "PHD")
AND
((Masters = True And [Forms]![FormName]![ComboboxName]="Masters")
OR [Forms]![FormName]![ComboboxName] <> "Masters")
AND
((Bachelors = True And [Forms]![FormName]![ComboboxName]="Bachelors")
OR [Forms]![FormName]![ComboboxName] <> "Bachelors"))

OR [Forms]![FormName]![ComboboxName] is Null


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================