Convert a complex DCount to SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Convert a complex DCount to SQL

Author

Topic

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-07-19 : 11:07:59

Hi

I'm trying to convert the following Access DCount to something that will work with MS SQL. I believe it should be just a COUNT function but I can't get it to work. Any ideas please? Thank you :)

DCount("[FamiliesID]","[Tbl_Placements]","([DateOfPlacement] is not null) AND ([DatePlacementFinished] IS NULL) AND [Tbl_Placements]![FamiliesID] = " & [tbl_Families]![FamiliesID])

Jim

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-07-19 : 11:10:23

(p.s. I only want those that are >0 from the above Access code, not sure if that needs to be built into code beforehand?)

Jim

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-07-21 : 13:16:58

Maybe?:



SELECT COUNT(*)
FROM Tbl_Placements tp
INNER JOIN Tbl_Families tf ON
    tf.FamiliesID = tp.FamiliesID
WHERE
    tp.FamiliesID > 0 AND
    DatePlacementFinished IS NULL

jimtimber
Yak Posting Veteran

60 Posts

Posted - 2014-07-22 : 07:47:55

Hi Scott,

On its own, it works but i'll need to try it with other fields included in the same query. I'll let you know,

Thanks once again.

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources