Converting Excel Formula to Access Query Format

[Guy Winfield]

Hello everyone,

I've been having problems with a formula that i have in Excel that i wish to convert into access in order to play around with my database.
Can somebody please tell me where i'm going wrong ?


OTD Réel: VraiFaux(Ou([011- Extraction FA ENSEMBLE]![OTD Prév]=1;Et([livraisons]![Dt BL]<=[livraisons]![Dt Prom];[livraisons]![Qté facturée]=[livraisons]![Qté cde];"1")))VraiFaux([livraisons]![Dt BL]<=[livraisons]![Dt Prom]; [livraisons]![Qté facturée]/[livraisons]![Qté cde];"0")

=SI(OU(D3=1;ET(B3<=C3;E3=F3));1;SI(B3<=C3;E3/F3;0))*

B-->Date BL
C-->Date Promise
D--> OTD Prev
E-->Qté facturée


F-->Qté cmdé

Also, as you may see i'm using a french version of microsoft access, this does not the change the usage of these words in English: Below you'll find the meaning for each french word:

Si-->If
VraiFaux-->Iff()
Et--> And
Ou--> Or

As long as i can see a working expression in English i shall be able to change it to french.

Kind regards !

[ranman256]

If you have a long formula with nested IIFs, try using a Formula for easier reading and debugging...
Then in the query, you:

Reply: getMyRate([bidate],[promise date], [otd], [fract], [cmd])

public function getMyRate(pBlDate, pPromDate,pOTD, pFract,pCmd)
Dim ans

select case true
Case pBlDate < date()
   Ans = "answer1"
pcase pPromDate > date()
    Ans = "answer 2"

end select

getMyRate = ans
end function

[CJ_London]

translating this excel to sql

=SI(OU(D3=1;ET(B3<=C3;E3=F3));1;SI(B3<=C3;E3/F3;0))*

would be

=IIF(d3=1 OR (b3<=C3 AND e3=f3),1,IIF(B3<=C3,e3/F3,0))