I am looking to calculate numbers in query like this if the field name in query is Unit and the second field name is amount and i want that the amount field to calculate the unit field on this criteria..Suppose if total unit is 393 so the calculation should be like this;
First hundred should be calculated with 100.00 X 5.79 = 579.00
Second 200.00 X 8.11 = 1622.00
Third 93.00 X 12.33 = 1146.69
Total should be displayed in the amount field = 3347.69
I have a form that have textbox that calculate total and then i want to convert numbers to words. Iv got the code with function currencytotext but when i set the code source of another textbox to:
=currencytotext([inv_total])
I have an error. I saw this on internet but i think bcz I am using access 2013 may be the syntax is different a bit.
I have a several fields on my form that are list boxes that must have text as the properties so I can populate the lookup data with things like" Severe rating = 7 points" and "Moderate rating = 3 points". I have another similar field where the choices are "High probability = 9 points" and "Low probability = 2 points" etc. A third field needs to show the total points (product) of the choices from the first two fields, i.e. 9 points x 2 points = 18 points. Then, a fourth field needs to display where the answer falls in a grid, i.e. 1-14 points = Low, 15-29 points = medium, 30-49 points = high, etc.
How do I apply the calculations on the field choices if the field properties have to be text instead of number?
I am creating a Make Table Query and calculating new fields based on current and prior year-end numbers. If the prior year-end number does not exist (Is Null), I want the use the current rate or calculate the change in rate. I have typed the below in the Field Box:
CHG_IN_PGM_RATE: IIf(([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE]) Is Null,([MASTER LIST CURRENT]![PTD_PM_PGM_RATE]),(([MASTER LIST CURRENT]![PTD_PM_PGM_RATE])-([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE])))
However, when running the query, I get the attached error message.
The screenshot will also show how the two tables are joined.
I have 2 fields that I would like to automate if possible
One field is called "p/o number" and another field called "line no"
These fields are part of an ordering database
Let say I have 200 items to purchase form 10 suppliers
And form example 20 items from each supplier
What I do at present is put the order number on each line item and the line number
example
p/o number line no
1 1 1 2 1 3
2 1 2 2 2 3 2 4
What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.
I am trying to use dsum to calculate a sum from either a table or query to display on a form and this needs to be for different services within a period so I am using the following:
The Query is called KatiesPeriodTakings The field I want totalled is called Price The date field is AppDate and the 2 controlling dates are the Startdate and EndDate from the Form
I have to make a form with multiple sections. Each section has n questions to which corresponds a numerical value from 1 to 4.
Example:
Question 1 value 2 Question 2 value 1 Question 3 value 3 Question 4 value 2 Question 5 value 4 Question 6 value 2
I have to choose the most recourrent number like the Excel MODA function then in this example is 2.But if there are more then one recurrence with the same times i must choose the highest one.
Question 1 value 2 Question 2 value 1 Question 3 value 3 Question 4 value 2 Question 5 value 4 Question 6 value 4
In the example above number 2 and 4 recur the same number of times and i must choose the number 4 because is the higher.
There is not a MODA function in Access, even if Excel, with the second example returns the most recurrent but the lower value 2.
i would want my form to calculate the age of a contact by using the inputted birthday, is this possible? and as long as the system clock is right, it should update too? or i'm wrong?
sample data: (assume the field name as SheetName)1 (considered as an index) MySheet (considered as a Name) How to handle them both in a single statement:vartype(SampleData!SheetName) returns 'Text' as Datatype for all values.
I have this code for calculating the age from birthdate but i want if there is years no months or days appear
Public Function CalcAge(Birthdate As Date) As String Dim intYears As Integer, intMonths As Integer, intDays As Integer intMonths = DateDiff("m", Birthdate, Date) intDays = DateDiff("d", DateAdd("m", intMonths, Birthdate), Date)
I am trying to get a module set-up that will add a number of workdays (no weekends, no holidays) to a date that is mentioned in a form and put the resulting deadline date in another field on the same form (date or remaining days to deadline would be even better).
Now it gets a little tricky, the module will need to select different amounts of workdays to be added to the date in the form depending on what status is selected (different statusses have different amounts of days).
I'll probably need: List of holidays List of days required per status Start date (to which the days can be added).
Example:
Status = "DQ" which has 2 days to work with. Start date in form = "21/03/2014". Result = 25/03/2014 or preferably "2 days remaining".
I want to select a date range from "Production" table where it agrees to the Dept_ID too. And then calculate the summation of the columns "hours", "produced" & "waste" of that particular range selection.
This is my code:
Code: Option Compare Database Private Sub cmdCal_Click() Dim sql As String Dim rs As Recordset Dim qdef As DAO.QueryDef Dim hours, waste, produced As Integer
[code]....
But it returns nothing, When i remove the errorHandler, it says that no records were found.
What I have is two fields, one is a simple date and the other is slightly more complex as it holds a date but in a different structure (a dated case number).
The case numbers are for example, 150211551223 the date being the first 6 numbers and equaling to 11/02/2015. so the code will need to grab this data, spin it around and convert it.
The formula will then be:
simpledate - convertedcasenumberdate = days between.
(simpledate will always be the latest date of the two).
At the moment I am working a little project at work and am stuck on how to calculate departments due date based on actual order due date.
example:
OrderID: xxxxxx-xx DueDate: 10/8/13
Now let say i have 5 different departments but I want/need to know what should be the due date for each of those departments based on the due date on top.
Ok i know negative numbers can be a bugger in calculations:
Code: Function FEV1pred(height As Double, ClientID As Double) As Double
Dim a As Double Dim Age As Integer Dim DOB As Date Dim TestDate As Date Age = DateDiff("yyyy", DOB, TestDate) + CInt(Format(DOB, "mmdd") > Format(TestDate, "mmdd")) 'In this case the clients Age is 39 a = -0.0244
'Now for the fun stuff...
FEV1pred = a 'returns -0.0244 so i know the dim a holds the neg number FEV1pred = (a * Age) 'returns 1.708 (which is incorrect, how this calculation is reached.) FEV1pred = (a * 39) 'returns -0.9516 (which is correct) End Function
So something about the dimming of Age seems to be doing something to the equation but i can't work out what?
I have a short text field where numbers such as "15.00", "2233.56", "-300.00" are stored. Now I want to convert the text field to numeric so that I have actually 15.00 or -300.00 stored as a double. I am going to do calculations on these fields. How can I achieve this conversion?
and I'm trying to pull the max value of the number after the -e- for a given set of them. In this example, I'd want to return the number 70. I'm then going to use that to create the next ID and populate another field.
The IDs are not used as the primary key. And while the previous IDs used leading zeros inconsistently, new IDs will not have leading zeros.
Here's what I have so far, but it doesn't seem to pull the number after the -e- at all. I think this section here is the problem, even though the same logic works in a query:
Code: Public Function MaxArticleERef(hbID As Long) As Variant On Error GoTo err_handler Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSql As String Dim maxERef As Variant
I need to create a very simple database that would just store records and produce a couple of reports. I have three tables: one with the roster , one has records of the inventory items people from the roster receive and another one contains types of inventory we have. Everything is very simple except for one part. Every time we make a record of an inventory item given to someone, it requires not only employee id and inventory type from the existing tables. It needs us to enter a serial number of an item. This serial number contains a letter and a number.
Looks something like this - M100. Many people receive a consecutive set of inventory items. For example, from M100 to M150. There is no way to have a separate table with all serial numbers because they constantly change. That is why we need to have two text boxes that would allow us to input a range of serial numbers or just one number. Then the program should separate numbers from letters, evaluate the range, create new records of numbers and then put new numbers and a letter back together into one field in the table where we have all inventory records.
I found the following code online that allows me to find numbers within a range, but it only works for numbers.
Dim varRange As Variant Dim lngLow As Long Dim lngHigh As Long Dim lngCounter As Long DoCmd.Hourglass True
[code]...
I then found a piece of code that is supposed to separate numbers from letters, but I can't find a way to make it work.
Public Function FindNum(strName As String) As String Dim strTemp As String Dim i As Integer For i = 1 To Len(strName) strTemp = Mid(strName, i, 1) If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 128 And Asc(strTemp) > 96) Then FindNum = Right$(strName, Len(strName) - i) End If Next i End Function
how to alter the code to make it work for my specific situation.
How to implement a functionality into my access database. The related tables required for this functionality can be found as an attachment.
When the information has been entered into the tables by the user, I would like to calculate the difference between the information for each part within the same table. The main columns for which the difference have to be calculated are: [RefObsInfo->RefSources] and [AltObsInfo->StatusAltSource] and [GenObsInfo->GenSources]. The values for these fields are restricted to 3 or 4 different ones. I also would like to show if the GenEstAvailable columns are changed, but that's for later.
In order to calculate this difference a query has to select for each Part the information related to the last date AND a date which has been specified by the user (with the previous date as max). For this the user is asked to give a from date, which she/he can select from a combobox. This combobox has a standard selected value which is the MaxDate-1, i.e. the previous date. The difference should be calculated from the previous date to the last date. I would prefer to give each (predefined) difference a specific action to show the user the difference in a matter of seconds.
I have two numbers, i need to calculate how much percentage of one is the other. E.g
num_1 = 100 num_2 = 10 percent = 10%
Users enter a dollar amount (retainer) and my code should calculate the percent of the total proposal amount.
Actual Code
Private Sub cmdSubmit_Click() Dim intProposalTotal As Double 'if i use interger i get "overflow" error intProposalTotal = Nz(DLookup("ProposalTotal", "qryPropsalTotalForRetainer", "proposal_id=" & Me.proposal_id), 0)
[Code] .....
billing_retainer_percent is formatted to Percent with 0 decimals. In the actual table Type - Number, Format - Percent, Field Size - Single. What i get is 100% instead of 10%.
I am using the code below to evaluate values for each transaction to determine the median:
Function MedianF(pTable As String, pfield As String, pgroup As String) As Single '******************************************* 'Purpose: Return median value from a recordset 'Inputs: ? medianF("Values", "Industry") <enter> 'Output: results are currently incorrect '******************************************* Dim rs As Recordset Dim strSQL As String
[code]....
The code generates median results based on the value but not the correct ones, as I check results in Excel using the Median function. I need to see the median for the transactions within the same industry and I do not know how to do that . Below are some sample transactions including the Industry, Value, mymedian (which the current results based on my code) and the expected median result (which I calculated in Excel based solely on the transactions listed here). Note that mymedian results listed in the 3rd column are determined using the complete data set (over 2000 transactions). I only included a few sample transactions below to show that I am currently getting 2 different median results: 44.62 and 50.82. Also be aware that I do not want to evaluate any zero value transactions, which I believe I am handling in the strSQL line above where I indicate <>0 . I need to see a Median result for each industry and the result should be the same for all transactions within the same industry.
Industry Value mymedian Expected Median Aerospace 18.01 50.82 40.45 Aerospace 53.02 50.82 40.45 Aerospace 109.18 50.82 40.45 Aerospace 61.54 44.62 40.45
[code]....
This is how I am passing the values from the query to the module, passing the value of Period2 if Period 1 = 0, else passing Period1: