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.

 

[braindead2]

Not sure if this is any use to you but the way I prefer to work with multi combo boxes are to build the string then use it as criteria
example:
I name the combo boxes in sequence and put the name of the field I want to search in the tag property.
combo1,tag = make
combo2 ,tag = model
combo3 ,tag = year
combo4 etc

I set up the row source just like normal then this code on an event

Dim critstr As String, x As Integer
For x = 1 To 7 ' 1 fer each combo
If Me(&quot;combo&quot; & x) <> &quot;&quot; Then
critstr = critstr & Me(&quot;combo&quot; & x).Tag & &quot; = '&quot; & Me(&quot;combo&quot; & x) & &quot;' and &quot;
End If
Next x

critstr = Left(critstr, Len(critstr) - 5) removes the unneeded and
Me.List1.RowSource = &quot;select fields from tbl where &quot; & critstr
Me.Refresh
end sub

this method only works well if searching on same type of field but it sure saves me from having to build all the if null then else statements and keeps the criteria consistant

 

[braindead2]

Sorry I failed to address the count I think the problem would be you need group by included

me.list1.rowsource = &quot;SELECT CarModel, State, Count(CarModel) As [Total Cars]
FROM Car_Research
WHERE &quot; & strcrit & &quot; group by carmodel&quot;

 

[ByronJohnson]

Thanks For Your Help

 

[ByronJohnson]

Now I get the error with the string function Left. It says that it is ambiguous defined. Does anyone know how to fix this?

Thanks.

 

[braindead2]

if you copied the code I posted above exactly then that may be the problem.

critstr = Left(critstr, Len(critstr) - 5) removes the unneeded and
needs to read
critstr = Left(critstr, Len(critstr) - 5) 'removes the unneeded and
the end text is a comment I put at the end to let you know what it does

The code was intended as an example not to run as posted
Sorry!!

 

[ByronJohnson]

I did leave out the comment and I still got the error. Access 2000 is not reconginzing the string function left for some reason and I don't understand why.

 

[ByronJohnson]

Here is my code(your code):

Private Sub cmdCompute_Click()
Dim critstr As String, x As Integer
For x = 1 To 8
If Me(&quot;combo&quot; & x) <> &quot;&quot; Then
critstr = critstr & Me(&quot;combo&quot; & x).Tag & &quot; = '&quot; & Me(&quot;combo&quot; & x) & &quot;' And &quot;
End If
Next x

critstr = Left(critstr, Len(critstr) - 5)
Me.picList.RowSource = &quot;SELECT Distinct lacIbase, Mutation, Count(Mutation) As [Number Observed] FROM The_Big_Query WHERE &quot; & critstr & &quot; group by lacibase, mutation&quot;
Me.Refresh

End Sub

I get the error on this line:

critstr = Left(critstr, Len(critstr) - 5)

Access is not liking the VBA string Fuction Left.

Thanks for your help again. I still am learning how to program within Access.

 

[braindead2]

comment out the left function and put in

msgbox critstr

see if it is getting the string from the comboboxes.

 

[ByronJohnson]

Ok it is getting the string from the comboboxes. The msgbox is displaying what fields within the comboboxs I selected.

 

[braindead2]

hit ctrl g and type in the debug window

? left(&quot;testthis&quot;,3)
should return tes

if not check your references and see if any are missing.

 

[ByronJohnson]

Where is the debug window? Thanks 4 all your help again.

 

[ByronJohnson]

Never mind about the debug window I know what it is. It has been a long day. I type that in and I still get an error wtih the (?).

 

[braindead2]

Sounds like your left function has stopped working all right.
I don't have access to access 2000 right now but in a few hours I'll check to see what goes on with the left() function in access 2000. I'll get back to you. In the mean time go to modules, new, tools, references and see if any boxes say missing on them. if so check the box. and hit apply. You can do a search on this forum and get more details on checking references if I was not clear enough.

 

[ByronJohnson]

I looked at my references and I didn't see any that are missing. That left() function should work because I do see it listed in the help menu. If you find out anything on how to fix this that would be way cool.

 

[braindead2]

On access 2000 as soon as I type ? left( it prompts me for a string. If yours is not doing that, and your references are Ok, then I am at a loss as to tell you what could be wrong. Does your right function work? when you had the critstr go to the msgbox did it read like a where statement should. I see you have posted this a few other times have you got any other responses.

 

[ByronJohnson]

My left and right function don't work but my Mid works. My msgbox read:

studynum = '8000' And Chemname = 'Sugar' and color = 'Green' and species = 'Rat' And

Those five boxes are checked in my refrences. I did get some other replys but basically saying the same thing you did.

 

[ByronJohnson]

I got it.

Thanks.

 

[braindead2]

well heck then you're in.
replace the following lines

critstr = critstr & &quot; and &quot; & Me(&quot;combo&quot; & x).Tag & &quot; = '&quot; & Me(&quot;combo&quot; & x) & &quot;'&quot;
and
critstr = mid(critstr, 5)

move it so it adds the and to the front of the string then remove the and with the mid function.

one more thing I want to note is that I noticed in the return string you refer to study number = '8000' if study number is indeed a number field and not a text field then you will have problems because numbers dont get enclosed in &quot;'s or it will error but the way around that would be to build those into your string also
example: (not working code)
if me.combo1 <> &quot;&quot; then
strcrit = me.combo1.tag & &quot; = &quot; & me.combo1
end if
for x = 2 to 8 etc... etc....
good luck

 

[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.