Problem with "count" function

[arunakumari02]

Dim X as integer

X = Val("SELECT COUNT(DISTINCT ID) FROM tblResource GROUP BY ID HAVING ProjectID =" & labID)

I have a problem with this query. This should return 3 but return 0.

Any help

 

[sbenj69]

why don't you use a dcount function instead?

X = dcount([ID], "tblResource", "ProjectID = '" & labID &"'")

should that be me.labID?

 

[arunakumari02]

I have to count only unquie values in the table where Dcount do not work.

Any help

 

[sbenj69]

lol.... duh..... look at the sql

sorry, at work, doing about 5 things at once

 

[sbenj69]

Have a look at this:

http://allenbrowne.com/ser-66.html

 

[MSAccessRookie]

Dim X as integer

X = Val("SELECT COUNT(DISTINCT ID) FROM tblResource GROUP BY ID HAVING ProjectID =" & labID)

I have a problem with this query. This should return 3 but return 0.

Any help

Try breaking the query down in pieces

SELECT DISTINCT ID FROM tblResource;

SELECT DISTINCT ID FROM tblResource WHERE ProjectID = me.labID;

SELECT COUNT(DISTINCT ID) FROM tblResource GROUP BY ID HAVING ProjectID = me.labID;

See if each sub query adds up right. Perhaps you will find a weakness to build up.

 

[arunakumari02]

I still have a problem.

The statement generates an error:

int X = ECount("[ID]", "tblResource", "[ProjectID] =" & labID)

The Ecount should generate unique values if ID from the tblResource where projectId = labID

Any help