Hello,

I have a select query with the following fields and sample data in:
The CRANE feild only ever contains "A" or "B".

CRANE - CELLSTATUS
---------------------------
A - EMPTY
A - STORED
B - STORED
B - HOLD
A - EMPTY
B - STORED
A - STORED

I have a form that uses the following DCount function:

=DCount("*","qryAllData","CRANE ='A'")

This will count all the records with the value of "A", but I want it to be a bit smarter. I want to know the number of records that have "A" in the CRANE field if the value in the CELLSTATUS field is "STORED". I spent ages looking on the forum for similar examples, but they are a bit different & I've tried to adapt them without success. I guess I can use an IF function somehow?
Any help would be much appreciated.

TIA

=DCount("*","qryAllData","[CRANE] ='A' And [CellStatus] = 'STORED' ")

Hello Kevin,

Thanks alot, that worked a treat and saved me having to create lots of queries.

TAC

Hello Kevin,

I've forgotten something :eek: In the same query, I've got a field called QUANTITY. This field contains the values 0, 1 or 2 only.
I'm using the following thanks to your help:

=DCount("*","qryAllData","[CRANE] ='A' And [CELLSTATUS] = 'STORED' And [PARTNO] = '2.0'")

This gives the number of records in CRANE "A", that have a CELLSTATUS of "STORED" and contain a PARTNO "2.0"

Each record needs to be multiplied by the QUANTITY value and then output to the form field. So if I've got 200 records, but each record has a quantity of 2 then the value return should be 400.

Is this possible with the above code or would I need to make a major change. My problem is that I don't understand how to write/use the functions that Access provides. Do you know of a good book for a beginner like myself?

Kind Regards,

You'll need to use a similar expression, but using DLookUp
rather than DCount to return the QTY.

Then simply multiply the two together in another text box...

[DCountField]*[DLookUpField]

A MUCH better and quicker way than using DLookUp and DCount is to use a Totals Query to return your results.

Don't know of any particular books, but the publisher QUE usually do a good range.

Hello Kevin,

I used this code:
=DLookUp("*","qryAllData","[QUANTITY] ")

in a second field on the form hoping that it would display the sum of my QUANTITY field records, but it returns an error? Any ideas.

Thanks for the time.

Will your query always return 2 in the QTY field?
This is what you implied before.

If so then....

=DLookUp("[QUANTITY]","qryAllData")

Then multiply by DCount field

If not then use...DSum instead of DLookUp.

But...This is a VERY inefficient way of going about this and will get SLOWER and SLOWER as your records grow.

Do have a look at Total queries as an alternative.

Thanks Kevin,

The QTY field can be 0, 1 or 2. So I'll try and use the Dsum function as well as looking at using a total query.

Kind Regards,