Dcount Null (2 Viewers)

[cybersardinha]

Hi,

I'm hitting a bit of a problem.

I have this formula on VBA:

ngultig = DCount("*", sTblName, mFB & " < " & sExpired)

It works and it gives me the right results but I wanted it not only to count the values inferior to sExpired but also the fields that are empty/null on that column.

Is This possible?

Thanks

 

[plog]

Add an OR to it to include your additional logic:

... OR IsNull(YourField)

 

[CJ_London]

try

ngultig = DCount("*", sTblName, nz(mFB,0) & " < " & sExpired)

or

ngultig = DCount("*", sTblName, mFB & " < " & sExpired OR isnull(mFB))

 

[cybersardinha]

try

ngultig = DCount("*", sTblName, nz(mFB,0) & " < " & sExpired)

or

ngultig = DCount("*", sTblName, mFB & " < " & sExpired OR isnull(mFB))

I've tried it, the first option just gives me the < sExpired count and the second one stops the code and doesn't give any output.

 

[CJ_London]

I just modified the code you say works. If the first one is giving you the same result, implies there are no nulls.

the second one, I've probably missed out an & somewhere

in both cases since mFB is not surrounded with quotes, it will be a vba value, not one in the table. I would have expected

"[mFB] < " & sExpired

or for my first example

"nz([mFB]) < " & sExpired

same goes for sExpired

 

[cybersardinha]

yes both are VBA values. the mFB contains the table name and the sExpired contains a date that is calculated by subtracting a number of months to the current date.

 

[CJ_London]

So how do you think dcount works? Lets call your table 'tableX' and you are subtracting 6 months from todays date.

Based on what you are saying then your criteria is

'tableX < 2022-01-07'

just don't see how that works