dcount with multiple critera

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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
 
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?

Ceri said:
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
Dan Artuso said:
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.
Click to expand...
Click to expand...
 
Hi Try this:
DCount("*","tblmciform","[type] = 'oil' And [WasteTransferNote-Oil] Is Null")

--
HTH
Dan Artuso, Access MVP


Ceri said:
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
Dan Artuso said:
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.
Click to expand...
Click to expand...
 
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?

Ceri said:
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
Dan Artuso said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top