DCount with True/False criteria (1 Viewer)

[gstylianou]

Hi,

Just a question, How can I write correct the following function below to count only the records which the field [Active]=True?

a = DCount("[Patient_id]", "Patient Diseases", "[Patient_id] =" & Forms!MainForm.LogCustID)

 

[arnelgp]

a = DCount("1", "Patient Diseases", "Patient_ID = " & Forms!MainForm.LogCustID & " And [Active] = -1")

 

[gstylianou]

a = DCount("1", "Patient Diseases", "Patient_ID = " & Forms!MainForm.LogCustID & " And [Active] = -1")

Thanks

 

[Pat Hartman]

Keep in mind that Only Access uses -1 for true. So I would write --

& " AND [Active] = True"

to avoid any future issues.

 

[Gasman]

Could even just use "AND [Active] ?

 

[Pat Hartman]

Yes you can but, personally, I like to read code that is specific.

 

[MarkK]

Also...

a = -DSum("Active", "Patient Diseases", "[Patient_id] =" & Forms!MainForm.LogCustID)

 

[Gasman]

Also...

a = -DSum("Active", "Patient Diseases", "[Patient_id] =" & Forms!MainForm.LogCustID)

@mark
Why the minus sign?

 

[MarkK]

Why the minus sign?

Boolean True equals -1, so if we Sum a boolean field....

 

[Gasman]

Boolean True equals -1, so if we Sum a boolean field....

So anything - is True?, like -30 ? Other than -0 of course

 

[Pat Hartman]

Access treats anything <> 0 as true. That doesn't necessarily apply to any other RDBMS though. SQL Server uses 1 as True so if you ever might need to upsize, use True when checking for the true value and leave the details to the RDBMS to sort out.

 

[Gasman]

Access treats anything <> 0 as true. That doesn't necessarily apply to any other RDBMS though. SQL Server uses 1 as True so if you ever might need to upsize, use True when checking for the true value and leave the details to the RDBMS to sort out.

Thank you Pat.
I did a quick test this mornng to see that was actually the case.

 

[Pat Hartman]

Oh ye of little faith

Sadly Access has numerous situations were it allows very sloppy processing in an effort to "save" the novice but being sloppy always has a price.