dcount with multiple critera

[Ceri]

Help! I'm trying to count the number of records in a form that have a set
value in one field and no value in another field. I've managed to get the
Dcount function to count all of the records but as soon as I enter criteria
it either comes up with 0 or with the total number of records.

Am I using it wrong or should I be using a different method altogether.

Thanks
Ceri

 

[Dan Artuso]

Hi,
You'll have to post what you've tried already and also, the data types of the fields
you're using for criteria because that will effect how they will be delimited within your
statement.

 

[Ceri]

I've got a little further now and managed to get one criteria to work, Ive
tried =DCount("*","tblmciform",'[WasteTransferNote-Oil] is null')
and =DCount("*","tblmciform",'[type]="oil"') both of which give the right
answers, but when I combine them and enter
=DCount("*","tblmciform",'[type]="oil"' And '[WasteTransferNote-Oil] is
null') it gives me the total number of records ot the table. The data types
are Text for Type and Number for WasteTransferNote-Oil

Hope this makes it clearer

Ceri

 

[Ceri]

I've gone a little further now, the fomula I'm trying is
=DCount("*","tblmciform",'[type]="oil"' And '[WasteTransferNote-Oil] Is
Null')And '[WasteTransferNote-Oil] Is Null')
I've got them to work with one criteria, both
=DCount("*","tblmciform",'[type]="oil"')
and
=DCount("*","tblmciform",'[WasteTransferNote-Oil] Is Null')
give me the right answers, but they dont work when combined, they just give
me the total number of records.
The Data types are Text for Type and Number (Long Integer) for
WasteTransferNote-Oil.

I hope this makes it a bit clearer

Thanks
Ceri

 

[Ceri]

sorry, i think I've got it now, the syntax was slightly wrong, I needed
=DCount("*","tblmciform",'[Type]="oil" And [WasteTransferNote-oil] Is Null')
any ideas how I add a wild card so that it looks for anything in the field
with oil in it?

I've gone a little further now, the fomula I'm trying is
=DCount("*","tblmciform",'[type]="oil"' And '[WasteTransferNote-Oil] Is
Null')And '[WasteTransferNote-Oil] Is Null')
I've got them to work with one criteria, both
=DCount("*","tblmciform",'[type]="oil"')
and
=DCount("*","tblmciform",'[WasteTransferNote-Oil] Is Null')
give me the right answers, but they dont work when combined, they just give
me the total number of records.
The Data types are Text for Type and Number (Long Integer) for
WasteTransferNote-Oil.

I hope this makes it a bit clearer

Thanks
Ceri

Hi,
You'll have to post what you've tried already and also, the data types of the fields
you're using for criteria because that will effect how they will be delimited within your
statement.

 

[Dan Artuso]

Hi Try this:
DCount("*","tblmciform","[type] = 'oil' And [WasteTransferNote-Oil] Is Null")

--
HTH
Dan Artuso, Access MVP

I've gone a little further now, the fomula I'm trying is
=DCount("*","tblmciform",'[type]="oil"' And '[WasteTransferNote-Oil] Is
Null')And '[WasteTransferNote-Oil] Is Null')
I've got them to work with one criteria, both
=DCount("*","tblmciform",'[type]="oil"')
and
=DCount("*","tblmciform",'[WasteTransferNote-Oil] Is Null')
give me the right answers, but they dont work when combined, they just give
me the total number of records.
The Data types are Text for Type and Number (Long Integer) for
WasteTransferNote-Oil.

I hope this makes it a bit clearer

Thanks
Ceri

Hi,
You'll have to post what you've tried already and also, the data types of the fields
you're using for criteria because that will effect how they will be delimited within your
statement.

 

[John Spencer (MVP)]

DCount("*","tblmciform","[type] Like '*oil*' And [WasteTransferNote-Oil] Is Null")

sorry, i think I've got it now, the syntax was slightly wrong, I needed
=DCount("*","tblmciform",'[Type]="oil" And [WasteTransferNote-oil] Is Null')
any ideas how I add a wild card so that it looks for anything in the field
with oil in it?

I've gone a little further now, the fomula I'm trying is
=DCount("*","tblmciform",'[type]="oil"' And '[WasteTransferNote-Oil] Is
Null')And '[WasteTransferNote-Oil] Is Null')
I've got them to work with one criteria, both
=DCount("*","tblmciform",'[type]="oil"')
and
=DCount("*","tblmciform",'[WasteTransferNote-Oil] Is Null')
give me the right answers, but they dont work when combined, they just give
me the total number of records.
The Data types are Text for Type and Number (Long Integer) for
WasteTransferNote-Oil.

I hope this makes it a bit clearer

Thanks
Ceri

Hi,
You'll have to post what you've tried already and also, the data types of the fields
you're using for criteria because that will effect how they will be delimited within your
statement.

--
HTH
Dan Artuso, Access MVP


Help! I'm trying to count the number of records in a form that have a set
value in one field and no value in another field. I've managed to get the
Dcount function to count all of the records but as soon as I enter criteria
it either comes up with 0 or with the total number of records.

Am I using it wrong or should I be using a different method altogether.

Thanks
Ceri