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 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

Tax Calculation - Save Total Amount In Table

Mar 16, 2013

We have a form where we enter various amounts and a box where these are added together to calculate taxes. This amount is shown on the screen but we need to save this total amount in the table where it can be used in other calculations in other forms

The control in the box looks like this:

=[registration fee]+[membership fee]+[fee per term]+[additional classes fee]+[costume]+[books]-[discount]+[applicable gst]+[uniform fee]

How can I get the result of this formula saved in another table field?

View 4 Replies View Related

Modules & VBA :: Calculation - Subtract Monthly Installment From Loan Amount On Monthly Basis

Apr 22, 2015

I have a query for loan calculation, fields are;

Loanamount
loandate
monthlyinstalment

what i want is that query to start subtracting lmonthlyinstalment from loanamount on monthly basis

View 3 Replies View Related

Null In A Calculation

Jul 12, 2005

Ok, so sorry if this has been asked before but I couldn't find my answer searching.

Basically my expressions in a query are giving me #error because there are some null fields being calculated.

I would like to know how to consider all null values as 0.

Is it something like this? IIF [FieldName]=Null,0

There are about three or four different fields that need something like this.

Also, I'm kind of new at this :o so could someone please tell where exactly to put the code for this?

thanks so much in advance.

joëlle

View 14 Replies View Related

Null/zero Calculation

Nov 30, 2007

i have database, my old database use blank, the new one use "0" (zero) on empty field, let's call "num" field. when i do calculation, it doesn't return any value (both on zero/empy). I use "-" (mines) calculation.

So far i save it on excel and use excel formula to do the calculation.
How i can solve this problem?

thanks

View 1 Replies View Related

Max Amount Fields In A Table

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

Fields In A Table (Max Amount Allowable)

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

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 :: Display Fields With Large Amount Of Data

Mar 27, 2013

Best way to display "notes: field that gets updated/added to multiple times, in a form. (Access 2007)

I've got a DB to track information about product changes. Each month we have a meeting and discuss problem products. I'm concerned with a notes/comments section. Currently it is a memo field in the source table that can be added to, so the notes are cumulative. But we want to auto-add a date to each new entry (in case the person inputting the data forgets, and so that notes can be sorted by order on a report).

There may be many comments on a certain product or there may be none. Sometimes the note may be very long, other notes on the same product may be very short. I created a new table with a one-to-many relationship to case ID (to link it to the main form), auto-populate a date field, and field for notes in order to link multiple notes to each case (and of course an autonumber PK).

I can put the subform in the original form, but then the note field will be the same size for every note, though notes are not all uniform in size/length, and it ends up taking up a LOT of room on the main form. So I created a button to click to open the subform, but it brings up all records for all cases, not just the currently selected case. Again, have the problem of one uniform field size for all notes, regardless of size/length.

Then I considered a report to view past history (field size can shrink or grow as needed) then a button on the report that opens a form for data entry/entry of new note. But again the report displays all records for all cases, not just the currently selected case.

View 2 Replies View Related

Forms :: Hiding Certain Fields Based On Number Amount

Jun 13, 2013

I search around online and did come across much info on code to how to hide fields. But, the problem is I cant seem to find a way to hide a certain amount of fields based on a number amount.

example: Lets say I have a 12 text fields to show 12 months of monthly payments, and lets say a client only has a 3 payment term. How can I list 3 under installment field and have remaining 9 fields auto hide since they wont be necessary? this way I only see what applies and not additional text fields.

View 1 Replies View Related

Combine Fields In Different Tables And Amount Is Duplicated / Stacked

Jul 20, 2015

I have a few input tables and I am trying to combine the fields from 2 tables into one query. How do I go about doing it without having the project amount (highlighted in red) being duplicated?

Table 1

Table 2

Query

Project
Project Amount

Project
Budget
Budget Amount

Project
Project Amount
Budget
Budget Amount

[code].....

View 2 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 :: 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 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

Forms :: How To Auto Populate Fields Based On Payment Term And Amount

May 14, 2013

How can I auto populate fields based on a payment term & amount?

