Query and text box help on formula

C

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

John W. Vinson

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

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

John Spencer

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
'====================================================
 
C

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,

John Spencer said:
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
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
 
C

Chad

Sorry John, The DT SETUP, DT MEETING and the DT MAINTENANCE could all contain
0.00

John Spencer said:
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
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
 
C

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?

Chad said:
Sorry John, The DT SETUP, DT MEETING and the DT MAINTENANCE could all contain
0.00

John Spencer said:
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
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
 
J

John W. Vinson

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?

The Control Source would just be your new calculated fieldname:

[Delay Percent]


John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query expression help 11
Percent help 3
Update query help 3
Query error help 6
Formula in text box on RPT help... 9
Need help with Report formula 6
Formula help for percent 4
VBA Help on Loop 1

Top