Solved: Counting to a point in a recordset

[bhunley]

Okay so I have a database used for quality control. It has a main form where a part number is either entered or looked up. When a part number is entered into the "Part Number" field a subform runs a query and shows all instances for that part number in table "Main". Then a second subform references the same query to pull the total number of entries and number of rejected items into textboxes using the expression below.

Ex: =DCount("[PART NUMBER]","InspCount","[PART NUMBER]")
Located in the Control Source

Now I need to find a way to only count the number of records since the last rejected one. For example: If I have the following data:

Prt# Acc Rej
1234 100 000
1234 300 000
1234 010 090
1234 200 000
1234 000 200

I currently get "4" total and "1" rejected, but I also need to get "2" since last rejection. Bsaically, I need to count the number of 0's till I hit a number. The numbers display in textboxes and the words are just labels.

Sorry, I cannot post a sample db due to confidentiality reasons. Thanks in advance for your help. Oh, and this being my 5th day playing with VBA in Access, a simplified answer would be appreciated. I did not build this database, I was just asked to modify it. Thank you.

[omocaig]

you cannot do this with only the 3 fields listed, because unless I am mistaken there is nothing provided to determine what "last" is. Ideally you would need a data field so that you can order records by date and determine when the last rejection occured. Once you have that you can count the number of records since that date.

hth,
Giacomo

[bhunley]

Giacomo,

Thanks for the quick reply. The example table shown above is not complete, I was only using it as an example. My query returns the following columns: Part #, Date, QTY REC, QTY ACC, QTY REJ, DIFFERENCE. So I do have a date field. So can it be done using that data set? Also, the query already orders the results from newest to oldest. I just don't know how to identify the date when a rejection occured and then count forward from there. Thanks again for your help.

[bhunley]

Well, thanks again for your help. I figured it out. I ended up using:

=DLookUp("[DATE]","LOOKUP Query","[QTY DEFECT]>0")
This returned the last time the item was rejected.
Then I used:
=DCount("[DATE]","LOOKUP Query","DATE>RejDate")
This counted the rows above the last rejection date.