Stuck with an #Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been messing with the IIf(isError Formula to try to eliminate errors
from my database.
FinalTest: IIf(IsError([Age]),"",[Age])

[Age] is returned from another cell, which calculates the person's age. It
works well, except when the patient's date of birth field is empty, when it
returns an error, (Unsurprisingly). I thought that the above code would
return [Age] unless the field contained #Error, where it would return a blank.

Have I got the syntax wrong, or have I completely misunderstood the use of
IsError?

Thanks for any advice, these discussion groups are by far and away the best,
most useful resource I have found on the web in a long time,
Thanks in advance for any help, and thanks for contributing- I have had a
lot of help here in the last couple of weeks.

Ian
 
You're probably better off checking whether or not there's a date of birth
value.

Assuming that date of birth is a Date field, being "empty" really means that
it's Null:

FinalTest: IIf(IsNull([Date Of Birth]),"",[Age])

In fact, you're probably better off doing that check when you calculate Age,
rather than having another field.

And yes, you probably are misinterpretting what IsError does.

It's intended to check if the argument is a Variant of VarType vbError,
where you set the variant using the CVErr function. While the KB article
only mentions Excel, the section about the CVErr function in
http://support.microsoft.com/?id=146864 does a fairly good job of
illustrating the use of IsError.
 
