DMax and DCount

[Chrisopia]

I've been playing around for some time and can't seem to get the hang of either.

Basically all I need it to do is to count how many "Order Types" =1... then to add 1 to it (and order types = 2 etc)

(Note: Order Type 1 = Invoice, Order Type 2 = Quote)
So basically Im filtering through for invoices and quotes to create a counting system.

When i use DMax:

=DMax([Order Type],"tbl1",[Order Type]=1)

I get a list of 1's next to where Order Type is indeed a 1. Where Oder Type=2 nothing is shown (which makes sense so far because I havent told it to read order type 2)

However when I use DCount:

]=DCount([Order Type],"tbl1",[Order Type]=1)

Really cleverly, yet uselessly, it displays the number of the record, but only if Order Type = 1... and again its blank where ever order type is 2.

For example:
Record Number 1,2,3,4,5,6
Order Type 1,2,1,1,2,1
DMax 1, ,1,1, ,1
DCount 1, ,3,4, ,6


What I really need:
Record Number: 1,2,3,4,5,6
Order Type: 1,2,1,1,2,1
End Result: 1,1,2,3,2,4

what am I doing wrong!!??

 

[pbaldy]

Here's a good reference on the syntax of domain aggregates, which you don't have quite right:

http://www.mvps.org/access/general/gen0018.htm

If you're doing what I think you're doing, you want a 2-part criteria. You want the count of how many have:

1) the same order type as the current record being evaluated
2) a record number equal to or less than the current record being evaluated

That should get you the result in your example.