IIF function not working properly in my access database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

blue - 30 Sep 2006 15:48 GMT

I am trying to find out the delay in days occurred between two date
fields in an MS ACCESS 2003 Query.
ScheduledDateforProductApprovalbyOBI:IIf([ActualProductSubmittalDate]<([ScheduledDateToPlaceOrder]-10),(IIf(([ScheduledDateToPlaceOrder]-([ActualProductSubmittalDate]+15))<5,[ScheduledDateToPlaceOrder]-5,[ActualProductSubmittalDate]+15),[ActualProductSubmittalDate]+7)

this is the IIF function i have used to find the delay. Can anybody
advise me where is the mistake in this function.

Reply to this Message

Duane Hookom - 30 Sep 2006 17:11 GMT

I think you should create a small user-defined function rather than
attempting to create business calculations in an expression in a query. I
expect the numbers may change at some point in the future and you should
never have to go back to a query to make the changes.

You can open a new, blank module and begin by entering:

Public Function GetSDPABOBI(datAPSD as Date, datSDTPO as Date) As Date
If datAPSD<datSDTPO -10 Then
'other code
End If
End Function

Signature

Duane Hookom
MS Access MVP

>I am trying to find out the delay in days occurred between two date
> fields in an MS ACCESS 2003 Query.
> ScheduledDateforProductApprovalbyOBI:IIf([ActualProductSubmittalDate]<([ScheduledDateToPlaceOrder]-10),(IIf(([ScheduledDateToPlaceOrder]-([ActualProductSubmittalDate]+15))<5,[ScheduledDateToPlaceOrder]-5,[ActualProductSubmittalDate]+15),[ActualProductSubmittalDate]+7)
>
> this is the IIF function i have used to find the delay. Can anybody
> advise me where is the mistake in this function.

Reply to this Message