Full Version: Iif And Isnull Keeps Erroring
UtterAccess Forums > Microsoft® Access > Access Forms
alorenzini
I have a textbox on form with the following control source:
IIf(IsNull([txtMHours]),0,[txtMHours])+IIf(IsNull([txtTHours]),0,[txtTHours])+I
If(IsNull([txtWHours]),0,[txtWHours])+IIf(IsNull([txtThHours]),0,[txtThHours])+II
f(IsNull([txtFHours]),0,[txtFHours])+IIf(IsNull([txtSHours]),0,[txtSHours])+IIf(I
sNull([txtSuHours]),0,[txtSuHours])
But it keeps giving a Name? error.
The txtMHours, txtTHours, etc fields have the following control source:
=Round(([SuTimeOut]-[SuTimeIn])*24,2)
Any ideas?
Bob_L
How about just using:
z([txtMHours],0) + Nz([txtTHours],0) + Nz([txtWHours],0) + Nz([txtThHours],0) + Nz([txtFHours],0) + Nz([txtSHours],0) + Nz([txtSuHours],0)
Hopefully none of them have an empty string "" in them.
datAdrenaline
I would suggest that you change your expressions that feed the "total" expression to something like this:
IIf(([SuTimeOut] + [SuTimeIn]) Is Null, 0, Round(([SuTimeOut]-[SuTimeIn])*24,2))
What this will due is return a 0 if EITHER or BOTH of the controls [SuTimeOut] or [SuTimeIn] are Null, otherwise it returns the number of hours between the two times.
Then you totaling expression would be (since all the operands would be a value):
=[txtMHours]+[txtTHours]+[txtWHours]+[txtThHours]+[txtFHours]+[txtSHours]+[txt
SuHours]
The reason I prefer this type of technique is that it coerces the nulls as early as possible, which I personally find helps prevent errors in my expressions.
-------------
Aside from all this, it does seem as though your table structure is not designed in an optimal fashion and if you wish to expand this db and have a longer period of usership, then I would suggest that you search through UA on articles involving Normalization.
alorenzini
I will take your advice about the normalization. This is works:
IIf(IsNull([txtMHours]),0,[txtMHours])+IIf(IsNull([txtTHours]),0,[txtTHours])+I
If(IsNull([txtWHours]),0,[txtWHours])+IIf(IsNull([txtThHours]),0,[txtThHours])+II
f(IsNull([txtFHours]),0,[txtFHours])+IIf(IsNull([txtSHours]),0,[txtSHours])+IIf(I
sNull([txtSuHours]),0,[txtSuHours])
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.