Date difference query based on excel formula

[harit26]

i want to write a ms access query for date difference.i have the excel formula for that.
in attachment i have shown wot i really want as output.
i want to make a IIf query for these conditions.



in excel i did this like:
=IF(J13="",IF(INT(B13)=INT(AE13),C13-DAY(I13)+1,C13),IF(INT(AA13)=INT(B13),IF(MONTH(J13 )=MONTH(I13),J13-I13+1,DAY(J13)),IF(INT(AA13)=INT(D13),DAY(J13)-E13,0)))

but i dont know how to do it in access query
please help

[harit26]

wot i have arrived so far is :
IIf(IsNull([EndDate]),IIf(Month([YYMM])=Month([StartDate]),([Current Month Days]-Day([StartDate])+1),[Current Month Days]),IIf(Month([EndDate])=Month([StartDate]),IIf(Month([EndDate])=Month([YYMM]),(Day([EndDate])-Day([StartDate])+1),Day([EndDate])),IIf(Month([EndDate])=Month([Previous YYMM]),Day([EndDate])-[Previous Month Days],0)))

but its not fulfilling last condition that gives -ve result.
how to correct it.
please help me out.

[ranman256]

If you have this many IF statements, then its time to move to a lookup table.
you build a table with 1 col having the lookup value, and the 2nd col. that has the value you want to replace with.

table: tStateLook
[state], [value]
NY, 4
CA, 45
WV, 99

here, your query would connect to tStateLook
joined to the main table on STATE, you would bring down the VALUE field. Where a state = NY, It automatically gives 4 as a value. Same with your IIF statment above but with NO long strings of IFs and no code.

[ranman256]

If you have this many IF statements, then its time to move to a lookup table.
you build a table with 1 col having the lookup value, and the 2nd col. that has the value you want to replace with.

table: tStateLook
[state], [value]
NY, 4
CA, 45
WV, 99

here, your query would connect to tStateLook
joined to the main table on STATE, you would bring down the VALUE field. Where a state = NY, It automatically gives 4 as a value. Same with your IIF statment above but with NO long strings of IFs and no code.

IF THIS STILL DONT FIT YOUR NEED, you can use a custom FUNCTION

function CalcMyVal(pvDate)

select case true
case pvDate< date()
CalcMyVal = "none"
case else
CalcMyVal = "found"
end select

[harit26]

thnx bro ....it did work....now i got wot i wanted as output