Problems with a Null field in Access query 1

[brd24gor]

I am having a problem getting the DCount function in VBA to recognize when a cell is null. In my query I have a column called STATUSDef that will either contain a 'D' or be null. Here is the code I am using to do this:

'tlh-def
.Cells(13, curColumn) = DCount("STATUSDef", "qryDailyTest", "STATUSDef = 'D'" & _
"and [DATE] = #" & myDate & "# and [SOURCE] = 'MAIN'")
          
'tlh-acc
.Cells(14, curColumn) = DCount("STATUSDef", "qryDailyTest", "[STATUSDef] <> 'D' " & _
"and [DATE] = #" & myDate & "# and [SOURCE] = 'MAIN'")

The section I am having problems with is tlh-acc. In place of [STATUSDef] <> 'D' I have also tried:
IsNull(STATUSDef)
[STATUSDef] = ''

The first one is working just fine. What do you guys know that I don't?

 

[PHV]

Replace this:
"[STATUSDef] <> 'D' "
By this:
"[STATUSDef] Is Null "

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[brd24gor]

No luck. I'm still returning 0 when I should be getting 19. Any other suggestions?

 

[PHV]

OOps, missed an important thing, replace this:
DCount("STATUSDef", "qryDailyTest", "[STATUSDef] <> 'D' "
By this:
DCount("*", "qryDailyTest", "[STATUSDef] Is Null "

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TonyJollans]

Hi brd24gor,

A field being NULL means that it DOES NOT EXIST.

Your selection of rows will work fine provided you explicitly check for null (you can use either "IsNull(STATUSDef)" or "STATUSDef Is Null") - but counting things which don't exist is difficult - so difficult that Access won't do it

If you count the rows rather than the non-existent fields you should get the right answer ...

[blue].Cells(14, curColumn) = DCount("[red]*[/red]", "qryDailyTest", "[STATUSDef] [red]Is Null[/red] " & _
"and [DATE] = #" & myDate & "# and [SOURCE] = 'MAIN'")[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[TonyJollans]

PH - you beat me to it - again!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[brd24gor]

Thanks, guys. It's working like it should now. I am just curious as to why the first STATUSDef needs to be replaced by an asterisk.

Thanks again,
Brad

 

[PHV]

With the cursor inside the DCount word in your code, press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[brd24gor]

Ahh, excellent. Thank you very much!

 

[TonyJollans]

Hi Brad,

Sorry - it seems I wasn't clear in my explanation. The asterisk forces the returned ROWS to be counted rather than the number of (non-existent) STATUSDef fields.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[