I created the following expression on a report, under the details, in a text box:
=nz(pathjbo50,0)/nz(pathjbo50,0)+nz(pathcol50,0)+nz(pathpp50,0)+nz( pathlm50,0)
what I want is to find out what percentage pathjbo50 is of the sum of the other 4 fields.
but I keep getting an "Error" message. How do I get this percentage?
Where is the best place to find literature on writting expressions?
Hello All,
I am not sure what I am doing wrong. I am trying to get a percentage of two numbers and the expression is not comming out right. This is the expression:
Expr1: [Ontime PU.CountOfP Performance]-[Late PU.CountOfP Performance]/[Ontime PU.CountOfP Performance]
Here is what it should do:
1242-68/1242=0.94524959742351046698872785829308 (which would be 95%)
For reason it is not doing this?????
Any Ideas????
Thanks
For several days now, I have been trying to write an expression, in a report, to get a percentage. I am new to Access so I don't completely get it yet. This is the expression I wrote (only works sometimes):
=[pathjbo50]/([pathjbo50]+[pathcol50]+[pathpp50])
This expression only works when all the fields are populated. If any of the fields are empty, I get nothing.
Please help!
I want a report to tell me a percentage of records. i have a report that records "records that are past date". I want the report to tell me the percentage of total records that are past date.
I have a query that shows only past date records
and a query that shows all records
how do i do this?
I'm having difficulty in adding decimal amounts to a percentage expression.On a form in a text box I have a percentage expression of 73.38%. In a combo box I have a drop down menu of: 0.75, 0.5, 0.25, 0, -.25, -.5, -.75With another text box I would like to have the decimal amounts added to the percentage. For example, 73.38% + .5..Which will equal in the text box: 73.88%
View 2 Replies View RelatedI'm trying to create an expression to calculate percentage.
The fields, Value A and Value B might both have null values, but I do not want Access to treat nulls as zero (as in the Nz() fn). How do I get the expression to exclude any nulls and not calculate for those rows, but set the calculated value for a row with null A or B to zero.
I want it to set no calculated value to zero so I don't get overflow errors and can therefore order the data by the calculated field.
I have a database functioning pretty well.
All I did was move the files from one computer to another via USB stick and now I get this alarm when I try to run this particular macro.
It is a macro to set a value to specific field.
The Macro uses a condition [Forms]![tblCustomerCall]![Alarm Number]="000".
If the condition is met then it is supposed to set a value to a field in the same form.
[Forms]![tblCustomerCall]![Alarm Descrption] is the item to set.
Left("TV Parity Alarm",50) is the expression.
So if 000 is entered in then the text TV Parity Alarm is filled into the Alarm description field.
It was working perfect until I moved the files to another computer.
I have Office SP2 installed and have updated office with everything available.
Please help.
Thanks
I'm relavtively new to databases but after a steep learning curve (being left with many databases created by my old manager with no instructions on use) I'm getting there.What I am trying to do is pull out the date from records in a field called "Model_ID". The Model_ID field contains this kind of information:
Endscopy20120726JSmith
GISurgery20120521JDoe
I want to bring back "20120726" or "20120521". In excel I can do this with the mid and find functions but find doesn't work in access. I've tried combining the mid function with instr function but it comes back as too complex!
I've got a Table with a string of data delimited by an "*". The first part of the string before the "*" can be variable in length, so if I use a LEFT expression to break apart the 2 parts of the string on each side of the "*", my results are incorrect. In MS Excel, I can use the following formula to solve the problem:
=LEFT(B6,(FIND("*",B6)))
You are just substituting the number of positions with a Find command that looks for the "*", and returns anything left of the "*". Unfortunately, the brilliant programmers at Microsoft, once again, don't have an Excel function in Access. how to recreate that functionality in a table in Access?
I am trying to run a query to print invoices that are 30, 60, and 90 days past due. What expression do I use to search for records that were purchased 30, 60, 90 days prior? I was able to find the expression to add time for the due date but was unable to find the expression to subtract time for my search. Thanks for any help!! Beck
View 1 Replies View RelatedI one of my tables, I have a date field (DateCompleted) with the format: mm/dd/yyyy
Now, I want to create a query which would create another field (DateExpected) by using the date in the above table and adding 5 months to the date.
The only issue is that for the new field in the query I want it just to have mm/yyyy format.
For example, if the original date in the table is 04/05/2006, after running the query, I would have a new field 09/2006.
Does anyone know the exact expression I would put in the query? Thanks. The expression would be put in the "build" area.
Thanks for your help!!
I have a database of students that have taken tests. That database includes how many attempts it took them to pass. I have successfully generated reports that sort the exams by type, date, student, etc. I would like to also somehow insert a formula to show the pass rate of those exams on the top of the report. I know I can get into design view and build an expression, but how would I build an expression that would create a pass rate percentage based on whether or not a student passed an exam on the first attempt.
I have a DB with a column titled "Attempt" and the options "First, Second, Third, Fourth". I would like the expression to generate a percentage of students that have "First" in their box (as opposed to anything else) out of the total number of student records.
We need to track student pass rates in my school for a re-certification process.
I am using expression builder to specify a field in a report but it is acting more like a filter.So I have a report based on a query. However I want to add a field that is not in the query but is in a related table - called tblAgent.
So using expression builder I select the tblAgent in Expression Elements and then select the field from this table. This creates the expression =[Agent]![AgentAddress] however when I try to run the report it asks for a Agent parameter? Do I need to go back to reports 101?
I am trying to create an expression in a query to sum only the # of hours a student attended between two date fields. I do not want to use a parameter because each student has different start and midpoint dates and I need to see all of them in one list.These are the fields I'm using in the query:
Student Name
Start Date
Midpoint Date
Hours
It keeps giving me "0" or if I move the () around it says the correct syntax is [NOT]
Expr1: Sum([Hours]) between [Start Date] & [Midpoint]))
I have Territory Mgmt database. Sometimes when I get a new lead I need to quickly find other leads in the database that are closest to it.
I am currently not tracking latitude/longitude etc. Do I need to? Is there a way to set certain location marks by address?
If so, once I put in the latitudel/longitude how do I create a search to find the nearest?
Good day all
Heres what im trying to do:
in one textfield i have =Count(*)
this returns all the records in my db.
now i want to split it up. i want to count all the records for each month.
my field name is datein_now but its just a normal textfield and not a date field.
example in field: 05/02/02 10:24:31 AM.
also got a datein textfield:05/02/02
i had
Count(*) where datein_now between 05/05/01 and 05/05/31
please help
lee
I have a table that documents the result of unit inspections. The data is Date inspected (once/month), Unit, and about 12 yes/no fields.
I want to run a query that shows the percentage compliant (yes) of all of the categories per month. I also want to be able to graph the results for one year per month to show trends.
What is the best way to do this?
Thanks,
Jason
Hi, i have poduced a report and i want to calculate some percentages. Some of the fields i have are:
Total Sales Clothes Shoes Pants
200 20 120 60
% 100% 10% 60% 30%
As you can see in total 200 sales were made, 20 clothes sales, 120 shoe sales etc and below it a percentage of each of the sale types. I have put this ' =Sum([Clothes]/[Total Sales])*100 ' in to calculate the percentage...but it does not work...Any help would me much appreciated :)
*This hasn't shown up very well...hope you understand it?!
Hello, I'm stuggling to work out how to go about setting up a formula.
I have 497 students 186 are Male 311 are Female. How do i work out the percentage of Male and Female students? I have no idea how to do the Math behind this so cannot start to add it to my database. If anyone can show me how to calculate this it would be really helpful.
regards
Pete
I need to be able to store percentages in an Access 97 table. I have set the data type to double and 4 decimal places, but when I enter .14, Access rounds to 0 and stores it as zeros.
What am I doing wrong?
In one of my queries I calculate a percentage based on a change from one year to the next; however, sometimes there will not be any prior year data hence the percentage is undefined (i.e. current year amount/0=undefined). To stop the query from displaying an error when this is the case I wrote the following:
CapitalSurplusChange: IIf([P/Y Operating Data]![CapitalandSurplus]=0,"N/A",(Nz([C/Y Operating Data]![CapitalandSurplus],0)-Nz([P/Y Operating Data]![CapitalandSurplus],0))/Nz([P/Y Operating Data]![CapitalandSurplus],0))
I have the properties of the field set to percentage, but the calculation does not display as a percentage but as a long decimal. If I take out the Iif the percentages display fine however there are errors for the undefined calculations (hence why I inserted the Iif). How can I amend the code above to only display the "N/A" answers as text. I know about the formatpercent function, however I am exporting this output to excel and this when using the formatpercent or formatnumber operation the data still displays as text and not a number. Any ideas?
Hi,
I am creating a database to help me monitor the students in my class. I have a query that takes a student mark from one table and compares it with the maximum mark available for the assessment/homework. In a calculated field called Percent I divide the mark by the maximum mark and then multiply by 100. This gives me the percent. I then have another calculated field which awards a grade from A to C and if they don't achieve a C they are awarded a Fail. The IIf statement looks like this:
Grade: IIf([percent]>="70","A",IIf([percent]>="60","B",IIf([percent]>="50","C","Fail")))
Here is the bit I don't understand. If a student gets 99 % they are awarded an A (cut offs are: 70% and above for an A, 60% to 69 % for a B and 50% to 59% for a C. 49% and below gets a fail). If they get 65% they get a B... all appears fine. But, if they should happen to get 100% the query says "Fail". Can someone shed any light?
Regards
Kevin
Hi,
I have a database which contains the following column: -
TYPE OF COVER
This column has various covers in it i.e. 'COMP', 'TPFT', 'TP' etc
I would like to be able to run a query that shows the following columns: -
TYPE OF COVER , TOTAL NUMBER OF EACH COVER, PERCENTAGE TOTAL
I am new to SQL and would be grateful for some advice.
Regards
I have a Query that gives me the output as follows:
Code___DateofReview___Response___CountofReponse
AAA____12/19/2006_____0_________3
AAA____12/20/2006_____1_________2
AAA____12/20/2006_____0_________1
AAA____1/4/2007_______1_________2
The Field "Response" can either be 0 or 1. The Variable "Countofresponse" gives me the Count of response when it is 0 and 1 for a given Date of Review.
How do I modify this query so I can get a Percentage value added to this list. I would like to see the following result but not sure how to arrive at this:
Code___DateofReview___Response___CountofReponse___ PercentValue
AAA____12/19/2006_____0_________3________________100%
AAA____12/20/2006_____1_________2________________66%
AAA____12/20/2006_____0_________1________________34%
AAA____1/4/2007_______1_________2________________100%
Can anyone help please?
I have about 40 queries based on employed trainees from different groups(MMF, MKO, CNP,...). Now, what I'm trying to do is find the percentage of trainees employed from the total trainees, % employed in community from total trainees, % employed at wuskwatim from total trainees,... and so on.
If you take a look at my DB, you will find I have have MANY queries made. Is there an easier way to find this kind of information?
Any suggestions would be appreciated.
18448
In a report, I need to calculate the percentage of clients that respond "Yes" to a question on a survey. I need to create a query that will return the count of the number of "Yes" responses, and the number of total responses.
For example, if 10 clients complete the survey, and seven respond "Yes", I need the 2 fields in the query to be 7 and 10. So far, I have only been able to do this using multiple queries.
Thank you.