Hello All,

I have a query [Sp Performance] with the following fields:

SCAC,CName,Period,Ontime Pickup,Late Pickup,Ontime Delivery,Late Delivery
Pickup %,Delivery %

On my form I have Combobox called [SCAC] and a listbox that contains [CName] that is associated with the [SCAC] combobox

I have the fields:

Ontime Pickup,Late Pickup,Ontime Delivery,Late Delivery
displayed on my form as a end result

If I choose a [SCAC] for the combo box and choose one or more of the [CName] for the listbox.

How do I get these fields on my form to give me the total value for the [SP Performance] query based upon what I choose from the combo box and listbox

**If I choose more than one [CName] it will total the two as a result**

Is this possible??

Any Ideas??

Thanks,

What is the Query now (post it)?
What exactly is it you want to total up?

.

Anyone,

I have attached the database so that you can take a look...

Thanks for your help!!

I'm not exactly sure if this is what you want but here is your sample DB back to you.

Changes I have made are Commented on the Form itself and the code used is located within the OnClick Event of the ListBox Control. The Code is Commented.

Hope it Helps.

WOW CyberLynx!

You are awesome!!! I hope I can be as good soon!
One thing I did notice is when I select more than one CName it doesn't total them both together.

Also when I delselect it doesn't go back to zero or nothing..I think it is adding all when you deselect. Also I have a option button that when select chooses all on the list box ( this would total all at that point)

Any Ideas??

this what I have:
Private Sub lstCName_Click()
'Declare Variables...
Dim Criteria$, i As Integer

'Enumerate through the List items so as to utilize
'those items selected.
For i = 0 To lstCName.ListCount - 1
'Is the List Item Selected?....
If lstCName.Selected(i) = True Then
'Yes it is...
'Does out Criteria Variable already contain something?
'If so, then place the AND Statement at the end of the
'String contained within the variable.
If Criteria <> "" Then Criteria = Criteria & " AND "
'Associate [CName] to the selected Item found in list.
Criteria = Criteria & "[CName]='" & Me.lstCName.ItemData(i) & "'"
End If
'See if there are more selected Items...
Next i

'Fill our Totals Into their Designated TexBoxes.
'We Use the DSum function to get the totals.
Me.[Ontime Pickup] = DSum("[SP Performance]![Ontime Pickup]", "[SP Performance]", Criteria)
Me.[Late Pickup] = DSum("[SP Performance]![Late Pickup]", "[SP Performance]", Criteria)
Me.[Ontime Delivery] = DSum("[SP Performance]![Ontime Delivery]", "[SP Performance]", Criteria)
Me.[Late Delivery] = DSum("[SP Performance]![Late Delivery]", "[SP Performance]", Criteria)
'Done
End Sub

Thanks,

One thing I did notice is when I select more than one CName it doesn't total them both together.

Even though there was a lack of sufficient table data to carry out any significant tests, I believe the function is working correctly in this particular area.

The DSum function used in code is utilizing a Criteria. This criteria is based upon the selections made from the ListBox. For every list selection made, the additional selection is added to the Criteria with the inclusion of the AND statement to bind the criteria together. When I first received your sample DB, the list contained 5 of the same (identical) items. I left a comment on the Form indicating this and that I had changed the RowSource query to include the DISTINCT statement so as to eliminate duplicates within the list.

The way the code works, every time an item is selected within the list (highlighted), it is added to the Criteria. Therefore, if you have five of the same items within the list and you select (highlight) one item the criteria will look like:

[CName]='ARDC'

Once you select a second item then that second item is added to the Criteria once selected. So now the criteria string will look like:

[CNme]='ARDC' AND [CName]='ARDC'

You can already see a problem here but never the less, let's select a third item from the list. Now our Criteria string will look like:

[CName]='ARDC' AND [CName]='ARDC' AND [CName]='ARDC'

This is no good. Three items in the criteria to compare for but they are all the same. If you have fifty items within your list and they are all the same, then you select all of them you end up with a very long criteria string but in reality selecting only one serves the identical purpose.

Multiple items within a criteria string must be distinct between one to the other, otherwise you're just wasting space.

A proper criteria String in your case may be perhaps:

[CName]='ARDC' AND [CName]='DEXL' AND [CName]='WKRP'

Here the criteria string, when placed into DSum function, will seek out and work with records which have the CName table field contains ARDC and where CName contains DEXL and yet again where CName contains WKRP (remember that comedy show :) ).

Take a look at you Data. Is the List really showing what you want or does it really contain the items you want to base your criteria string upon.

Also when I deselect it doesn't go back to zero or nothing..I think it is adding all when you deselect.

Oops....So sorry - My Bad. :o

You are right! It was adding all.

To remedy this, change the code which states:


'Fill our Totals Into their Designated TexBoxes.
'We Use the DSum function to get the totals.
Me.[Ontime Pickup] = DSum("[SP Performance]![Ontime Pickup]", "[SP Performance]", Criteria)
Me.[Late Pickup] = DSum("[SP Performance]![Late Pickup]", "[SP Performance]", Criteria)
Me.[Ontime Delivery] = DSum("[SP Performance]![Ontime Delivery]", "[SP Performance]", Criteria)
Me.[Late Delivery] = DSum("[SP Performance]![Late Delivery]", "[SP Performance]", Criteria)
'Done


To this code (I added a couple of little lines):


'Fill our Totals Into their Designated TexBoxes.
'We Use the DSum function to get the totals.
If Criteria <> "" Then
Me.[Ontime Pickup] = DSum("[SP Performance]![Ontime Pickup]", "[SP Performance]", Criteria)
Me.[Late Pickup] = DSum("[SP Performance]![Late Pickup]", "[SP Performance]", Criteria)
Me.[Ontime Delivery] = DSum("[SP Performance]![Ontime Delivery]", "[SP Performance]", Criteria)
Me.[Late Delivery] = DSum("[SP Performance]![Late Delivery]", "[SP Performance]", Criteria)
Else
Me.[Ontime Pickup] = 0
Me.[Late Pickup] = 0
Me.[Ontime Delivery] = 0
Me.[Late Delivery] = 0
End If
'Done


Hope this helps...

.

Hey Cyberlynx,

I did put the DISTINCT statment in the row source and it only gives me one. However, If I (for instance)

select SPCG in the drop down - I gives me CANON,EUS-MUS,HILLROM,OCE,

now if I choose CANON it works GREAT! however when I choose CANON and HILLROM or any of the others together the results are blank.

I guess I wanted it to give the total of (for instance) CANON + the total for HILLROM if I choose those two or if I choose all of them together it would add them all together and give the total.

Is this possible?

If you are basing the data off of the tables from the sample db you provided then check your tables and make sure the data actually exists.

Like I said earlier....if you are pulling criteria off a list....you don't want duplicates. You would need to add something else in the list (Columns) to make each item distinct and the list display should show that in fact each item is indeed distinct, otherwise how in heavens name will you know what you are really selecting.

Since the Criteria is based from List selections it makes no sense to have identical items within the criteria.....it serves no purpose whatsoever.

.