If statement help

[romeyp]

Here is my if then statement -

=IIf([SickBalance]>=240,240,[SickBalance])

What it currently does is determine how many sick hours a person has accrued. If it is greater than or equal to 240 hours it display the number 240 (this is the max amount you can accrue), if it is less than 240 hours then is show the amount you have accrued.
What I am really trying to do is set the max at 240, but my problem is each month you continue to accrue 4 hours of sick time, which in example if you had 240 hours two months ago you would have accrued 8 hours of sick giving you a total of 248.
Here is my problem- How do I set 240 as my max value and then subtract any sick time taken form that 240?
Would it work better to come up with a better if statement? Or is there a better way to set it up.
I know I'm not providing all of the information you may need, but this is the only way I can keep it short and sweet.

Thank you in advance

 

[hneal98]

Can you give a scenario of what you are wanting to do?

 

[romeyp]

OK I'll try to explain it.
I have created a database for tracking vacation and sick time for my company.
It tracks the sick time of each person in our company.
I take the data provide on each persons time sheet and create an excel spreadsheet with everyones information on it.
From there I dump it into two tables in my database
Accrued and Taken

I then simply add up all of the time they have accured for each vacation and sick, then I subtract what they have taken.
In the case of sick time you would accrue 4 hours of sick time each month until you reach the max amount of sick time at 240 hours.
What I have done with my if statement is told it to look and see if the total amount of sick time is greater than or equal to 240 hours if it is then display 240 as your total for sick hours available, if it is below 240 the display that amount which can be anything under 240.
The problem for me is I need to know how to go about either setting 240 as the max limit and then once you do finally take sick time it is subtracted from 240 to give you a balance below 240.

Here is what I have-
=IIf([SickBalance]>=240,240,[SickBalance])

Here is what I'm trying to get to but can not get to work

=IIf([SickBalance]>=240,240("if the display number is 240 and a person has taken sick time with in the past month subtract that amount from 240 and display that amount"),[SickBalance])

I know if is probably a simple answer but I have been trying to figure it out for the past two months.

Thanks for your help

 

[hneal98]

What I would do is subtract the sick time and add the Accrued. Then if the amount is over 240, then display 240.

Your formula is fine once you take care of the other two processes. Unfortunately, I don't think it would be good to do them all in one step. What you might consider doing is doing the the calculations in Excel first and then bring it into Access.

By the way, I am assuming since you are putting the data in Excel and then in Access, you are not very familiar with Access, am I correct?

 

[romeyp]

Actually I have my certification for Excel and Access, so I can get around in each of them pretty well.
The reason I use excel first is because the data that is collected from the timesheets comes from our accounting system called BST which is designed around Crystal Reports.
I have to take it over to Excel because once I run the report in BST it has a lot of extra lines, logos and what not.
I am currently doing as you suggested by subtracting the sick time then adding the accrued the problem is some people do not take sick time each month, but the continue to accrue 4 hours, so if you had accrued a total of 240 in April, then come May, and June you would accrue another 4 hours each month for a total of 248. If you have not taken any sick time during any of these months then my if statement would show your balance as being 240, but the problem is if you take 8 hours of sick in July you also gain another 4 which would put you at 244. What I am trying to do is to subtract your 8 hours of sick from 240 to give you a balance of 232.
I'm sure this is sounding like the ravings of a lunatic but I have been going around with this for the past couple of months.

Thank you for your replies.

 

[hneal98]

Ok, lets take a look at this:

I start with 240 hours. I accrue 4 and I take 8 hours sick time. That give me 236. Next month I don't take any sick time, but I accrue 4 hours. That gives me 240. The next month, again, I don't take any sick time, but accrue another 4. That would give me 244, but my max is 240, so based on your formula, I only have 240. Now, if I take 8 hours sick time and accrue 4 hours for the next month, it would be 240-8+4. Since we want the calculation to happen in a certain order we put () around the first part of the equation like this: (240-8)+4. That gives 236.

Is that what you are looking for? If you are less than 236, the order of operation does not matter, but once you get that magic number, you want to subtract the sick time first and then add the accrue, correct?

 

