Having Trouble with Counting Totals

[ByronJohnson]

I have a form with more than 7 combo boxes. Than I have a list box that will either display all records if no combo boxes are selected or some records are displayed depending on which info was selected in the combo boxes. The list box displays three fields. Two are pulled a query which I am not having a problem with. The problem I am having is with the third field. I want to have the third field within the listbox display a total. I can get a total amount for all records but am having trouble getting a totals for individual fields(exp. total of records for VW Jetta's) .

Example:

Query Name:
CarResearch

Form Name: Compare

Fields (comboboxes):
CarType
CarModel
Color
Year
State
Age-Group

List box would display the car model, state, and total number of cars registrate in that state depending on the field selected in the combo boxes.

PsuedoCode I used in Record Source of list Box:
SELECT CarModel, State, Count(CarModel) As [Total Cars]
FROM Car_Research
WHERE
(([Car_Research].[CarModel]=[forms]![Compare]![cboCarModel]
OR
[Car_Research].[CarModel]=[forms]![Compare]![cboCarModel] Is Null)
AND
([Car_Research].[State]=[forms]![Compare]![cboState]
OR
[Car_Research].[State]=[forms]![Compare]![cboState] Is Null)
etc.

 

[ByronJohnson]

I ended up changing study number from a number field to a text field. One more thing. What code would I have to put in if all fields are left blank then the list box would display all fields.

 

[braindead2]

examplenon working)

if critstr = "" then me.list1.recordsource = Select yourfields from yourtable

just drop the where clause from the select statement and then exit the function
error trapping like this should be part of any code you create. One other thing to is you are limiting based on "and" you could easily do this same thing but building an "or" into the select statement

good luck on your future learning looks like you are a promising student.