expression i need to write rate -admin costs*driver percentage in.

Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.

I really appreciate the help on this.

"Marshall Barton" wrote:

How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.
--
Marsh
MVP [MS Access]



dasad wrote:
i work at a truck line. i have a table for every load we book with the rate
for the load. From this table i have a query to pay the drivers for each
load (rate * driver percentage/100) In the design of the table rate is
currency, driver percentage is number, intger,and currency. Admin costs is
listed the same as the driver percentage. right now I have a report pulled
from the query to do a "payroll" sheet per driver. I need to deduct an
optional 5% admin costs from the rate prior to the driver percentage.
example: rate is $100.00 minus $5.00 for admin cost, the driver gets paid a
percentage of the $95.00 that is left.


dasad wrote:
Thanks for the help. It did not work. Rate is a dollar amount and the admin
costs is a percentage of that dollar amount.


dasad wrote:
I have three fields in a table rate, admin costs and driver percentage

1. Rate - currency
2. Admin Costs - number intger currency default 5
3. driver percentage - number intger currency default 25

I need an expression to have rate - admin costs * driver percentage.
Right now I have rate * driver percentage but I can not get it to deduct the
5% in admin costs. I need the driver percentage based on the rate minus
admin costs.


"Marshall Barton" wrote:
Not sure what you're trying to do here. Is Admin Costs a
percent? Is Rate a dollar amount?

Maybe this is close??
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.
--
Marsh
MVP [MS Access]



dasad wrote:
Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


"Marshall Barton" wrote:
How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.

I did try your original suggestion and I stil get 25.00 not 23.75

"Marshall Barton" wrote:

Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.
--
Marsh
MVP [MS Access]



dasad wrote:
Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


"Marshall Barton" wrote:
How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.

It gives me the right answer. Are you sure Admin Costs is
spelled correctly or has a value of 5?

I doubt that this is the problem, but you should be aware
the Rate is the name of a built-in function.
--
Marsh
MVP [MS Access]


dasad wrote:
I did try your original suggestion and I stil get 25.00 not 23.75

"Marshall Barton" wrote:
Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.


dasad wrote:
Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


"Marshall Barton" wrote:
How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.

i did a cut and paste and took your formula and pasted in the query under
design mode. I still comeup with the same 25.00

"Marshall Barton" wrote:

It gives me the right answer. Are you sure Admin Costs is
spelled correctly or has a value of 5?

I doubt that this is the problem, but you should be aware
the Rate is the name of a built-in function.
--
Marsh
MVP [MS Access]


dasad wrote:
I did try your original suggestion and I stil get 25.00 not 23.75

"Marshall Barton" wrote:
Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.


dasad wrote:
Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


"Marshall Barton" wrote:
How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.

- Create a new query in design view
- Add the table as your datasource
- Create the following calculated columns:
DriversRate: Format([Rate],"#,##0.00")
AdminPercent: Format([Admin Costs],"#,##0.00")
DriverPercent: Format([Driver Percentage],"#,##0.00")
- Run the query and check the result of your output
- For your example, you should now have the result:
100 5 25
If not, then the problem is with the data.

Regards,
Andreas


dasad wrote:
i did a cut and paste and took your formula and pasted in the query under
design mode. I still comeup with the same 25.00

"Marshall Barton" wrote:


It gives me the right answer. Are you sure Admin Costs is
spelled correctly or has a value of 5?

I doubt that this is the problem, but you should be aware
the Rate is the name of a built-in function.
--
Marsh
MVP [MS Access]


dasad wrote:

I did try your original suggestion and I stil get 25.00 not 23.75

"Marshall Barton" wrote:

Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.


dasad wrote:

Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


"Marshall Barton" wrote:

How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.

I did get the correct results from the query you told me to run. SO the data
is not the problem. Do I need to rename my "rate " column? would that help?

"Andreas" wrote:

- Create a new query in design view
- Add the table as your datasource
- Create the following calculated columns:
DriversRate: Format([Rate],"#,##0.00")
AdminPercent: Format([Admin Costs],"#,##0.00")
DriverPercent: Format([Driver Percentage],"#,##0.00")
- Run the query and check the result of your output
- For your example, you should now have the result:
100 5 25
If not, then the problem is with the data.

Regards,
Andreas


dasad wrote:
i did a cut and paste and took your formula and pasted in the query under
design mode. I still comeup with the same 25.00

"Marshall Barton" wrote:


It gives me the right answer. Are you sure Admin Costs is
spelled correctly or has a value of 5?

I doubt that this is the problem, but you should be aware
the Rate is the name of a built-in function.
--
Marsh
MVP [MS Access]


dasad wrote:

I did try your original suggestion and I stil get 25.00 not 23.75

"Marshall Barton" wrote:

Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.


dasad wrote:

Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


"Marshall Barton" wrote:

How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.

Are you sure that you got:
DriversRate: 100.00
AdminPercent: 5.00
DriverPercent: 25.00

The only explanation I can think of for the results you're
seeing is if AdminPercent were a very small number such as 0
or 0.05. Are you sure the 5 for AdminPercent is really an
integer 5 and not 5% or ???

