Calculate until End of Financial year

[NoRegrets_jackhammer2485]

Does anybody have the faintest idea how i can achieve this????

How can i calculate the number of months between a given date =Now() and the
31 March, and use this to make further calculations


for example

i have a table of work requests that have been submitted.called [tblWork]

fields that hold the relevent data are within this table:


[Date completed], with the format property set to Date/Time

[One off charge], with the format property set to Currency

[Monthly Charge], with the format property set to Currency

and [total charge] with the format property set to Currency

the one off charge and monthly charge will be entered i would the like the
calulation to work out the total charge.

I.e.

I submit a work request which is completed on 27 Feb 06.
the monthly charge for the work is $100

the one off charge is $20

so i need to:

a. calculate the number of months (either whole or part of) ie 2 (Feb and
Mar)
b. Multiply this number by the monthly cost ie $100*2 =$200
c. add to this the one off cost. $200+$20
d. display the answer in the [total charge field] total charge $220

Apologies for this layout i hope ive explained it well enough,

 

[PC Datasheet]

Look at the DateDiff function in the Help file. It will give you the number
of months between two dates.

 

[Guest]

Firstly you need to establish whether the Date Completed is before or after
31 March so as to determine whether to compute the number of months up to 31
March this year or next year. The IIF function can be used to do this. Then
it’s a simple task of computing the TotalCharge, As this can be computed
from the other data you don't need to have this column in the table, and
should not do so. Its redundant and redundancy leaves the door open to
update anomalies. So a query to compute the total charges for each Date
Completed would be:

SELECT
[Date Completed],
(IIF(MONTH([Date Completed])<4,4-MONTH([Date Completed]),16-MONTH([Date
Completed])) * [Monthly Charge]) + [One Off Charge] AS TotalCharge
FROM tblWork;

Ken Sheridan
Stafford, England

 

[Guest]

If you are working with dates, no time involved, do not use Now() but use
Date() as it has no time increment included.

 

[StopThisAdvertising]

"PC Datasheet" <[email protected]> schreef in bericht
--

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications 'Resource ????
Over 1125 users have come to me from the newsgroups requesting help '1125 users ????
(e-mail address removed)

--
To Steve:
Over 415 users from the newsgroups have visited the website to read what kind of a 'resource' you are... (rapidly increasing..)

To the original poster:

Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R

 

[NoRegrets_jackhammer2485]

Thanks for the IIF function however can you please clarify something for me?

(IIF(MONTH([Date_Completed])>4,4-MONTH([Date_Completed]),16-MONTH([Date_Completed]))*[Cost_Monthly]+[One_Off_Cost]
AS Total_Charge
FROM tblChargeable_RFCs;

Ive put the following code into the criteria property of my select querey
using the code builder however can you tell me what the syntax error is
here?? Access says its with the AS however as i'm very new to this i dont
totally understand

After a bit of reserach I see this expression as:

IIf(expr, truepart, falsepart)*[Cost_Monthly]+[Cost] AS Total_Cost FROM
tblchargeable_RFCs;

so
expr=(IIF(MONTH([Date_Completed])<4, --------------------if the month
value of the date completed field is less than 4
TruePart= 4-MONTH([Date_Completed]), --------------------Calculate the
difference upto the 4 MONTH
FalsePart=16-MONTH([Date_Completed])) ---------------------If the value is
Greater than the 4 month in the [Date completed] calulate the
---------------------difference
upto the 16 month from the 4 month (ie march the following year)
*[Cost_Monthly]+[One_Off_Cost]
-----------------------Multiply the value given by the [Cost Monthly] and
Add the [One Off Cost]
AS Total_Charge FROM tblChargeable_RFCs -------I DONT Understand this bit at
all what does the AS Total_Charge portion do, I think the

FROM
tbleChargeable_RFCs is the link back to the original table,

And lastly if the information is not to be stored in the table column (ie
because its redundant data how do i get the answer??

Thank you for your help