General :: Minus One Amount From Another In Subform

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 Replies


ADVERTISEMENT

General :: Multiply Different Amount Of Hours By Different Amount Of Rates

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

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 4 Replies View Related

Modules & VBA :: Adding Amount And Checking Daily In Hand Amount?

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

Forms :: Calculations In Subform - Display Total Amount For All Records

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

General :: Count Amount Of String?

Jun 23, 2014

In form (datasheet) I have a three columns "RightColors", "LeftColors" and "AmountOfColors"

I want to do something like this:

If I fill 'RightColors' "red;green;blue;", and fill 'LeftColors' "orange;" then in 'AmountOfColors' column should be "4". Sometimes I can fill only 'RighColors' or 'LeftColors;

I think I should add code in after update event. Is this possible to build code which can count colors using ";"? I need loop for this, right?

View 2 Replies View Related

General :: Limiting Amount Of Bookings Per Course To 50

Jun 27, 2013

I have Trainee, Staff, Course, and Booking tables and forms. Everything is working fine but I want to limit the amount of bookings per course to 50, how would I go about doing this?

View 9 Replies View Related

General :: Finding Average Amount Of Items

Feb 18, 2013

I work in the Insurance Industry and I am having a hard time trying to find the formula for finding the answer to the below issue I am having.

In a table I have 4 columns:

1st: Total # of Claims (Claim Count)
2nd: Total # of Items (Item Count)
3rd: Item % of Claim Count
4th: Average # of Items per Claim (This is the number which I am trying to get)

Is there a formula which could give me the "Average # of Items per Claim"?

View 3 Replies View Related

General :: Calculation Of 2 Amount If Some Of The Fields Null

Oct 16, 2012

Calculation : In the event the null, is will become 0.

