Problem with SQL

[arunakumari02]

I have to 3 records with RID's. (2 records have same RID's and 1 iwith different RID)

When i try use this code it print 0 instead of 2.

   intX = Val("SELECT COUNT(DISTINCT [RID]) FROM tblResource GROUP BY [RID] HAVING [ProjectID] =" & labProjectID)

Any Help.

 

[namliam]

That is because the query doesnt get executed... You cannot execute a query in a Val().

Try something like:

Dim rs as dao.recordset
set rs = currentdb.openrecordset(" SELECT COUNT(DISTINCT [RID]) as ridcount " & _
                                 " FROM tblResource " & _
                                 " GROUP BY [RID] " & _
                                 " HAVING [ProjectID] =" & labProjectID)
IntX = rs!RIDCount

 

[arunakumari02]

I still have a problem in the query

Syntax Error (missing operator) in query expression 'COUNT(DISTINCT [RID])'

     Set rstCount = CurrentDb.OpenRecordset("SELECT COUNT(DISTINCT [RID]) AS RIDCount " & _
 "FROM tblResourceDemand GROUP BY [ResourceID] HAVING [ProjectID] = " & labProjectID)
 
    intX = rstCount!RIDCount

Any help

 

[arunakumari02]

When I used this without DISTINCT it worked but I need to have only distinct records which does not include duplicate records.

  [B]Worked without distinct[/B]
 
'Set rstCount = CurrentDb.OpenRecordset("SELECT COUNT([RID]) AS RIDCount " & _
                                  "FROM tblResource WHERE [PID] =" & (labPID))

[B]ERROR: when used DISTINCT[/B]
 
[B]Syntax error in query expression 'COUNT(DISTINCT [RID])[/B]
 
 Set rstCount = CurrentDb.OpenRecordset("SELECT COUNT(DISTINCT [RID]) AS RIDCount " & _
 "FROM tblResource WHERE [PID] =" & (labPID))

Any help?

 

[namliam]

You know I didnt format the SQL just for fun!
Making your code/SQL readable makes for much easier maintenance in the future.

Select count(RID) 
from (Select distinct RID 
      from tblResource 
      where PID = labPID)

 

[arunakumari02]

Could you please help me with the syntax.

[B] Set rstCount = CurrentDb.OpenRecordset("SELECT COUNT(RID) AS RIDCount FROM ("SELECT DISTINCT[RID] FROM tblResource WHERE [PID] =" & labPID)[/B]
 
[B]intX = rstCount!RIDCount[/B]

Where to give quations..etc.

Any Help

 

[namliam]

Your close I think...

Set rstCount = CurrentDb.OpenRecordset("SELECT COUNT(RID) AS RIDCount FROM (SELECT DISTINCT[RID] FROM tblResource WHERE [PID] =" & labPID & ")") 

intX = rstCount!RIDCount

That should do it I think

 

[arunakumari02]

Hey Thanks. It worked.