Count Items in Column

[ECEstudent]

Hi, I'm stuck on this one part of my code where I am trying to count values in a column called 'ItemQty' in table 'dbo_Item'. I only want to count the values with the associated values in column 'OrderNumber'. I am getting the values of 'OrderNumber' from an array. Here is my code...(it doesn't work though. When I put a Msgbox to print out what the ItemQuantity value ends up to be, it only prints my code back to me.)

For Each Order In q
            
MsgBox "Order = '" & Order & "'"
                    
ItemQuantity = ItemQuantity + ("count(ItemQty) Where OrderNumber LIKE '*" & Order & "'")
                    
Next Order

 

[ECEstudent]

OrderNumber l ItemQty

1247 l 2

1789 l 4

1247 l 3

2468 l 1

1247 l 2

----------------------------------


For OrderNumber 1247...the ItemQuantity total should end up to be 2+3+2=7. So at the end, it should display 7. I'd appreciate any help. Thanks a lot.

 

[bob fitz]

I think you need to create a "Totals" type query. Search the access help files for "totals query" for more information about how this can be done. Post back if you're still stuck.

 

[MikeLeBen]

How about:

For Each Order In q
            
MsgBox "Order = '" & Me.OrderNumber & "' Item Quantity = '" & DSum("ItemQty", "yourTblOrders", "[OrderNumber] = Me.OrderNumber) & "'"

Next Order