Query #error

[Aoife]

Hiya, been attempting to avoid #error values in a query when [expirytype] (a numeric field),[expiresmonths] (numeric) and [completed] (date) are blank

Step1: IIf([expirytype]=2 And (DateAdd("m",[expiresmonths],[completed])>Now()),(DateAdd("m",[expiresmonths],[completed])))

My research has revealed that iserror doesn't work in a query, suspect nz function may provide solution but would appreciate pointers in it's use

 

[plog]

NZ (http://www.techonthenet.com/access/functions/advanced/nz.php) allows you to test a variable for a null value and replace that null value with a different one. So instead of [expiresmonth] and [compeleted] you would have them each inside thier own Nz function.

The trick is deciding what values you want when they are null. Should the thing just fail and produce a blank, or do you want an actual value to be returned?

 

[Aoife]

Thanks for the reply. In order for my head to get around this I decided to separate out the calculations.

ExpiresMonthsNZ: Nz([ExpiresMonths])
ExpiryTypeNZ: Nz([expirytype])
CompletedNZ: Nz([completed])

Then I decided to strip back my original calculation to my dateAdd expression:

DateAdd("m",[expiresmonthsnz],[completednz])

This also resulted in the dreaded #error and therein probably lies the issue.

I then tried
ExpiresMonthsNZ: Nz([ExpiresMonths],0) for each of the 3 NZ caculations above but the DateAdd threw out a date in the 20th century.

How can I get the dateadd to return a blank?

 

[plog]

DateAdd can't return a blank, it returns a date or #error. The issue you are having now is that CompletedNz might contain 0. Then when you go to use it in Date it screws it up because it expects a date and it has a zero.

You need to think about what should the final result be when any of the fields are null. What should be the final result when [completed] is null, what about [expiresMonths]?

Expiry type doesn't need to go through the NZ because any value other than 2 stops it right there. It can be null all day long.