#Error in date field

Hi everyone,

I would be grateful if someone could tell me how to handle errors in
queries. The problem is that I am using DateAdd function to add up a number
to the date so for eg:
DateCleared = 12/01/2004
Length_of_Level = 2
So i have used the following:
Date Renewal: DateAdd("yyyy",[Length_of_ Level],[DateCleared])-1
The formula adds up the date and substracts one day...so the final result
would be 11/01/2006 and it works fine.
But if either the dateCleared or Length_of_Level is missing it results in
#Error in the field but I want it to be blank and not say anything in the
field if such information is missing.
Please bear in mind that I don't want to use VBA code as i'm still a
beginner and not too good with VB code and therefore appreciate it if you can
help me with it in Query itself.
Thanks in advance

Try something like:
Date Renewal: IIf(IsNull([Length_of_ Level]+[DateCleared]), Null,
DateAdd("yyyy",[Length_of_ Level],[DateCleared])-1)


--
Duane Hookom
MS Access MVP


"Kushum" wrote in message
...
Hi everyone,

I would be grateful if someone could tell me how to handle errors in
queries. The problem is that I am using DateAdd function to add up a
number
to the date so for eg:
DateCleared = 12/01/2004
Length_of_Level = 2
So i have used the following:
Date Renewal: DateAdd("yyyy",[Length_of_ Level],[DateCleared])-1
The formula adds up the date and substracts one day...so the final result
would be 11/01/2006 and it works fine.
But if either the dateCleared or Length_of_Level is missing it results in
#Error in the field but I want it to be blank and not say anything in the
field if such information is missing.
Please bear in mind that I don't want to use VBA code as i'm still a
beginner and not too good with VB code and therefore appreciate it if you
can
help me with it in Query itself.
Thanks in advance

Hi Duane,
Thankyou so much for your help I appreciate it...it works fine..

"Duane Hookom" wrote:

Try something like:
Date Renewal: IIf(IsNull([Length_of_ Level]+[DateCleared]), Null,
DateAdd("yyyy",[Length_of_ Level],[DateCleared])-1)


--
Duane Hookom
MS Access MVP


"Kushum" wrote in message
...
Hi everyone,

I would be grateful if someone could tell me how to handle errors in
queries. The problem is that I am using DateAdd function to add up a
number
to the date so for eg:
DateCleared = 12/01/2004
Length_of_Level = 2
So i have used the following:
Date Renewal: DateAdd("yyyy",[Length_of_ Level],[DateCleared])-1
The formula adds up the date and substracts one day...so the final result
would be 11/01/2006 and it works fine.
But if either the dateCleared or Length_of_Level is missing it results in
#Error in the field but I want it to be blank and not say anything in the
field if such information is missing.
Please bear in mind that I don't want to use VBA code as i'm still a
beginner and not too good with VB code and therefore appreciate it if you
can
help me with it in Query itself.
Thanks in advance