Reports :: Formula To Get A Field Value Corresponding To Another Field Value
Jan 16, 2015
I need to add a text box to my report that shows the value of a record in a certain field that is corresponding to a given value in another field.
For example, if my report shows the following:
N City
3 D
7 F
2 H
9 K
4 A
the text box should show the name of the city corresponding to the largest number which is in this case "K". is it possible through formula builder?
View Replies
ADVERTISEMENT
Apr 27, 2005
I've got a table set up with sales info and all other kinds of data. How do I get a formula to work? Here are the inputs I have: Total sales (by outside company), total purchases (from my company). I want to know what percent of their total sales are made up of the poducts they purchase from me and have that field update automatically. Its a simple formula really, I just don't know how to enter it properly. Thanks.
View 2 Replies
View Related
May 31, 2007
Hey guys hope someone can help me out here...
i need to have a currency field in one of my tables, the user will enter the price in euros, and the output will be in pounds... therefor i need the following formula to work:
[enterdprice]*[current exchangerate] = price in pounds
i would very much appreciate suggestions/help
View 3 Replies
View Related
Dec 15, 2006
hi i have a text field in with this calculation
=([Total amount requested]+[text3])/[Initial valuation]
it displays a div/0 error which i want to remove, i know i need to put this calcualtion in an iif statement but whenever i try and make the formula it never works
=iif([Total amount requested]+[text3])/[Initial valuation],?,""
where the question mark is i am not sure what should go there, as i want the total to of the calculation there and if 0 hide 0 with the ""
this has been bugging me for a while now and i can just get my head around it, probably very simple for someone to answer
regards
chris
View 3 Replies
View Related
Apr 11, 2005
Hi all,
A nice easy one for you. I am creating a database, one of the fields is total cost. Is there an easy way to have another field, which calculates the VAT without using a query? I would like this done as soon as the total is entered.
I have done this using a query, but didnt like this way.
I am sure there must be a very simple way of doing this but it has escaped me.
Help
Phil
View 2 Replies
View Related
Nov 20, 2014
I am trying to make a query using fields from multiple tables. I used the Expression builder to create it and it is a formula field where multiple table fields are involved. But whenever i run the query Access asks for a parameter value in a modal dialogue for the value of the fields. Is it because the field names has Square brackets around it? But access puts that automatically. How to make it work properly?
View 11 Replies
View Related
Apr 8, 2013
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail.
I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
How can this problem be rectified?
View 11 Replies
View Related
Mar 9, 2008
I'm sure this is a simple issue, but so simple I can't find the answer.
I have a recipe DB and have calories and fat and fiber fields, I want to have another field which will display a point count for a formula (calories/50+Fat/2+fiber/5) but the catch is if the fiber number entered is over 4 I only want the calculation to use 4 max.
Not sure how or where to create this formula to populate a field in the DB.
As you can tell I'm new to all this and any help would be appreciated.
Thanks in advance
Mike
View 14 Replies
View Related
Apr 8, 2013
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail. I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
View 1 Replies
View Related
Apr 23, 2014
I got a table salaries master I want to extract some info out of and calculate some values. The formula below doesn't work, I'm pretty sure it has to do with [pay period] being a text field. Is there no way to make it work?
'Gross monthly Salary: IIf([Salaries Master]![Pay Period] = "weekly";[Salaries Master]![Daily Wage]*5*52/12,IIf([Salaries Master]![Pay Period] = "monthly";[Salaries Master]![Daily Wage]*20))'
View 10 Replies
View Related
Jan 31, 2014
I have a date in "date to engineering" of 13/ 01/2010 but I am not getting a value in my field which should be 1479 my field is just always returning an empty field
Code:
=IIf(IsDate([date to engineering]),"",IIf(IsDate([date from engineering]),Now()-[date to engineering],[date from engineering]-[date to engineering]))
View 4 Replies
View Related
Dec 15, 2013
I made a report that have 'txtRemarks' field, I just want to get remarks automatically in 'txtRemarks' field based on the other field in report. that is why I used a function like below:
Code:
Private Function Estd_Remarks(Estd_Point As Long) As String
If Me.Estd_Point < 20 Or Me.Estd_Point = 0 Then
Estd_Point = "Earlier Established"
Esle
Estd_Point = "OK"
End If
Estd_Remarks = Estd_Point
End Function
And I wrote in properties 'On Format' event this code below:
Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Estd_Remarks = "Ok" Then
Me.txtRemarks = "Ranked & Sortlisted"
Else
Me.txtRemarks = "Estd_Remarks"
End If
End Sub
When preview the report then it shows
Compile error
Argument optional
View 3 Replies
View Related
Dec 4, 2014
I have a list of all previous employers for a job applicant. I can enter up to 10 but I only want the boxes to show if there is a previous position for that box. So, if there are 3 previous positions, I want to see 3 lines. If there are 10, I want to see 10. I have fields [Company_1]. [Company_2], etc. I only want company 2 to show if the person has 2 previous positions entered into the form.
View 1 Replies
View Related
Sep 5, 2013
I have a report that is looking up min and max quality specifications for a product. The table with the specifications has a Yes/No check box field for when the specs are defined as "Internal". So the table looks like Product/MinValue/MaxValue/Internal
I want the report to display "TEST AND RECORD" when the Internal box is checked where the MinValue and MaxValue (that are stored in the table) would be displayed otherwise.
View 3 Replies
View Related
Oct 2, 2013
I have a report that has multiple fields - 3 of which matter for this discussion -
1.) employee
2.) employer
3.) rateofpay
The employer field contains 1 of 2 options. For the sake of this conversation, lets call it ABC123 and XYZ123
I need to be able to calculate the average rate of pay for ABC123 company, and exclude the rate of pay for XYZ123 company in my report.
I am calculating this in a section footer.
View 2 Replies
View Related
Jun 6, 2014
I am trying to run graph on a report from query but what's happening is query shows name but report on graph shows ID'S of the field .
Below is my query
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet
GROUP BY tblMainTWTTPSheet.txtRootCause, tblMainTWTTPSheet.Date
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null) AND ((tblMainTWTTPSheet.Date) Between [Forms]![frmStratificationOfRootCauses]![startDate] And [Forms]![frmStratificationOfRootCauses]![endDate]));
My bound column on main menu form and back end table is 1
column count 2
column width 0;1
View 1 Replies
View Related
Nov 26, 2004
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
Thank you
View 6 Replies
View Related
Jan 20, 2014
I have a Report based on a query in Access and I need it to sort in a different manner and can't seem to make it do that as the item I want to sort is a formula ...
Right now it sorts like this - first on month and year - oldest to newest - which is great.
Second sort is sorting on the reviewer's name which I don't want, I want it to sort on their monthly total - (number they reviewed within that month) with the one with the most reviews first - but that is a formula (see below).
What I have currently:
January 2013 Monthly Total: 7 % of Grand Total 100%
Reviewer 1 2 28.6%
Reviewer 2 4 57.1%
Reviewer 3 1 14.3%
This is what I would like:
January 2013 Monthly Total: 7 % of Grand Total 100%
Reviewer 2 4 57.1%
Reviewer 1 2 28.6%
Reviewer 3 1 14.3%
The formula is in the Reviewer Name Header and looks like this:
Reviewer Full Name =Count([Review Date]) =Count([Review Date])/[AccessTotalsReview Date1]
View 14 Replies
View Related
Mar 30, 2014
I have four forms named [Information Form], [Evidence Form], [Subject Form], and [Vehicle Form]. The Final Form is a Tabbed Form named [New Incident]. The tabbed form has four tabs, with each tab displaying one of the forms above. I also have a Print Command Button named [Print All] underneath the tabbed portion of the [New Incident] form.
Also, All of the four forms are set to data entry - yes, so that on open they will be clean and read for new record entry. I need the [Print All] button to do, just that, print everything that has or has not just been entered into the four forms, even if some are printed blank because they were not necessary for this particular report. One thin of note is that each form on the tab has a field for the "Incident Number" which will be the same for all four forms.
View 12 Replies
View Related
Jul 1, 2013
I have an access report that there is One column among 9 of them, refuse to give answer.. Its suppose to Sum all the total of each line in the column. See Attachment. This report is group by ID and sort by Shift. The formula is: Sum([Rates]), and the Rate is list in Details Report where the Sum ([Rates]) is located in Id Footer section.
FYI, when I ran this report, it does not know what the Rate is. However, the rate is the formula which listed accordingly in the report details.
View 3 Replies
View Related
Mar 10, 2005
I have a report that has a field (4) whose data is in the form of NO.1.1.2, NO.2.1.1, NO.1.12.3, etc. It is not allowing me to have the report display with those in order, sorting this field is not offered. My report is being grouped by Fields 1, 2, and 3, no sorting. How can the data then displayed in Field 4 be in order of those numbers. Sorry I don't know more about Access and I'm having to do this without support. Thank you
View 3 Replies
View Related
May 8, 2014
I have a field on a report with this as its control source...
-------------------
=(Format(Int([quantity]*(DLookUp("[Plot_times]","[ProductList]","[Code] = Reports![Mask_order_book]![CUST_REF]"))/60),"0") & ":" & Format([quantity]*(DLookUp("[Plot_times]","[ProductList]","[Code] = Reports![Mask_order_book]![CUST_REF]")) Mod 60,"00"))
----------------------
It turns a number like this "120" into elapsed time in hours and minutes like this "2:00"
I have a report with an elapsed time for all of the items. I simply want to SUM it at the report footer, but i guess my control source turns it from a number into text, which then cannot be summed ?
View 4 Replies
View Related
Jul 21, 2005
I've inherited a database with many queries (most based on a single table). There is a requirement to change the name of a few fields in the table. Is there a way to find ALL queries/reports that are utilizing the fields that need to be changed so I know what queries need to be modified?
View 3 Replies
View Related
Mar 21, 2015
A report field data is sourced from a query which displays its values as a string eg "George Renee", however in the reports it displays data as a number eg 42. It is true that the query field obtains its data from a table which gets its data through lookup Wizard. I really want the report to display string type.
View 1 Replies
View Related
Dec 19, 2013
When exporting a report to Excel my Yes/No fields do not export. The Label nor the field itself exports. I am using the Table to create the report, not a query and sorting data within the report.
View 2 Replies
View Related
Mar 27, 2013
I have a report based on a parameter query. One of the parameters is TM Name. This parameter value is selected from a list box. The table on which the query is based also has TM Name as a look-up from a list. So ofcourse the ID is showing in the TM Name field on the report...not the actual name.
I am attempting to use a Dlookup to show the actual name than I will hide the field with the ID. Here's what I have as the control source:
=DLookUp("Name","TM_Names","[TM]=" & [Reports]![Report_TM_Records]![TM])
It is pulling in the value of the first row in the "Name" column of the "TM_Names" table. Doesn't seem to be looking at the value in the TM field on the reports.
My gut feeling is the report needs to be "saved" or "refreshed". I have tried a few macros to do this but I get errors.
View 1 Replies
View Related
