Using Dcount to count records of 4 flavors...

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I have a table (TEMP) which contains 3 fields:
-----------------------------------------------------
Item text
Type text
Status text

I would like to get a count of how many records in my table
have the following criteria:

Type = A and Status = 0
Type = B and Status = 0
Type = D and Status = 0
Type = S and Status = 0

When I run the following:

Dim Total As Integer

Total = DCOUNT( "*", "TEMP", "Type ='A' AND Status = '0'" OR "Type ='B'
AND Status = '0'" OR "Type ='D' AND Status = '0'" OR "Type ='S' AND
Status = '0'")

I get a run time error 13- type mismatch...

What is the correct syntax and how can I check to see how many records exist
per the above criteria?

Thanks
 
Enclose the whole criteria string inside one set of double quotes;

DCount("*","TEMP","Type = 'A' And Status = '0' Or Type = 'B' And Status =
'0' Or Type = 'D' And Status = '0' Or Type = 'S' And Status = '0'")
 
I have a table (TEMP) which contains 3 fields:
-----------------------------------------------------
Item text
Type text
Status text

I would like to get a count of how many records in my table
have the following criteria:

Type = A and Status = 0
Type = B and Status = 0
Type = D and Status = 0
Type = S and Status = 0

When I run the following:

Dim Total As Integer

Total = DCOUNT( "*", "TEMP", "Type ='A' AND Status = '0'" OR "Type ='B'
AND Status = '0'" OR "Type ='D' AND Status = '0'" OR "Type ='S' AND
Status = '0'")

I get a run time error 13- type mismatch...

What is the correct syntax and how can I check to see how many records exist
per the above criteria?
Click to expand...

You can actually do this in a single query with no DCounts, no IIFs:

SELECT [Type], Count(*) AS HowMany
FROM [TEMP]
WHERE [Type] IN ("A", "B", "D", "S")
AND Status = 0
GROUP BY Type;
 
thanks Beetle-

That worked great!


Beetle said:
Enclose the whole criteria string inside one set of double quotes;

DCount("*","TEMP","Type = 'A' And Status = '0' Or Type = 'B' And Status =
'0' Or Type = 'D' And Status = '0' Or Type = 'S' And Status = '0'")
Click to expand...
 
Back
Top