convert Excel formula to Access query expression (1 Viewer)

bowgieman

New member
Local time
Today, 12:11
Joined
Jan 15, 2013
Messages
1
Hi,

I hope someone can help me. I have a formula that I have created in Excel, and I am trying to use the same method to create an expression in my Access query, but I am not getting the same result.

My excel formula is:
=IF(D2<>"",D2-C2,IF(AND(C2<>"",MAX(A2-C2,0)=0),B2-C2,B2-A2))

And this is my attempt for the access query expression:
IIf(IsNull([D]),IIf(Sgn([A]-[C])=-1,-[A],
-[C]),[D]-[C])

Basically all four cells/fields contain a date in dd/mm/yyyy format.
I want to find out the number of days between two dates if they meet the condition in the Excel formula.

I would be grateful for some help.

Many thanks
 

Attachments

  • excelformula.xls
    16.5 KB · Views: 211

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:11
Joined
Feb 19, 2002
Messages
42,970
It would be better to use actual names for your columns rather than letters. But in any event the first part of the expression is backwards and should be - IIf(NOT IsNull([D]),

I don't know what the AND function does so I don't know what to do with that. And I'm going to guess that the Max() function returns the highest value from a range of cells which means it has no equivalent in VBA since there is no concept of cells. You would need to use IIf()s to determine which date was greater.
 

Users who are viewing this thread

Top Bottom