Iif and (1 Viewer)

paulcherianc · Jul 20, 2017

Can any one help me to fix the formula

Monthly Cost: IIf(AND([FromDate]<[LeaseStartDate]<[ToDate]),([ToDate]-[LeaseStartDate])*[MonthlyRent]*12/364,IIf((AND([FromDate]<[LeaseStartDate]<[ToDate]),([LeaseEndDate]-[FromDate])*[MonthlyRent]*12/364,[MonthlyRent])):banghead:

paulcherianc · Jul 20, 2017

I was trying to transform the excel formula to access.

=IF(AND($B$13<Q21,Q21<$D$13),($D$13-Q21)*S21*12/364,IF(AND($B$13<R21,R21<$D$13),(R21-$B$13)*S21*12/364,S21))

isladogs · Jul 20, 2017

Try this:

Code:

Monthly Cost: IIf(([FromDate]<[LeaseStartDate] And [LeaseStartDate]<[ToDate]),([ToDate]-[LeaseStartDate])*[MonthlyRent]*12/364,[MonthlyRent])

paulcherianc · Jul 20, 2017

Thanks for your Quick help on this!

However, date diff calculation went wrong! I think its the issue with "*12/364". Any workaround for this?

isladogs · Jul 20, 2017

Don't think 12/364 is an issue.
More likely to be caused by date formatting.

1. What are the datatypes for your date fields - dates or text?
2. What is your default date format e.g. dd/mm/yyyy as in UK?

Suggest you also post a few example records together with expected answers

paulcherianc · Jul 20, 2017

Yes for better understanding please find attached the excel sheet where the figure highlighted in red showing 7714.00 but as per the formula you provided the amount is showing 13000.00

1. Data Type is : Date
2. dd/mm/yyyy

paulcherianc · Jul 20, 2017

I tried Abs((DateDiff("d",([ToDate],[LeaseStartDate])*12/364*[MonthlyRent]) but even then its not giving the right figure.

paulcherianc · Jul 20, 2017

Can anyone help me to resolve this please!:banghead:

isladogs · Jul 20, 2017

It's a bit trickier than expected but I'm almost there

However, I'm having some difficulties understanding why some of the monthly costs are as shown on the Excel spreadsheet.

For example:
Rows 11/13/17/21 : lease already ended so why is there a monthly cost?
Row 20: lease ends on 26 Jun so why are they charged the full month?

Row 19: (shown in RED) - I can see why this is a part month only so will adjust the formula accordingly

Will get back to you once you've clarified the above

isladogs · Jul 20, 2017

OK I've done it - that was more complicated than your initial question indicated.

I agree with all except one of your calculations in the Excel sheet
Row 20 should I believe be £4,945.05

In the end I've done this as 2 queries
a) qryPartMonth determines whether the full month's rent is due
b) qryMonthlyCost calculates the rent due depending on the result of the previous query

SQL for these is as follows:
qryPartMonth:

Code:

SELECT [Lease Cost].SerialNumber, [Lease Cost].LeaseStartDate, [Lease Cost].LeaseEndDate, [Lease Cost].MonthlyRent, #6/1/2017# AS FromDate, #6/30/2017# AS ToDate, IIf(CLng([FromDate])<CLng([LeaseStartDate]) Or CLng([LeaseEndDate])>CLng([FromDate]) And CLng([LeaseEndDate])<CLng([ToDate]),"Y","") AS PartMonth, IIf(CLng([LeaseStartDate])>=CLng([FromDate]),[LeaseStartDate],[FromDate]) AS EffStartDate, IIf(CLng([LeaseEndDate])<=CLng([ToDate]),[LeaseEndDate],[ToDate]) AS EffEndDate
FROM [Lease Cost]
WHERE ((([Lease Cost].SerialNumber) Is Not Null) AND (([Lease Cost].LeaseStartDate) Is Not Null));

qryMonthlyCost:

Code:

SELECT qryPartMonth.SerialNumber, qryPartMonth.FromDate, qryPartMonth.ToDate, qryPartMonth.LeaseStartDate, qryPartMonth.LeaseEndDate, qryPartMonth.MonthlyRent, qryPartMonth.PartMonth, qryPartMonth.EffStartDate, qryPartMonth.EffEndDate, IIf([PartMonth]="Y",DateDiff("d",[EffStartDate],[EffEndDate])*12*[MonthlyRent]/364,[MonthlyRent]) AS [Monthly Cost]
FROM qryPartMonth;

See attached for db & your Excel file where I've highlighted row 20 in YELLOW

I'll leave you to change this to a function ...

There may well be other more elegant solutions than mine but I've spent too long on this as it is!

paulcherianc · Jul 20, 2017

Oh Perfect!!! Thanks a ton dear!!!

isladogs · Jul 20, 2017

You're welcome blossom!!

paulcherianc · Nov 27, 2017

Hi ,

How can I add 2 days to the date difference. Can you please help me.

Monthly Cost: IIf([PartMonth]="Y",DateDiff("d",[EffStartDate],[EffEndDate])*12*[MonthlyRent]/365,[MonthlyRent])

arnelgp · Nov 27, 2017

on this part you add:

Monthly Cost: IIf([PartMonth]="Y, (DateDiff("d",[EffStartDate],[EffEndDate]) + 2)*12*[MonthlyRent]/365,[MonthlyRent])

paulcherianc · Nov 27, 2017

Dear Arnel,

Thanks. But I don't think I could resolve my issue with my requirement. Can you please help me to resolve this. I will attach the result I want in excel and the access query also.

Monthly cost in excel should match with the same cost in access query "Lease Cost".

Thanks in advance!

paulcherianc · Nov 27, 2017

arnelgp said:
on this part you add:

Monthly Cost: IIf([PartMonth]="Y, (DateDiff("d",[EffStartDate],[EffEndDate]) + 2)*12*[MonthlyRent]/365,[MonthlyRent])

Dear Arnel,

I tried with this. But couldn't fix my issue.

Iif and (1 Viewer)

paulcherianc

Registered User.

paulcherianc

Registered User.

isladogs

MVP / VIP

paulcherianc

Registered User.

isladogs

MVP / VIP

paulcherianc

Registered User.

Attachments

paulcherianc

Registered User.

paulcherianc

Registered User.

isladogs

MVP / VIP

isladogs

MVP / VIP

Attachments

paulcherianc

Registered User.

isladogs

MVP / VIP

paulcherianc

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

paulcherianc

Registered User.

Attachments

paulcherianc

Registered User.

Similar threads

Users who are viewing this thread