[romeyp]

Yes!!! That is exactly what I am trying to do, but I can not figure out the corret way to write the "IIf statement".
Any suggestions?

Thanks once again for your replies.

 

[hneal98]

Not a problem. As far as your IIF statement is concerned, it is fine. You just need to do this in several processes. Is the IIF statement on a form, or in a query?

 

[romeyp]

It is in a query.

 

[hneal98]

Ok and are you just running the query manually? or are you clicking a button to run it? What I am trying to get at is how is the Accrue being added? Is it manually, or is there an automated process in place?

 

[romeyp]

I am running the query manually, but I have it set up with others to run from a macros.
So I could do it either way.

 

[romeyp]

Let me give you a little more info.
This is the query which figures out the balance for sick time.

CorSickBal: [Employee Info]![Sick Balance]+[CorrectedTimeAccrued]![SumOfSick Accrued]-[CorrectedTakenBalance]![SumOfSick Taken]

The reason why it is called CorSickBal is because I began this entire database with data from an older tracking system and have been adding and subtracting all new data from that system.

Employee info - is a table which has each employee's beginning balances from the old system.

CorrectedTimeAccrued - Adds up all of the time that has been accrued for a perticular field.

CorrectedTakenBalance - Adds up all of the time that has been taken for a perticular field.

Here is an example - [Employee Info] 36 + [CorrectedTimeAccrued] 10 - [CorrectedTakenBalance] 8 = CorSickBal of 38.

From this query I have a text box on my report which draws CorSickBal which I then run my "IIf then Statement" against.

Does that help or make any sense?

Thanks.

 

[hneal98]

Well, it brings up another question. Your iff statement is in a textbox on your report?

The way I see it, you will need to run one query to do the addition as you stated above, the

 CorSickBal: [Employee Info]![Sick Balance]+[CorrectedTimeAccrued]![SumOfSick Accrued]-[CorrectedTakenBalance]![SumOfSick Taken]

then you need to go in and do your comparison. It seems like, from your explanation, you are already doing that. I am confused, but here is what I think you need to do:

create two queries and one table so you can test my results to make sure it is what you will need.

The table name will be Table2. It will have 5 fields. Total, Sick, Accrue, NewTotal (all four of these are Number datatype), and AccrueDate (Date/Time))

Here are the two queries:

Query1:

 UPDATE Table2 AS t1 SET t1.NewTotal = ([t1].[Total]-[t1].[Sick])+[t1].[Accrue]
WHERE (((Now())>=[AccrueDate]));

Query2:

 INSERT INTO Table2 ( Total, Sick, Accrue, AccrueDate )
SELECT IIf([NewTotal]>240,240,[NewTotal]) AS Total, 0 AS Sick, 4 AS Accrue, Now() AS Expr1
FROM Table2;

You can create these queries by clicking on New Query and then clicking on the SQL button and replacing the existing code with one the above codes and do the same for the other.

If all is well, you should be able to click on the design button and it should bring you back to the query grid.

Add a record to the table like the following:

Total Sick Accrue NewTotal AccrueDate
238 0 4 0 06/15/2004


Then run query1 and then query2 and note the results. Play around with the data to make sure it is doing what you want.

 

[romeyp]

I gave it a try, and you are on the right track.
The "SELECT IIf" is very close to what I am trying to do.
In reference to your previous question, yes my if statement is in text box on the report I create to print out to give to people to let them know how much sick and vacation time they have. Is there a way to do this without adding additional tables or queries?

I'll wrestle around with it a little more to see what I come up with.

Thanks

 

[romeyp]

Is it possible to write a nested if statement that would get the result that I am trying to get to?

 

[hneal98]

The table was only for example. You should take the queries and set them up to suit your needs. As for doing a nested IIF, yes it is possible, but you have to be careful in that you realize there are two parts to each iif in the nest. It would be set up like this:

 =iif(a=b, iif(b=c, a),b)

The logic example above, of course, makes little sense, but I think you can get the idea.