Prompt for value of field with calculated field?

Infiltrator64

Board Regular
Joined
Dec 19, 2004
Messages
63
I have a calculated field in a query that devides one field by another in the same query. when i run the query it prompys me for Total Swaps and again for Total cases. If i just click OK it runs the query and calculates correctly.

How can i stop the prompt for values of these fields and just run the query and calculate? My code is below and all fields referenced are in the same query as the calcualted field.

Swap %: ([Total Swaps]/[Total Cases])
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Try only use alphanumeric characters in the field name (no spaces or symbols like "%").

If that doesn't make a difference, try adding table references to your field names, i.e.

Swap_Percent: ([Table1]![Total Swaps]/[Table1]![Total Cases])
 
Upvote 0
Are you sure you have the field names right?

As far as I know this sort of thing would only happen if you had incorrect field names.

One good way of making sure you have the correct field names when creating an expression is to use the Expression Builder.

You can access this when in query design view by right clicking and selecting Build...
 
Upvote 0
It's prompting you because there are no fields available in the tables that comprise your query named "Total Swaps" or "Total Cases"

If you put something in brackets, the query will look for a field with the name in brackets, if there isn't one available, it will request a value in its place.

If "Total Swaps" and "Total Cases" are expressions built in this query, replace their names with the expression that define each name.

ex., instead of:
Swap %: ([Total Swaps]/[Total Cases])
use
Swap %: sum([Swaps])/sum([Cases])
 
Upvote 0
Thanks for the feed back. What I actually needed to to for this coulmn is set it to Group by Expression to stop the prompt.
 
Upvote 0
I had same problem....Thanks for posting your solution because I had to change the total from group by to expression as well. :)
 
Upvote 0

Forum statistics

Threads
1,217,383
Messages
6,136,259
Members
450,001
Latest member
KWeekley08

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top