Dcount or other?

[mafhobb]

Hi.

I use the following code to count the number of records with a specific SKU number:

        A = DCount("SKU", "Calls", "SKU= '" & SKUList!SKU & "'")

This works well, however now I need to narrow down my results. The same table "Calls" has another field "IssueTypeID" which has values from "1" to "9".

I need to find the number of values that have one specific SKU number and then a specific number.

For example, the code above may return "10" with SKU=AAAA, and now I need to know how many records exist that have the same SKU number, AAAA, and "1" in the "IssueTypeID" field. Then AAAAA and "2", then AAAA and "3" up until "9". They should all ad up to "10"

How to do this?

Thanks

mafhobb

 

[plog]

Sounds like the job for a query:

SELECT SKU, IssueTypeID, COUNT(SKU) AS RecordCount
FROM Calls
GROUP BY SKU, IssueTypeID
ORDER BY SKU, IssueTypeID

Depending on what you are actually doing with the information, you could just use the query, or load it into a recordset and work with it that way within your VBA code.

 

[mafhobb]

Plog,

This is very close to what I need, but not quite. I am looking for a single number output for each SKU and IssueTypeID that I input.

For example, based on the previous post, if I enter SKU "AAAA" and IssueTypeID "1" then I need a single number that matches those two. How do I do that?

I will independently enter the SKU and the IssueTypeID, I do not need the complete list at once

mafhobb

 

[plog]

You would assign the input values to variables and use the variables in your DCount:

A = DCount("SKU", "Calls", "SKU= '" & sku & "' AND IssueTypeID='" & iti & "'")

 

[mafhobb]

Got it!

So Dcount does work well for this...Cool

Thank you plog

mafhobb