UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Help With A Formula Thinking An If Statment, Access 2010    
 
   
wornout
post Nov 6 2018, 10:56 PM
Post#1



Posts: 1,218
Joined: 17-November 13
From: Orewa New Zealand


I am not sure the best way to go about this be it a query a textbox on the form at the moment I am trying to work it out in a textbox on a form but happy for it to change because I dont know what I am doing and you smart people do

I have a form "LEAVE" on the form I have
Textbox "Accuredhours" bound to a table "Leaveandsick"
textbox "Outstandinghours" bound to a table "Leaveandsick"
Textbox "AdvanceHours" bound to a table "Leaveandsick"
Textbox " totalHours" unbound
Textbox "text69" unbound (name will change when I figure out what I am trying to do)
at the moment
"Accuredhours" has 2.20
"Outstandinghours"has 1.20
"AdvanceHours" has 0
" totalHours" has 4.50
TotalHours needs to come off in this order
"Outstandinghours"
"Accuredhours"
and the balance if any off "AdvanceHours"
so
"Outstandinghours"will become 0
"Accuredhours" will become 0
"AdvanceHours" will become 1.10
at some stage any one of the bound fields might be 0
no bound field will have a negative number.
so I need something to check if "Outstandinghours" has anything in it if yes takeaway Totalhours untill 0 if not or then move onto Accuredhours" check if that has anything in it and if so take the balance off or if not or then move onto "AdvanceHours" and add the balance to that

I hope that makes sense
maybe VBA with a select case
This post has been edited by wornout: Nov 6 2018, 11:00 PM
Go to the top of the page
 
ranman256
post Nov 7 2018, 08:33 AM
Post#2



Posts: 883
Joined: 25-April 14



i have a report table,
Q1 sums reg hours ,then appends to the report,
Q2 sums OT hrs,then appends to the report,

reports run off this table.
yours could do the same.
Go to the top of the page
 
projecttoday
post Nov 7 2018, 11:03 AM
Post#3


UtterAccess VIP
Posts: 10,252
Joined: 10-February 04
From: South Charleston, WV


You need to show your data to us. Type in the layouts of the tables.

--------------------
Robert Crouser
Go to the top of the page
 
wornout
post Nov 7 2018, 03:14 PM
Post#4



Posts: 1,218
Joined: 17-November 13
From: Orewa New Zealand


I dont think I worded my question very well will try again
I have 4 fields in my Table (LeaveSick) Accuredleave,outstandingleave,advancedleave,leavetaken I am not sure how to proceed, I need something that will check if outstanding and accured are 0 then put leavetake in advance, or if outstanding has a balance then reduce that amount by leavetaken but if there is not enough in outstanding take the rest off accrued if there is still not enough put it in advanced
now
Accrued.......... Outstanding...............Advanced..............LeaveTaken
2.00....................2.00.......................0.00.....................5.00
2.00.................... 2.00......................0.00....................3.00

How I want it
Accrued.......... Outstanding...............Advanced..............LeaveTaken
0.00....................0.00......................1.00.....................0.00
1.00....................0.00......................0.00...................0..00
This post has been edited by wornout: Nov 7 2018, 03:20 PM
Go to the top of the page
 
wornout
post Nov 7 2018, 03:22 PM
Post#5



Posts: 1,218
Joined: 17-November 13
From: Orewa New Zealand


I do realize that I might not beable to do it with one query but thought some VBA might do it in one go I dont mind which way it is done
Go to the top of the page
 
projecttoday
post Nov 7 2018, 08:06 PM
Post#6


UtterAccess VIP
Posts: 10,252
Joined: 10-February 04
From: South Charleston, WV


There is no employee column? Are you trying to update the table or produce a listing or report?

--------------------
Robert Crouser
Go to the top of the page
 
RJD
post Nov 7 2018, 10:01 PM
Post#7


UtterAccess VIP
Posts: 9,193
Joined: 25-October 10
From: Gulf South USA


