MS Access, Calculated Date Field

Benjo_2

Hi All

This may be to specialized for this forum but I was hoping that someone may be able to help or even point me in the right direction of a forum that would be able to assist

I am developing an application in Access that is using calculated date fields. I have placed the code below in the "control Source" of a control

=IIf(IsNull([StartDate]),DateSerial(Year([Text26])+[Text21],Month([Text26]),Day([Text26])),DateSerial(Year([StartDate])+[Text21],Month([StartDate]),Day([StartDate])))

The code in itself works fine, however, if for any reason, [Text26] or [Text21] are Null I get a response displaying #ERROR. I would like it just to remain blank.

I cant for the life of me figure out how to get around this in this piece of code,

Hope someone can point me in the right direction

Ben

FreheitOverture

Hmm....It has been a while since I have done any Access programming so I am not entirely sure what I am talking about, however surely if you are trying to work out Year, Month or Day from a NULL field you are going to get an error. 0 is not a valid date in any format and so you cannot work out a year from it. You may need another IF statement saying that if the 26 or 21 are Null then leave the date empty.

Benjo_2

Hi there

That is the problem. I have another calculated field (see below) which rightly sets the value to "" if a control is blank, but i do not know how to incorportate it in the statement above.


=IIf(IsNull([Text26]),"",DateSerial(Year([Text26])+[Text15],Month([Text26])+[TermM],Day([Text26]+[TermD])))

FreheitOverture

Can you not do a nested if statement?

So basically:
=IIf(IsNull([StartDate]),IIf(IsNull([Text26]),"",DateSerial(Year([Text26])+[Text15],Month([Text26])+[TermM],Day([Text26]+[TermD])))), DateSerial(Year([StartDate])+[Text21],Month([StartDate]),Day([StartDate])))

Benjo_2

No I tried it and got a message saying,

"The expression you entered contains invalid syntax, or you need to enclose your text data in quotes"

Any other ideas?

FreheitOverture

Erm....unfortunately no I'm all out of ideas

Benjo_2

Cheers for a good effort anyway

anyone else?

PhilCo_2

Put some ifs at the beginning to make it blank if either Text21 or 26 are blank.

=IIf(IsNull([text21]),"",IIf(IsNull([text26]),"",IIf(IsNull([StartDate]),DateSerial(Year([Text26])+[Text21],Month([Text26]),Day([Text26])),DateSerial(Year([StartDate])+[Text21],Month([StartDate]),Day([StartDate])))))

Remember to add the brackets at the end.

fergual2

=Iif(IsNull([StartDate]) and [Text26]>0 and [Text21]>0,DateSerial(Year([Text26])+[Text21],Month([Text26]),Day([Text26])),Iif([Text21]>0,DateSerial(Year([StartDate])+[Text21],Month([StartDate]),Day([StartDate])),Null))

seems to work here for me
Cheers
Al

Benjo_2

PhilCo that worked a treat thanks so much.

I cant believe it was really that easy, I have been looking at that for hours

Thank you eveybody for all your help.

Ben