Calculating Reimbursement Amount In A Subform
Nov 28, 2011
New to Access Form Design. Trying to create a form that will display the amount that is owed to an employee.
Our office gives its employees a $125 supply allowance each year. When an employee submits a receipt for a partial amount (say they spend only $35), I enter the amount in the PurchaseAmount field of my subform.
The ReimbursementField control source is =IIf([PurchaseAmount]>=125,125,[PurchaseAmount]).
The correct amount of $35 is displayed.
(There is no beginning balance or ending balance field on the form.)
When a subsequent purchase amount is entered and is over the amount of the remaining balance, (let's say spent $100 and only has $90 remaining) how can I have the form display the correct amount of $90? The closest answers I came across suggested using recordset clone in an AfterUpdate event, but I just can't figure out the proper way to write the calculation. My main form name is "Supply Purchases"; subform name is "Supply Purchases Subform.
View Replies
ADVERTISEMENT
Apr 1, 2014
I need to minus one amount from another in my subform.
How to do that and which command to use? Sum?
Also should I go to criteria and do it there?
View 2 Replies
View Related
Jul 21, 2015
I have One table(and designed form from it) in which i have recording the daily transactions(it is like a daily book). Daily transactions took place like Purchase of items of Amount 45, sale=70, sold on account=100 etc.
What problem i am facing is: I would like to Add Cash In Hand amount and would like to show it on my form. When day end I would like to move remaining cash In Hand on separate column of that date. The next day i would like to take the previous Remaining Cash in Hand as Cash In Hand at Start for next date and so on.
View 2 Replies
View Related
Nov 20, 2014
I am trying to get the main form to display a total Amount for all records in Purchase order details but the control displays Error:
I have a Form called frmPurchaseOrder with a sub-form sbfrmPurchaseOrderDetails
sbfrmPurchaseOrderDetails takes item details from a table based on what is selected in the combo box then fills out four additional fields in the same sub-form.
SELECT tblItemListDetails.ItemID, tblItemListDetails.ItemName, tblItemListDetails.ItemSize, tblItemListDetails.ItemUnit, tblItemListDetails.ItemUnitCost, tblItemListDetails.ItemBrand FROM tblItemListDetails WHERE (((tblItemListDetails.Supplier)=[Forms]![frmPurchaseOrders]![SupplierCombo]));
Private Sub cmbItemName_Change()
Me.txtSize.Value = Me.cmbItemName.Column(2)
[Code] ....
View 5 Replies
View Related
Jul 17, 2014
I am trying to make a report in which I can see the amount of money that I have to pay weekly to an employee. To explain myself clearer, I have different rates for each employee. For example:
NameNormalPayRateVacationPayRateSickPayRateOvertimePayRateStandbyPayRate
John Smith$15$10$15$30$8
Ken Jonson$10$8$10$20$8
I am doing this in a query. So what I want to be able to do is, for example, take 4 hours that the employee worked at NormalPayRate and multiply it. But if tomorrow the employee worked at a different rate, for example 3 hours overtime, I want those 3 hours multiplied by OvertimePayRate.
The way I saw it on my Google researches, is that they have only one rate and the amount will be [SumOfHours]*[Rate]. But how can I tell my query to skip, for example, one rate because the employee didn't work at that rate that day. So I can have different amount for the different rates.
By the way, entering the information I can divide at what rate the employee worked x day. So if the employee work 6 hours at regular, and later on 4 hours at standby, I have those information separated in my table.
Results that I am getting: I can easily get the amount in money multiplying the whole hours by a rate, so my result in my report will be multiplied by the same rate.
I am using Access 2013 ...
View 2 Replies
View Related
Oct 26, 2005
Hi,
Attached is a project I'm working on. We want to calculate the total delays on each day. In a form called 'main' there is a subform for the delays on that date. But the 'Total Delays' control does not work.
I have used the following format to get the total from the subform:
=Delays.Forms!TotalDelay
'TotalDelay' is the name of the control which calculates the total of the delays within the subform.
I have used the =[Delays].[Forms]![TotalDelay] format in other projects and it works. However in this program when I want to save the form, the control source is changed to :=Delays.Forms!TotalDelay (the brackets are removed)
Thank you
View 1 Replies
View Related
Sep 17, 2004
I have a Main form with a sub form. Every record in the main form has multiple sub form records. (they are linked in a one to many relationship)
Each subform record has a "price" text box. I would like a total of all the "price" text boxes for that main record to be displayed in the main form.
It needs to be updated when a new subform record is added or changed.
Any help is welcomed. Thanks in advance
Joe
View 7 Replies
View Related
Feb 22, 2006
I have created a form with company ID, company name, and so on. I put a subform in the main form which includes invoice & points information. Somehow I need to calculate a balance for each customers points. In the form I have points earned and points redeemed...how would i get the totals for each customer's balance?
The main form is linked to the subform, so that you can see each individuals record of points. Please let me know how I can get the balance for each customer.
Thanks.
View 1 Replies
View Related
May 6, 2005
I have created a form with a subform and I want to subtract the total of the subform from the total of the form to get a balance; i.e. I have a total of expenses on the form and the subform is a record of payments against the expenses and I want to show a balance.
I have been all over this one and can not make the expression work. I have been working in Access for some time but this one has me stumped, I am probably over thinking it or just can't see the forest for the trees.
I have gone into the expression builder and created the formula but when I complete the process I get the #name? error statement in the field.
Can anyone help me with this problem? Thank you in advance. You can send responses to sharon.dunn@newmont.com.
View 2 Replies
View Related
Oct 9, 2013
On my master form I have a subform called invoices this is tied by Site Number to the record on the form which is has the following important information, site code. group code, exp code an amount.
On another subform via a query I have a summary of all the invoices by exp code and group code.On another subform I need to create a Income an Exp.
I have two combo boxes 1 for the Group Code, and another for the expense code what I want it to now do is look up group code exp code = Total amount. There is currently only 1 line as you will see but there will be many rows of summary data...do i need a subform for the summary?
And I need to do it this way as not all exp appears on the income and exp and I want to be able to lay out the expenses for each site differently Attached is the zip file, of the database the only record that has data is site code 0289S
View 2 Replies
View Related
Sep 25, 2006
Ohh Stress. Need Help....I have a mainform that contained two subforms in it. MainForm called "Employee", the two subform called "vac", and "Prevac". There is a field in my mainform called "Hours", that I'd like to use to Add with a field in Vac subform called "VacA" and a field in Prevac called "FHours". I'd like to display the total (sum of all three field on the same mainform. Is there anyone have any idea? Thanks in advance...
View 3 Replies
View Related
Feb 3, 2005
I have a script file of FoxPro 2.6 which is use to read amount field and write it in words.
Can any body convert in VBA Script.
I manualy write amount in words.
Thankyou.
Saira
View 3 Replies
View Related
Sep 26, 2005
How many users can an access database support? I'm setting up an app accross 3 workstations but each has multiple users. There may be as many as 1000 records a day entered. Can an access database handle this or do I need to switch to sql server?
View 6 Replies
View Related
Nov 8, 2007
Hi
Is there any way i can increase the amount of columns in a table before importing data? I am importing data from a notepad doc that needs abt 300 columns but i can only get abt 100 columns.
Thanks for any help
View 8 Replies
View Related
Jun 2, 2005
Hi All.
I need to generate a query which will show me in Field1 Cost for the current year only and in Field2 Cost from a start date to the current. If is it possible how to do it?
Thanks.
View 1 Replies
View Related
Jun 3, 2005
I'm trying to produce a query that will bring up a list of all employees with their current salary amount. The table however, has all changes for an employee and reads as follows:
Table Name(NBRJOBS)
Employee ID-- Date of Change -- Salary Amt
1234 ----- 01/01/2003 -------- 500.00
1234 ----- 05/01/2003 ----- --- 600.00
1234 ----- 11/01/2003 ---- --- 700.00
2222 ----- 03/05/2004 ------- 200.00
2222 ----- 12/05/2004 ---- --- 300.00
2222 ----- 02/01/2005 ---- --- 300.00
It will be necessary to create a report to list out all employees and their current salaries. Any suggestions?
View 5 Replies
View Related
Dec 6, 2005
I have a table containing the following two fields, one with monthly dates (end of month plus year) and one with profits (per month). However, for some dates the records are missing. For example, for the 31-1-1994 there is no record (not in the date field, nor in the profits field).
How can i create a query that will only show me the records if 10 or more monthly subsequent profits are known, so meaning that in those 10 months no records are missing? So that only the timespans without the gaps (missing records) are shown.
So if the 31-1-1994 and the 30/6/1994 record are missing, then the 4 subsequent records in between those two dates should not be shown,, since the amount of records is not 10 or more. However, if the next missing date would be 30/6/1995, then all the 11 subsequent records between 30/6/1994 and 30/6/1995 should be shown. Since the number of records is bigger than the required 10.
Thanks for helping me out!
Luuk
View 4 Replies
View Related
Jul 14, 2007
I'm ok with Parameter Queries now, but I'm stuggling for the certeria to select say the top 20 records for a list. This would be based on a number, say amount spent.
Any help?
View 3 Replies
View Related
Nov 23, 2007
Dear all,in my table I have the following fields:- fldstartdate- fldstarttime- fldenddate- fldendtimeCan I calculate the time that is between those variables using an expression?fldenddate.fldendtime - fldstartdate.fldstarttime = "x days and x minutes"Kind regards,Hans B.
View 4 Replies
View Related
Feb 27, 2008
Hello All
if I have thirty records and have the end user select the first 8 or what ever amount they want, could be 3, 14, 9 etc, is this possible via a form ?
thanks
View 13 Replies
View Related
Mar 3, 2005
Make Table Name: tblnum
Fields:
1.num (for number)(Feed 1 to 99)
2.inword (for In words)(Feed One to Ninty Nine)
Make Module
Public Function inwords(amount As Variant) As String
Dim intlac As Integer
Dim intTh As Integer
Dim intHun As Integer
Dim intNum As Integer
Dim strLac As String
Dim strTh As String
Dim strHun As String
Dim strNum As String
Dim intlen As Integer
intlen = Len(amount)
Select Case intlen
Case 7
intlac = Left(amount, 2)
intTh = Mid(amount, 3, 2)
intHun = Mid(amount, 5, 1)
intNum = Right(amount, 2)
strLac = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intlac) & " Lac"
If intTh = 0 Then
strTh = ""
Else
strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"
End If
If intHun = 0 Then
strHun = ""
Else
strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"
End If
If intNum = 0 Then
strNum = ""
Else
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End If
Case 6
intlac = Left(amount, 1)
intTh = Mid(amount, 2, 2)
intHun = Mid(amount, 4, 1)
intNum = Right(amount, 2)
strLac = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intlac) & " Lac"
If intTh = 0 Then
strTh = ""
Else
strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"
End If
If intHun = 0 Then
strHun = ""
Else
strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"
End If
If intNum = 0 Then
strNum = ""
Else
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End If
Case 5
intTh = CInt(Left(amount, 2))
intHun = CInt(Mid(amount, 3, 1))
intNum = CInt(Right(amount, 2))
strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"
If intHun = 0 Then
strHun = ""
Else
strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"
End If
If intNum = 0 Then
strNum = ""
Else
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End If
Case 4
intTh = CInt(Left(amount, 1))
intHun = CInt(Mid(amount, 2, 1))
intNum = CInt(Right(amount, 2))
strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"
If intHun = 0 Then
strHun = ""
Else
strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"
End If
If intNum = 0 Then
strNum = ""
Else
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End If
Case 3
intHun = CInt(Left(amount, 1))
intNum = CInt(Right(amount, 2))
strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"
If intNum = 0 Then
strNum = ""
Else
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End If
Case 2
intNum = CInt(Right(amount, 2))
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
Case 1
intNum = CInt(Right(amount, 2))
strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)
End Select
inwords = strLac & " " & strTh & " " & strHun & " " & strNum & " Only"
End Function
Result = inwords(Number TextBox)
View 2 Replies
View Related
Sep 2, 2005
What is the maximum amount of fields/columns a table can hold in access 2003?
And what do you do if you have a form and its record source is a table that has run out of fields/columns?
If I remember right in access 97 it was 256 or 257 fields/columns was the max allowed. And it seemed to me it was quite common to run out of fields.
I'm currently waiting for my access 2003 upgrade disk to arrive and I'm
anticipating issues that might occur.
Thanks--Any help is much appreciated.
View 3 Replies
View Related
Feb 8, 2008
Hi everybody....
I have a vba question....
I have a form that states how many entries a certain table can have related to that form.
For example:
Form1 - based directly on the table for diagnostic purposes
Cust ID - text - primary key
NumofEntries - number - integer
I want it to create X amount of records in form2 based upon the NumofEntries from form 1 based on table1
For example, I run form 1, and it asks for the custnum, I enter it, and then it asks for the number of entries allowed for the customer, and then I enter it. This number can be different each time. I want the 2nd form just to show that many fields for entry either by a running total (subtraction) in form view or by a for next loop to create X amount of records in the table.
Form1:
CustNum = 1011
NumofEntries = 3
CustNum = 2022
NumofEntries = 4
CustNum = 3033
NumofEntries = 2
Table2
Custnum = 1011, entrynum =1, entry data
Custnum = 1011, entrynum =2, entry data
Custnum = 1011, entrynum =3, entry data
Custnum = 2022, entrynum =1, entry data
Custnum = 2022, entrynum =2, entry data
Custnum = 2022, entrynum =3, entry data
Custnum = 2022, entrynum =4, entry data
Custnum = 3033, entrynum =1, entry data
Custnum = 3033, entrynum =2, entry data
the key is to have the entrynum to start at 1 each time, the rest I can handle.... I am at a loss right now, as I am down to one brain cell, and it's misfiring.....
Granted, tomorrow, when I wake up, I will prolly have a solution, but as always, I value all of your input and design suggestions. Yes, I know already that it's a one to many relationship from table 1 to table 2, I just want some alternate ways to do this. Thanks in advance for any insight you wish to give.
View 3 Replies
View Related
Jun 19, 2015
I have a qry which gives me this data:
date/code/hrs/amount
12/05/15 ABC 4 243.55
12/5/15 DEF 6 707.12
13/05/15 ABC 1 101.01
etc..
I'd like to sum the amount for each date. Is this possible?
I will be updating the table with new dates and want to eventually create a form where i set the dates and it does the query where I get the amount for the dates specified, e.g. 01/07/20 - 08/07/20
View 1 Replies
View Related
Apr 25, 2005
Hey! I'm doing Computing AS level and the coursework is to create a system for "Terry's Turkey". This is basically my first introduction to Access, and our teacher is little to no use!
After alot of work working out things in access and reading alot of books, other than creating the switchboard, theres only a few things left to do!
The first is to limit the amount of "turkeys" that can be ordered. I have 2 tables that this envolves... Order and Customers, they have a one to many relationship (each customer may place one or more orders, each order must be placed by one and only one customer). They are linked by Customer ID. I need to set two limits, I guess by a validation rule of some kind. The first is that each Customer can place no more than 5 orders. The second is that their can be no more than 40 orders in total. A slight complication to this is that a customer can "cancel" an order, in this case its marked as cancelled (theres a yes/no field called Active to determine this), and ideally, these wouldnt be included in the counts.
So far I have the following as a query, though I have no idea how to put this into a validation rule
SELECT Count(Orders.OrderID) AS Var1
FROM Orders;
I guessed that to expand this to not including inactive orders something like
SELECT Count(Orders.OrderID) AS Var1
FROM Orders
WHERE active="yes";
Any help at all would be most appreciated! I have spent many hours pondering over this is and looking at as many access sources that I can! I think this is my last resort :(
View 3 Replies
View Related
Sep 2, 2005
What is the maximum amount of filds/columns a table can hold in access 2003?
And what do you do if you have a form and its record source is a table that has run out of fields/columns?
If I remember right in access 97 it was 256 or 257 fields/columns was the max allowed. And it seemed to me it was quite common to run out of fileds.
I'm currently waiting for my access 2003 upgrade disk to arrive and I'm
anticipating issues that might occur.
Thanks--Any help is much appreciated.
View 3 Replies
View Related
