 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / May 2007Query and text box help on formula
Hello, I have a query that takes DT REGULAR and / it by EMPLOYEE TIME and gives me a percent. I found out that its conflicting with other numbers and need to fix it by having it have an if statment or something. it needs to do this [DT REGULAR]/[EMPLOYEE TIME] then here is where im lost. If I have somethig in DT SETUP or DT MEETINGS or DT MAINTENANCE I need to subtract the numbers from those three things from EMPLOYEE TIME. I also have this "=[DT REGULAR]/[EMPLOYEE TIME]" in a text box on a form that just shows the percent and means nothing and I would need to change that formula as well to do the same thing. here is what I have in the field of my query "Delay Percent: Sum([DT REGULAR])/Sum([EMPLOYEE TIME])" and thats what what I have so far... Any help would be greatly appreciated! Thanks, Chad >Hello, I have a query that takes DT REGULAR and / it by EMPLOYEE TIME and >gives me a percent. I found out that its conflicting with other numbers and [quoted text clipped - 10 lines] >Thanks, >Chad I'm not at all sure I understand - you're perhaps assuming we know more about your business rules and fieldnames than we do! But... try Delay Percent: Sum([DT REGULAR]) / (Sum([EMPLOYEE TIME]) - Sum([DT SETUP]) - Sum([DT MEETINGS]) - Sum([DT MAINTENANCE])) I cannot figure out what you mean by doing the same calculation in a textbox and in a calculated field, or what you are referring to by "means nothing"... John W. Vinson [MVP] Try the following ======= All this should be on one line ========= Sum([DT Regular)/Sum([Employee Time]-Nz([DT SetUP],0)- Nz([DT Meetings])-NZ([DT Maintenance],0)) ===== All the above should be on one line ====== I used the NZ (Field, 0) call in case your fields could contain blanks (nulls). If any field were null, then the math would return blank. Nz returns the first argument (the field) unless the first argument is null. If the first argument is null, the NZ returns the value of the second argument (zero in the example) '==================================================== John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '==================================================== > Hello, I have a query that takes DT REGULAR and / it by EMPLOYEE TIME and > gives me a percent. I found out that its conflicting with other numbers and [quoted text clipped - 10 lines] > Thanks, > Chad Ok I explained it wrong im sorry.. I need to have in this order DT SETUP,DT MEETINGS,DT MAINTENANCE be subtracted from EMPLOYEE TIME first. then the number I get that would be my new employee time number and it would get / DT REGULAR but like this [DT REGULAR]/[DT EMPLOYEE TIME] Thanks, > Try the following > [quoted text clipped - 32 lines] > > Thanks, > > Chad Sorry John, The DT SETUP, DT MEETING and the DT MAINTENANCE could all contain 0.00 > Try the following > [quoted text clipped - 32 lines] > > Thanks, > > Chad John, My mistake I dont know what I was doing but I got it working with the Delay Percent: Sum([DT Regular)/Sum([Employee Time]-Nz([DT SetUP],0)-Nz([DT Meetings])-NZ([DT Maintenance],0)) but, now I need to get the text box on a form that does the same thing but dont save it anywhere just for show. How would I write the formula for the control source? > Sorry John, The DT SETUP, DT MEETING and the DT MAINTENANCE could all contain > 0.00 [quoted text clipped - 35 lines] > > > Thanks, > > > Chad >John, >My mistake I dont know what I was doing but I got it working with the [quoted text clipped - 5 lines] >dont save it anywhere just for show. How would I write the formula for the >control source? The Control Source would just be your new calculated fieldname: [Delay Percent] John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.