Error when field is empty

[hjgoldstein]

Hi guys.

Simple situation, I am sure simple solution too.

I have a table with a number of fields in it.

One of the fields is "CustLastAudit" and is a date field, another is "CustAudFreq" and is a Long Integer referring to a number of months.

A further field, "CustNextAudDueDate" which should be calculated from the previous 2. That is to say, add the number of months in "CustAudFreq" to the date in "CustLastAudit" to get the desired result.

The associated form has the first two fields in bound controls. I have a text box with the following as Control Source:

=DateSerial(Year([CustLastAudit]);Month([CustLastAudit])+([CustAudFreq]);Day([CustLastAudit]))

All is fine except when "CustLastAudit" is empty.

I have tried various permutations of IIf and NZ but with no joy.

Have you any ideas what I can put in the Control Source to trap the error?

Hope you can help.

Thanks.

Howard

It is time for pacifists to stand up and fight for their beliefs.

 

[Golom]

You state that [CustNextAudDueDate] is a computed field, presumably in the same record as [CustLastAudit] and [CustAudFreq]. Be aware that storing calculated values is not usually a good idea. You should be presenting such computed values in a query and not stored as a permanent field in a table.

= IIF(IsNull([CustLastAudit]), NULL , 
DateSerial(Year([CustLastAudit])[red],[/red]Month([CustLastAudit])+[CustAudFreq][red],[/red]Day([CustLastAudit]))

Argument seperators in DateSerial are commas, not semicolons.

 

[hjgoldstein]

Thanks Golom. A perfect solution to my immediate problem.

I understand your concerns re storing computed fields and will try to address this.

Regarding the semicolons, I do appreciate your comment, but I need to use these rather than commas as my application is based on a server with a Belgian format. I only found this out by trial and error.

Your help is appreciated.



It is time for pacifists to stand up and fight for their beliefs.

 

[PHV]

Perhaps this ?

=DateAdd("m";Nz([CustAudFreq];0);[CustLastAudit])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hjgoldstein]

Thanks, PH

Works just as well with less keystrokes!

It is time for pacifists to stand up and fight for their beliefs.