Hi wornout: PMFJI, but here is a way to do this with VBA. I think I covered all bases, but check it thoroughly. When you open the demo, a form will appear with several rows of data (employees - I added the EmployeeID, as was suggested by Robert). The calculations and postings are done by record (employee) on a form - command button for each employee. Test this by employee, and if this works for you, you can apply this logic against all records in the table en masse, not just one record at a time.

Following your logic, here's the VBA I developed ...

CODE
Private Sub CalcLogic_Click()

If Me!LeaveTaken = 0 Then GoTo Outahere

Dim AccX As Double, OutX As Double, AdvX As Double, TakX As Double
AccX = Me!Accrued
OutX = Me!Outstanding
AdvX = Me!Advanced
TakX = Me!LeaveTaken

If OutX <= TakX Then
    TakX = TakX - OutX
    If TakX = 0 Then Me!LeaveTaken = 0
    Me!Outstanding = 0
ElseIf OutX > TakX Then
    Me!Outstanding = OutX - TakX
    Me!LeaveTaken = 0
    GoTo Outahere
End If

If AccX <= TakX Then
    TakX = TakX - AccX
    If TakX = 0 Then Me!LeaveTaken = 0
    Me!Accrued = 0
ElseIf AccX > TakX Then
    Me!Accrued = AccX - TakX
    Me!LeaveTaken = 0
    GoTo Outahere
End If

If TakX > 0 Then
    Me!Advanced = Me!Advanced + TakX
    Me!LeaveTaken = 0
End If

Outahere:

End Sub

So, take a look, test it with all the different combinations you might encounter and see if this is what you are trying to do.

HTH
Joe

Note: I just replaced the code and demo after a minor code adjustment.
Attached File(s)
Attached File  LeaveAdjustments.zip ( 26.25K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
wornout
post Nov 7 2018, 11:23 PM
Post#8



Posts: 1,218
Joined: 17-November 13
From: Orewa New Zealand


That is sooooo cool I have been trying for days I ended up with a hundred queries and still didnt get there going to try and replicate it into my DB. thank you thank you that you even my husband thanks you cause now he wont have to cook dinner again
Go to the top of the page
 
wornout
post Nov 8 2018, 12:07 AM
Post#9



Posts: 1,218
Joined: 17-November 13
From: Orewa New Zealand


Great so now
The leave goes into accrued from the date they start work for 12 months then after the 12 months is up that amount goes to outstanding and accrued starts again
your wonderful code handles if there is amounts in any of it but I need to get the amounts into accrued then to outstanding and yes I had that part covered with 6 querys but was so excited I deleted them all .
Go to the top of the page
 
RJD
post Nov 8 2018, 11:36 AM
Post#10


UtterAccess VIP
Posts: 9,193
Joined: 25-October 10
From: Gulf South USA


You are very welcome - glad that works for that part of the requirement ...

QUOTE
... I need to get the amounts into accrued then to outstanding ...

I presume you are working on that now and not asking for assistance at this point. Is that correct? Anyway, we don't know what the rest of your db looks like or what the accrual algorithm looks like.

In the meantime, looks like you have at least a partial solution with the new code.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
wornout
post Nov 8 2018, 12:12 PM
Post#11



Posts: 1,218
Joined: 17-November 13
From: Orewa New Zealand


You are correct I am sorry.
I found a bit of code that restores deleted objects as long as I had not compiled so restored my query's.
Silly me I got so excited by your code that I was deleting all the query's that I tried to make just to do what you did and deleted the important one as well.
It was not untill 2 this morning when I was going over what I had learned in my head I realized that my post above was silly as you had dealt with the leave taken not the accumulation of it. I had actually done a good job with that part admittedly not as good as you would of done as I used a few query's but it did the job.
So thank you once again
Go to the top of the page
 
RJD
post Nov 8 2018, 12:27 PM
Post#12


UtterAccess VIP
Posts: 9,193
Joined: 25-October 10
From: Gulf South USA


You are very welcome, of course. No problem. Good luck with your project. Let us know if we can be of further assistance ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th November 2018 - 11:59 PM