Excel Formula to Access Query - Time Between 2 Dates in Weeks

[Guest]

I have an excel formula that calculates the # of weeks between two date fields:
=IF(AND(A14="",B14=""),"",IF(AND(B14="",A14>0),(NOW()-A14)/7,IF(AND(A14>0,B14>0),IF(A14=B14,0.14,(B14-A14)/7))))
Where B=Date2 and A=Date1. Not all date fields have a value and sometimes
the two date fields are the same. Here are the possibilities and the desired
result:

Date1 Date2 Result
Null Null Null
x y (y-x)/7
x x 0.14
x Null (Today()-x)/7

So I want to convert my Excel formula to an Access Query using Access 2002.
I have done some simple IIf statements but get very mixed up if I try to
nest. Any suggestions on where to look for the structure of formulas in
Access? Excel makes it so easy with the If(logical_test,[true],[false])
guide there for you. Thanks!

 

[Guest]

Here is two ways to do it ---
SELECT Your_Table.Date1, Your_Table.Date2, DateDiff("w",[Date1],IIf([Date2]
Is Null,Date(),[Date2])) AS [Whole Weeks], DateDiff("d",[Date1],IIf([Date2]
Is Null,Date(),[Date2]))/7 AS [Fractional Weeks]
FROM Your_Table;

 

[Michel Walsh]

iif( Date2=Date1, 0.14, ( Nz(Date2, Date() ) - Date1 ) / 7 )



Hoping it may help,
Vanderghast, Access MVP