Dcount query

[Ginty]

i want to calculate the number of males that are currently working at my company.

i am currently using this code to work out the percentage of males.

Public Function getPercentageOfMales()

Dim males As Integer

Dim totalem As Integer

Dim per As Integer


males = DCount("[SEX]", "ALL DETAILS", "[SEX] = 'M'")

totalem = DCount("[SEX]", "ALL DETAILS")

per = males / totalem * 100


getPercentageOfMales = per

Exit Function

End Function

but i need to calculate the people that.. sex = 'm' and [Finish date] is null

i tried using this but it does not work,

males = DCount("[SEX]", "ALL DETAILS", "[SEX] = 'M'" And [FINISH DATE] Is Null)


thanks for you help

 

[modest]

males = DCount("[SEX]", "ALL DETAILS", "[SEX] = 'M'" And [FINISH DATE] Is Null)

You still need to include everything in the string:
males = DCount("[SEX]", "ALL DETAILS", "[SEX] = 'M'" And [FINISH DATE] Is Null)

Change it to this:

males = DCount("[SEX]", "ALL DETAILS", "[SEX] = 'M' And [FINISH DATE] Is Null")

If the SQL "Is Null" does not work correctly, use Access's built-in function

males = DCount("[SEX]", "ALL DETAILS", "[SEX] = 'M' And IsNull([FINISH DATE])=True")

Hope this helps

 

[Ginty]

thanks it works, i understand it now.

i was using 2 queries to work this out before now.