CODEWITHSUNDEEP
sqlms-accessvbanull
graidan
graidan

Reputation: 151

The Infamous 'Invalid USe of Null' in a VBA query

Code first:

    bncSql = "UPDATE mrTool " & _
        " SET mrTool.Action = 'Y', mrTool.TQA = FALSE," & _
        " mrTool.OPID = 'SYS', mrTool.ReasonCode = 'AOK'," & _
        " mrTool.History = 'AOK'" & _
        " WHERE (Nz(CCur([Amount]),0) + Nz(CCur([SFS]),0) <=  0" & _
        " AND mrTool.Action Is Null);"
    DoCmd.RunSQL bncSql

mrTool.Action is defined as Text(1) (i.e. a single character), which is where I assume it is. The "Is Null" seems to work everywhere else - when I replace it with "=''" (the zero-length string), thinking maybe it's because it's Text not Variant, it stops working altogether.

I should add that I have the same format elsewehere and it seems to be working fine - it doesn't give me a runtime error in the 23 other places before this that I use it. because of that, I thought maybe it was an error in SFS and Amount, hence the Nz(), but that doesn't help any - Still get the error.

I'm at a loss...

Upvotes: 2

Views: 1343

Answers (1)

Steve
Steve

Reputation: 216263

maybe?

WHERE (CCur(Nz([Amount],0)) + CCur(Nz([SFS],0)) <=  0

Upvotes: 2

Related Questions