OT Qty: Nz([Qty(Overtime)-Nz([Qty(Work Hour)])

Is work to calculated the OT amount. How to set the value become zero in the event the the Qty(Overtime) was null.

View 4 Replies View Related

General :: Drop Down Box - Field Is Too Small To Accept Amount Of Data

Sep 17, 2014

I currently have a drop down box with four options in there and the options are:

Low
Minimum
Medium
High

However when i change the options in the table to:

Low
Moderate
Significant
High

it comes up with the error message: the field is too small the accept the amount of data you attemtpted to add. Try inserting or pasting less data.

I do not understand what is wrong with the second option of words? The maximum for characters is set to 255 so i am not sure why it is coming up with that error message.

View 4 Replies View Related

General :: User Login Page Which Count Amount Of Time A Person Logs In

May 23, 2013

I have made a basic form and use it a log on screen. This requires the user to in put a user name and a password before gaining access to the main database.

The background to the form is a table where the users details are entered including the i enter the user name and password.

Is there any way to add a counter that is only visible in the table, so I can report on the different users.

View 14 Replies View Related

General :: Calculate Deposit Amount For Current Month - DSUM With Multiple Criteria

Mar 23, 2013

I am trying to get deposit amount for the current month but results are in accurate i am using this

DSum("[Amount]","[income]"," [trans_type] = 'deposit'" AND "Month([dep_date]) =" & Month(Now()) AND " Year([dep_date]) =" & year(Now()))

View 3 Replies View Related

Plus Or Minus

Mar 21, 2006

Hi,

I am setting up a query to display a records of products within certain sizes.

I want the width to be input, eg; "500" and i want all records - plus or minus 50 of this record to be displayed. I know i could use the "BETWEEN" "AND" command but i want the user to only enter 1 size if it is possible.

Any help greatly apreciated.

Thanks

Don

View 1 Replies View Related

Minus Figure

May 20, 2005

Hi
This is probaly really easy, I want to identify just minus figues in a query ie -5.00. I have tried iif([TotalPrice])<0,[TotalPrice]) this shows no result. Can any one advise. Thanks in advance.

View 1 Replies View Related

MINUS Operator

Nov 15, 2005

Hi guys!

It's unknown for me why Access hasn't got MINUS implemented and Unmatched Query Wizard doesn't help me much. So I'm wondered if anyone had to confront the following task:

Table1

Book Agent
----------
b1 a1
b2 a2
b3 a3

Table2

Book Agent
----------
b1 a1
b3 a3

The problem is I have to eliminate duplicates but I need to find unique row, which is Book and Agent. From my example it's row with (b2 a2). I tried using
Select * From Table1
Where (Book, Agent) Not In (Select Book, Agent From Table2)
didn't work
I also tried EXISTS
didn't work
Unmatched Wizard compares only one field, not two.

Any comments welcome, thank you

View 2 Replies View Related

Minus &lt;-&gt; Union

Jul 11, 2006

To fill a ddl list I could use a MINUS query. I found out that UNION and UNION ALL work, but MINUS doesn't.

Is there a way to make it work, or maybe an other way to solve this kind of problem?

View 1 Replies View Related

Get A Minus Number Where It Should Me A 0

Mar 15, 2005

Hi,
I've searched this forum and can't quite get what I need.
I have a subform on a form, in the footer of the subform I total an amount owing field. Then I get the value from the footer control to the main form. I know that the total should be 0 but on the main form I get a -0.0119. How can I get this to show 0.
Thanks

View 1 Replies View Related

I Want To Do A MINUS Operation Equivalent. How?

Jan 16, 2007

this is vexing me.

I have 4 tables, 2 entities, 1 relationship and 1 lookup

Entities are tblOrg and tblForm
Relationship is tblVentureParticipation
Lookup is tblInfo (Has many different lookups)

When an organization (these are listed in tblOrg) turns in a form, a new record is inserted into tblForm with the ID of that organization and the ID of the form

I want to identify the forms that have NOT been turned in.

So I figured

Step 1:
Identify all organizations who are participating (WHERE tblOrg.OrgID = tblVentureParticipation.OrgID)

Step 2:
Join all of the previously selected organizations with all of the forms (FROM tblOrg, tblInfo WHERE tblInfo.LookupID LIKE 'F##')

Step 3:
Remove all of the entries in tblForm that match the result of Step 2.

I have verified that both of these individual queries return the right information ...

View 1 Replies View Related

'Sum' Ignoring Minus Figures

Jan 22, 2007

I've been using the following query:

SELECT
[qry_1].Month,
B_Division_Group.Grouping_Name,
Sum([qry_1].Month_Client_Count_from_B) AS Monthly_Count,
Sum([qry_1].Month_Assets_from_B) AS Monthly_Total,
Sum([qry_1].YTD_Client_Count_from_B) AS Yearly_Count,
Sum([qry_1].YTD_Assets_from_B) AS Yearly_Total
FROM
([qry_1] INNER JOIN tbl_branch
ON [qry_1].BranchCode = tbl_branch.BranchID)
INNER JOIN B_Division_Group
ON tbl_branch.BranchName = BDivision_Group.N_Br
GROUP BY
[qry_1].Month,
B_Division_Group.Grouping_Name;

While all monthly and yearly values were positive, the query produced exactly the results expected (i.e. one row of data for each Grouping Name/Month combination, containing the overall totals for each field).

Now, however, some Grouping Names have minus values and the query is showing an extra row (one for positive values, one for negative). It's as if having one or more minus values is being treated as a new Grouping Name/Month combination.

Is Sum() the correct method to use, when dealing with negative values, or should I be using some other function?

View 3 Replies View Related

Date Minus 1 Year

Jul 25, 2007

I am creating a report and I need data about parts that have not been used in the past 12 months. I'm not sure how to get just numbers from the past 12 months. How do I get the current date minus 1 year?

View 2 Replies View Related

TimeOut Minus TimeIn

Dec 13, 2006

I have a datasheet subform that is acting like a timesheet. I have a TimeIn field and TimeOut field (both fields formatted as Medium Time); and a field for Hours to total the time. I used the expression =HoursAndMinutes([TimeOut]-[TimeIn]) that I found in another post, and it didn't work. All I got was #Name. Any thoughts as to why that's happening? I would preferably like the hours to total in tenths of hours, ie. 10.5.

Thanks
ScrmingWhisprs

View 6 Replies View Related

Default Value And Expression Minus 1 Day

Mar 4, 2015

So I am trying to setup a default value in the tbl design. The Following two fields are the two in question(StartDate & ReportDate). It already has relevant dates properly formatted in the StartDate Field. The ReportDate will always be the day before or minus 1 day from the Startdate. My question is that I can't seem to get the syntax right to make it work.

Screenshot included.

View 1 Replies View Related

Sum IF Else Minus - Calculation In Field

Mar 4, 2013

I have a report which is a statement. I want to be able to have a field that calculates rent, and minus payments. There are a few rent types, Pro Rated rent, Rent brought forward and rent changed, then there is 1 payment type. I am trying to write the sum a few ways, but have not had success. If rent type i= 1 or 2 or 3 I want it to add, if rent total = 4 I want it to minus.

=Sum(IIf([RentType]=1 Or 2 Or 3,[RentAmount],0))-Sum(IIf([RentType]=4,[RentAmount]))

View 2 Replies View Related

Queries :: Sum Field Then Minus Quantity

Jun 3, 2014

I am trying to sum a qty field naming it 'TotalRecieve' and then minus a qty from that sum?

I've tried the following

Code : RemainingQty: Sum([tblReceiveDetail].[QTY])-[tblOrderDetail].[Qty]

but i get an error saying 'Your query does not include the specified expression as part of an aggregate function?

View 2 Replies View Related

Queries :: Using One Date Minus To Other Dates

Jul 8, 2015

I need to create a query that will do the next:

use today's date, minus start date, minus sent to BP, minus response from BP.

The field names are:
Start_Date
Sent_to_BP
Response_from_BP

I also have a holiday table that should be used to have only business days passed (so, not weekends and holidays counting).

View 8 Replies View Related

Forms :: Plus / Minus Button For Any Numeric Box On Form

Oct 1, 2014

I'm working on a form that I want to insert a plus button/minus button.I am aware of the vba code numbers 107 and 109 if you want to use the actual keys but I would rather use a button.

I made a button and here is the code I used now. This code is numeric box specific. So it only adds one to the MileageStart numeric field. (Additionally you can put a minus in to subtract one).

Private Sub Command153_Click()
MileageStart = MileageStart + 1
End Sub

Instead of having a bunch of these buttons all over boxes on my form is there some code that I can use to "select" a text box and just have the Plus/Minus button on the form header that I would hit and it would add or subtract (and then I could go to another box select it and hit Plus/Minus and so forth).

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved