IF THEN statement???

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Russell Pascoe - 25 Sep 2007 12:10 GMT

Hi there, I really hope you can help me!

I have a database that has a bunch of names and dates, names of tenants,
dates that they moved in, and moved out, and then some other stuff.

I have produced a report that only has the names of tenants that were with
us during a particular two dates (financial year). I now want to get a little
more clever and calculate the rent they paid between their two dates. Where I
am struggling is that I want to do something along the lines of;

IF (Date Moved in) < 5th April 2006 THEN (Date Moved Out - 5th April 2006) *
Rent

I have to make an analysis of whether the tenant moved in before the
beginning of the tax year, because I only want the rent payments DURING that
tax year, so similarly I need to do something similar for the date they moved
out because that maybe in the following tax year, so I only need to calculate
their rent to the end of the 06/07 year.

The only way I can (possibly ignorantly) spot to do this is with an IF THEN
statement, but I cannot find that functionality in Access, could someone
please give me a suggestion or two, I would be most grateful - will make my
life a lot easier!

Thanks.
Russell.

Reply to this Message

Marshall Barton - 25 Sep 2007 14:53 GMT

>Hi there, I really hope you can help me!
>
[quoted text clipped - 19 lines]
>please give me a suggestion or two, I would be most grateful - will make my
>life a lot easier!

There is the IIf function for that kind of thing.

Try using these expressions in text boxes:

txtSdate
=IIf([Date Moved in] < #4/5/2006#. #4/5/2006#. [Date Moved
in])

rxrEdate
=IIf([Date Moved out] > #4/6/2007#. #4/6/2007#. [Date Moved
out])

txtRent2006
=DateDiff("d", Sdate, Edate) * (Rent / 30)

and if that's not sufficient, post back with explanations of
what's wrong with the results.

Not that the / 30 assumes that rent is prorated to partial
months, which is effectively a daily rent.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Russell Pascoe - 26 Sep 2007 13:34 GMT

Thank you!

Let me give that a go, first glance, I think that a few bits I don't
understand, but I'll work it through and then see!

Thanks again.
Russell.

> >Hi there, I really hope you can help me!
> >
[quoted text clipped - 40 lines]
> Not that the / 30 assumes that rent is prorated to partial
> months, which is effectively a daily rent.

Reply to this Message

Russell Pascoe - 19 Oct 2007 18:53 GMT

MArshall,

I am now on vacation, and have had proper time to work through this and
other issues. I have got a lot sorted, and understand the gist of your answer
- I don't know why I didn't see that solution sooner. But I would have done
it a different way, I would have tried nested IIF commands, which I presume
you haven't done because you are wiser than me. So my real question is now
about the variables you are creating: I presume txtSdate is a text variable,
I have no idea what a rxr is from rxrEdate, and then I guess I am displaying
the result within txtRent2006?

Can you guide me to where I can do a little research on these particular
points, and perhaps just explain a little of the logic you are using too.

Thank you in advance.
Russ.

> Thank you!
>
[quoted text clipped - 48 lines]
> > Not that the / 30 assumes that rent is prorated to partial
> > months, which is effectively a daily rent.

Reply to this Message