example: client has 9 monthly payment of $150 term due every 15th of the month. (I would like it to be able to max out at 30 months)

So what I want to do is populate 9 monthly fields with dates and $150 payments fields next to it. Now, the payment fields even after auto populate, must be flexible in the sense for me to add a different amount just in case client makes an over payment that month too. In, addition I would like a check mark box to auto populate along if possible based on the 9 month term, this way I can manually check TRUE when payment is received in office.

View 3 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

Calculation In Fields Of Two Different Tables

May 21, 2006

I have a field1 in table1 & field2 in table2. (field1&2 are numbers)
After designing the tables I linked them with an ID (which is primary key in table1).

When I created a tabular form based on these two tables & tried to enter data, two problems.

1) Error come unable to update / enter & help says its locked while when I check the property of Locked, it is set as 'No' & Enabled 'Yes'. Why I can't input data.

2) The calculation field2/field1*1000 does not appear in a textbox (where it is defined).

Nafees
nafeesjan@msn.com

View 1 Replies View Related

Performing A Calculation On Two Fields

Dec 14, 2006

This is going to be a simple one for someone, but it is proving to be a pain for me. I have two collums in a table, one that is a value another is a quantity, i have another field that totals the two fields. This works fine and shows the total but it does not store the total into the database, how can i get it to do this.

In the Total field the control source is "=[Cost]*[Quantity]" i have tried to set it too TotalCost=[Cost]*[Quantity] but this gives me errors

Thanks for looking

View 3 Replies View Related

Reports :: Calculation For 16 Fields In A Report

Jun 23, 2013

I'm using Access 2010 and creating a report that is summing 16 fields but the wizard says I have too many fields selected. I had tried creating the report from scratch adding a sum field in the group footer but the field appears blank.

Below is what I am adding together all from one table. I have another report that will need to sum up 20 fields from a query.

=Sum([S1_MaxProfSupportAmt]+[S1_RateAirAllow]+[S1_RateRailAllow]+[S1_RateHireCarAllow]+[S1_MaxPersonCarAmt]+[S1_RateTaxiAllow]+[S1_RateParkingAllow]+[S1_MaxAccomAmt]+[S1_MaxAbsentAmt]+[S1_MaxBreakfastAmt]+[S1_MaxLunchAmt]+[S1_MaxDinnerAmt]+[S1_MaxIncidentalAmt]+[S1_MaxRoomHireAmt]+[S1_MaxSecretaryAmt]+[S1_AddNoteMaxAmt])

View 5 Replies View Related

Queries :: Calculation Query To Add Values Of Three Fields

May 8, 2013

I created a simple calculation query to add the values of three fields:

Program_Cost, Auditorium_Cost and Millage_Fee.

I followed the steps found here: [URL] ....

But it doesn't work. The query pulls the values for the relevant fields but doesn't actually calculate the total. What am I doing wrong? Here's the query's SQL:

SELECT [Event Information].Event_ID, Sum([Program_Cost]+[Millage_Fee]+[Auditorium_Cost]) AS Total_Cost, [Event Information].Program_Cost, [Event Information].Auditorium_Cost, [Event Information].Millage_Fee
FROM [Event Information]
GROUP BY [Event Information].Event_ID, [Event Information].Program_Cost, [Event Information].Auditorium_Cost, [Event Information].Millage_Fee;

View 2 Replies View Related

Table With Four Fields - Calculation Of Total Time

Feb 21, 2013

I have created a table with four fields. The names are:1stBusOn, 1stBusOff, 2ndBusOn, 2ndBusOff.

I want the time for the first two fields and the time for the second two fields to end up in a field called TotalRideTime.

Also, if only one set of the fields are completed I still want that total time to in up in the the TotalRideTime field.

View 1 Replies View Related

Queries :: Calculation That Counts Where Values Equals 4 In Fields

Nov 15, 2014

I have six fields in a record that look like Median.University1, Median.University2, Median.University3, Median. University4, Median.University5,Median.Unive rsity6. How can I create a calculated field that counts where values equals 4 in the fields . I have tried Dcount and Count with no success.

View 7 Replies View Related







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