If you are really getting **exactly** the numbers above,
then Paste my expression into another field in your test
query and double check the results against the data.
--
Marsh
MVP [MS Access]


dasad wrote:
I did get the correct results from the query you told me to run. SO the data
is not the problem. Do I need to rename my "rate " column? would that help?

"Andreas" wrote:
- Create a new query in design view
- Add the table as your datasource
- Create the following calculated columns:
DriversRate: Format([Rate],"#,##0.00")
AdminPercent: Format([Admin Costs],"#,##0.00")
DriverPercent: Format([Driver Percentage],"#,##0.00")
- Run the query and check the result of your output
- For your example, you should now have the result:
100 5 25
If not, then the problem is with the data.


dasad wrote:
i did a cut and paste and took your formula and pasted in the query under
design mode. I still comeup with the same 25.00

"Marshall Barton" wrote:
It gives me the right answer. Are you sure Admin Costs is
spelled correctly or has a value of 5?

I doubt that this is the problem, but you should be aware
the Rate is the name of a built-in function.


dasad wrote:
I did try your original suggestion and I stil get 25.00 not 23.75

"Marshall Barton" wrote:
Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.


dasad wrote:
Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


"Marshall Barton" wrote:
How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.

Now add the following columns to the query:
AdminRate: DriversRate * AdminPercent / 100
DriversPayment: (DriversRate - AdminRate) * DriverPercent / 100

Regards,
Andreas


dasad wrote:
I did get the correct results from the query you told me to run. SO the data
is not the problem. Do I need to rename my "rate " column? would that help?

"Andreas" wrote:


- Create a new query in design view
- Add the table as your datasource
- Create the following calculated columns:
DriversRate: Format([Rate],"#,##0.00")
AdminPercent: Format([Admin Costs],"#,##0.00")
DriverPercent: Format([Driver Percentage],"#,##0.00")
- Run the query and check the result of your output
- For your example, you should now have the result:
100 5 25
If not, then the problem is with the data.

Regards,
Andreas


dasad wrote:

i did a cut and paste and took your formula and pasted in the query under
design mode. I still comeup with the same 25.00

"Marshall Barton" wrote:



It gives me the right answer. Are you sure Admin Costs is
spelled correctly or has a value of 5?

I doubt that this is the problem, but you should be aware
the Rate is the name of a built-in function.
--
Marsh
MVP [MS Access]


dasad wrote:


I did try your original suggestion and I stil get 25.00 not 23.75

"Marshall Barton" wrote:


Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.


dasad wrote:


Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


"Marshall Barton" wrote:


How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.

I just finished jumping up and down and shouting for joy. IT WORKS! IT
WORKS! IT WORKS!.

Thank you so much for all your time and effort. You don't know how much I
appreciate it. I had just got to the point where I was going to give and try
and figure a long way of doing it. Thank you Thank you thank you.
"Andreas" wrote:

Now add the following columns to the query:
AdminRate: DriversRate * AdminPercent / 100
DriversPayment: (DriversRate - AdminRate) * DriverPercent / 100

Regards,
Andreas


dasad wrote:
I did get the correct results from the query you told me to run. SO the data
is not the problem. Do I need to rename my "rate " column? would that help?

"Andreas" wrote:


- Create a new query in design view
- Add the table as your datasource
- Create the following calculated columns:
DriversRate: Format([Rate],"#,##0.00")
AdminPercent: Format([Admin Costs],"#,##0.00")
DriverPercent: Format([Driver Percentage],"#,##0.00")
- Run the query and check the result of your output
- For your example, you should now have the result:
100 5 25
If not, then the problem is with the data.

Regards,
Andreas


dasad wrote:

i did a cut and paste and took your formula and pasted in the query under
design mode. I still comeup with the same 25.00

"Marshall Barton" wrote:



It gives me the right answer. Are you sure Admin Costs is
spelled correctly or has a value of 5?

I doubt that this is the problem, but you should be aware
the Rate is the name of a built-in function.
--
Marsh
MVP [MS Access]


dasad wrote:


I did try your original suggestion and I stil get 25.00 not 23.75

"Marshall Barton" wrote:


Please try my original suggestion:

Driver Pay:
Rate* (1 - [Admin Costs]/100) * ([driver percentage]/100)

and let me know what results you get.


dasad wrote:


Driver Pay: [rate]*[driver percentage]/100
this is the expression I am currently using. I need to add in the admin
costs. I have "cut and paste" all the formulas you both have given me and it
still leaves out the admin costs. Instead of 23.75, I get 25.00.


"Marshall Barton" wrote:


How about that, my original guess was the right one ;-)

Now let's go back and try the expression I posted earlier
and see if we can figure out what about it, or your data,
leads you to think "it doesn't work".

Do you have a data record with a Rate of 100, Admin of 5 and
Dirver 25? Try it and see what result you get, it should be
23.75. If not, pleas post back with the result you did get
along with a Copy/Paste of the **exact** expression you're
using.

Check Andreas posts too. We are both suggesting equivalent
expressions that will produce the same results. He also
makes a good point about using names with spaces or other
funky characters.