Ian said:
I have been messing with the IIf(isError Formula to try to eliminate errors
from my database.
FinalTest: IIf(IsError([Age]),"",[Age])

[Age] is returned from another cell, which calculates the person's age. It
works well, except when the patient's date of birth field is empty, when it
returns an error, (Unsurprisingly). I thought that the above code would
return [Age] unless the field contained #Error, where it would return a blank.

Have I got the syntax wrong, or have I completely misunderstood the use of
IsError?

Thanks for any advice, these discussion groups are by far and away the best,
most useful resource I have found on the web in a long time,
Thanks in advance for any help, and thanks for contributing- I have had a
lot of help here in the last couple of weeks.

Ian
Click to expand...


By database, do you mean an Access database or an Excel worksheet? I ask
because you said "> [Age] is returned from another cell" which implies Excel.

This is a forum for MS Access database, not Excel. You might want to post the
question in an Excel forum if you are using Excel.


In any case, Access does not have an "ISERROR()" function.
Excel has "ISERROR()" and also "ISERR()" functions; see Excel Help.


Looking at the line:
FinalTest: IIf(IsError([Age]),"",[Age])
Click to expand...

it doesn't do what you want it to because, [Age] is not an error, even if it is
Null. So the IIF() function would always return what is in the age field (Null
or the age), *IF* Access had an "ISERROR()" function.


You could try (in Access):

FinalTest: IIf(IsDate([BirthDate]),[Age],"")

or maybe:

FinalTest: IIf(Trim(Nz([BirthDate],""))<>"",[Age],"")


HTH
 
By database, do you mean an Access database or an Excel worksheet? I ask
because you said "> [Age] is returned from another cell" which implies
Excel.

This is a forum for MS Access database, not Excel. You might want to
post the question in an Excel forum if you are using Excel.


In any case, Access does not have an "ISERROR()" function.
Excel has "ISERROR()" and also "ISERR()" functions; see Excel Help.


Looking at the line:
FinalTest: IIf(IsError([Age]),"",[Age])
Click to expand...

it doesn't do what you want it to because, [Age] is not an error, even
if it is Null. So the IIF() function would always return what is in the
age field (Null or the age), *IF* Access had an "ISERROR()" function.


You could try (in Access):

FinalTest: IIf(IsDate([BirthDate]),[Age],"")

or maybe:

FinalTest: IIf(Trim(Nz([BirthDate],""))<>"",[Age],"")


HTH
Click to expand...

Didn't search hard enough....

I was using Help, typing in ISERROR; I didn't find it in the Index search, but
the Object Browser shows it is a member of VBA.Information.

IsError Function : .....

The IsError function is used to determine if a numeric expression represents an
error. IsError returns True if the expression argument indicates an error;
otherwise, it returns False.

So it still holds true that IsError([Age]) returns False if birthdate is null
because Null is not an error.
 
try using the nz() function
-----------------------------------------------logic
if [date of birth] is null then return the value -1
if the value returned = -1 then FinalTest is an empty string ""
else FinalTest the [age]
----------------------------------------------------
FinalTest: IIF(nz([date of Birth],-1)=-1,"",[Age])
or

FinalTest: IIf([Date Of Birth] is null,"",[Age])

Ed Warren

Douglas J. Steele said:
You're probably better off checking whether or not there's a date of birth
value.

Assuming that date of birth is a Date field, being "empty" really means
that it's Null:

FinalTest: IIf(IsNull([Date Of Birth]),"",[Age])

In fact, you're probably better off doing that check when you calculate
Age, rather than having another field.

And yes, you probably are misinterpretting what IsError does.

It's intended to check if the argument is a Variant of VarType vbError,
where you set the variant using the CVErr function. While the KB article
only mentions Excel, the section about the CVErr function in
http://support.microsoft.com/?id=146864 does a fairly good job of
illustrating the use of IsError.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ian McLeish said:
I have been messing with the IIf(isError Formula to try to eliminate
errors
from my database.
FinalTest: IIf(IsError([Age]),"",[Age])

[Age] is returned from another cell, which calculates the person's age.
It
works well, except when the patient's date of birth field is empty, when
it
returns an error, (Unsurprisingly). I thought that the above code would
return [Age] unless the field contained #Error, where it would return a
blank.

Have I got the syntax wrong, or have I completely misunderstood the use
of
IsError?

Thanks for any advice, these discussion groups are by far and away the
best,
most useful resource I have found on the web in a long time,
Thanks in advance for any help, and thanks for contributing- I have had a
lot of help here in the last couple of weeks.

Ian
Click to expand...
Click to expand...
 
Depending on the nature of the database, it's possible someone could have a
legitimate date of birth of -1. That's how Access stores the date 29 Dec,
1899.

And you must use the IsNull function in the IIf statement, "is null" is for
SQL statements.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ed Warren said:
try using the nz() function
-----------------------------------------------logic
if [date of birth] is null then return the value -1
if the value returned = -1 then FinalTest is an empty string ""
else FinalTest the [age]
----------------------------------------------------
FinalTest: IIF(nz([date of Birth],-1)=-1,"",[Age])
or

FinalTest: IIf([Date Of Birth] is null,"",[Age])

Ed Warren

Douglas J. Steele said:
You're probably better off checking whether or not there's a date of
birth value.

Assuming that date of birth is a Date field, being "empty" really means
that it's Null:

FinalTest: IIf(IsNull([Date Of Birth]),"",[Age])

In fact, you're probably better off doing that check when you calculate
Age, rather than having another field.

And yes, you probably are misinterpretting what IsError does.

It's intended to check if the argument is a Variant of VarType vbError,
where you set the variant using the CVErr function. While the KB article
only mentions Excel, the section about the CVErr function in
http://support.microsoft.com/?id=146864 does a fairly good job of
illustrating the use of IsError.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ian McLeish said:
I have been messing with the IIf(isError Formula to try to eliminate
errors
from my database.
FinalTest: IIf(IsError([Age]),"",[Age])

[Age] is returned from another cell, which calculates the person's age.
It
works well, except when the patient's date of birth field is empty, when
it
returns an error, (Unsurprisingly). I thought that the above code would
return [Age] unless the field contained #Error, where it would return a
blank.

Have I got the syntax wrong, or have I completely misunderstood the use
of
IsError?

Thanks for any advice, these discussion groups are by far and away the
best,
most useful resource I have found on the web in a long time,
Thanks in advance for any help, and thanks for contributing- I have had
a
lot of help here in the last couple of weeks.

Ian
Click to expand...
Click to expand...
Click to expand...
 
I have found the way MsAccess handles NULL'sconfusing and appearing on the
surface inconsistent --(I know if one 'really' took the time to fully
understand all the variations they are in some logic universe consistent).
This is one of the major hurdles for the new user. How to handle nulls!!

you have the nz() function
you have IS NULL
you have ISNULL
if you declare a function with no typed return it will handle nulls.
If you declare a function with a typed return it throws an error on nulls.

All this time I have used IIF in SQL and IF--Then-Else in VBA code.

When I try to enter (in a query) ReturnVal:IIF(ISNULL(XXX),0,1) I get
compile errors in my SQL statements.

When I use ReturnVal:IIF([xxx] is null,0,1) it works.

You've got me for the 10 or so people still on earth with a date of birth of
29 Dec 1899. There are certainly better ways to trap this error anyway (as
you have so correctly stated do it in the age function. There you
could/should also handle the 'stupid data errors', like a birthdates in the
future, etc.)

Cheers,

Ed Warren.


Douglas J. Steele said:
Depending on the nature of the database, it's possible someone could have
a legitimate date of birth of -1. That's how Access stores the date 29
Dec, 1899.

And you must use the IsNull function in the IIf statement, "is null" is
for SQL statements.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ed Warren said:
try using the nz() function
-----------------------------------------------logic
if [date of birth] is null then return the value -1
if the value returned = -1 then FinalTest is an empty string ""
else FinalTest the [age]
----------------------------------------------------
FinalTest: IIF(nz([date of Birth],-1)=-1,"",[Age])
or

FinalTest: IIf([Date Of Birth] is null,"",[Age])

Ed Warren

Douglas J. Steele said:
You're probably better off checking whether or not there's a date of
birth value.

Assuming that date of birth is a Date field, being "empty" really means
that it's Null:

FinalTest: IIf(IsNull([Date Of Birth]),"",[Age])

In fact, you're probably better off doing that check when you calculate
Age, rather than having another field.

And yes, you probably are misinterpretting what IsError does.

It's intended to check if the argument is a Variant of VarType vbError,
where you set the variant using the CVErr function. While the KB article
only mentions Excel, the section about the CVErr function in
http://support.microsoft.com/?id=146864 does a fairly good job of
illustrating the use of IsError.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have been messing with the IIf(isError Formula to try to eliminate
errors
from my database.
FinalTest: IIf(IsError([Age]),"",[Age])

[Age] is returned from another cell, which calculates the person's age.
It
works well, except when the patient's date of birth field is empty,
when it
returns an error, (Unsurprisingly). I thought that the above code would
return [Age] unless the field contained #Error, where it would return a
blank.

Have I got the syntax wrong, or have I completely misunderstood the use
of
IsError?

Thanks for any advice, these discussion groups are by far and away the
best,
most useful resource I have found on the web in a long time,
Thanks in advance for any help, and thanks for contributing- I have had
a
lot of help here in the last couple of weeks.

Ian
Click to expand...
Click to expand...
Click to expand...
 
Back
Top