By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,498 Members | 835 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,498 IT Pros & Developers. It's quick & easy.

Access Query Prompting for Value

P: n/a
MX1
I have query2 that sums a set of fields from query1. Works fine. However,
when I try to add two of the calculated fields from query2, I get prompted
for the label I gave the fields. I just hit enter and it gives me my data.
Any thoughts regarding why I'd get prompted for the field name on a
caluclated field when I try to use it in another calcuated field?

thanks! :)
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
MX1 wrote:
I have query2 that sums a set of fields from query1. Works fine.
However, when I try to add two of the calculated fields from query2,
I get prompted for the label I gave the fields. I just hit enter and
it gives me my data. Any thoughts regarding why I'd get prompted for
the field name on a caluclated field when I try to use it in another
calcuated field?


The usual suspect:
If you put a sort or criteria on a field that uses the name of a
calculated field Access doesn't recognice the calculated field
in the ORDER BY or WHERE part of the query and prompts
you for the value.
Instead of reusing the name of the calculated field you have
to repeat its calculation to avoid the prompt.

--
HTH
Karl
*********
Access-FAQ (German): http://www.donkarl.com
Nov 12 '05 #2

P: n/a
can you post the two queries ?

"MX1" <mx*@mx1.abc> wrote in message news:<3M1Qb.101763$Rc4.684691@attbi_s54>...
I have query2 that sums a set of fields from query1. Works fine. However,
when I try to add two of the calculated fields from query2, I get prompted
for the label I gave the fields. I just hit enter and it gives me my data.
Any thoughts regarding why I'd get prompted for the field name on a
caluclated field when I try to use it in another calcuated field?

thanks! :)

Nov 12 '05 #3

P: n/a
MX1
Here you go. I keep getting prompted for Customer? which is correct, but it
also prompts me for Discount Total. I leave it blank, hit enter and it
works. How do I get rid of Discount Total prompt?

Order Query 1:

SELECT tblOrder.CustomerID, tblOrder.AccountID, tblOrder.InvoiceNum,
tblMaster.First, tblMaster.Middle, tblMaster.Last, tblOrder.SellersFee,
nz([PurchaseAmt],0) AS PurchaseAmt1, nz([Percent],0) AS Percent1,
CCur(Format(([PurchaseAmt1]*([Percent1]/100))/2,'Standard')) AS
LineItemSubtotal
FROM tblMaster INNER JOIN (tblOrder LEFT JOIN tblOrderDetail ON
tblOrder.InvoiceNum=tblOrderDetail.InvoiceNum) ON
tblMaster.AccountID=tblOrder.AccountID
WHERE (((tblOrder.CustomerID)=[Customer?]) And ((tblOrder.InvoiceTypeID)=1))
Or (((tblOrder.InvoiceTypeID)=1) And (((tblOrder.CustomerID) Like
[Customer?]) Is Null))
ORDER BY tblOrder.InvoiceNum;

Order Query2:

SELECT DISTINCTROW [OrderQuery1].CustomerID, [OrderQuery1].AccountID,
[OrderQuery1].InvoiceNum, [OrderQuery1].First, [OrderQuery1].Middle,
[OrderQuery1].Last, [OrderQuery1].SellersFee,
Sum([OrderQuery1].LineItemSubtotal) AS [Discount Total],
[SellersFee]+[Discount Total] AS [Invoice Total]
FROM [OrderQuery1]
GROUP BY [OrderQuery1].CustomerID, [OrderQuery1].AccountID,
[OrderQuery1].InvoiceNum, [OrderQuery1].First, [OrderQuery1].Middle,
[OrderQuery1].Last, [OrderQuery1].SellersFee, [SellersFee]+[Discount Total]
ORDER BY [OrderQuery1].InvoiceNum;
"Roger" <le*********@natpro.com> wrote in message
news:8c**************************@posting.google.c om...
can you post the two queries ?

"MX1" <mx*@mx1.abc> wrote in message

news:<3M1Qb.101763$Rc4.684691@attbi_s54>...
I have query2 that sums a set of fields from query1. Works fine. However, when I try to add two of the calculated fields from query2, I get prompted for the label I gave the fields. I just hit enter and it gives me my data. Any thoughts regarding why I'd get prompted for the field name on a
caluclated field when I try to use it in another calcuated field?

thanks! :)

Nov 12 '05 #4

P: n/a
MX1 wrote:
Here you go. I keep getting prompted for Customer? which is correct, but
it also prompts me for Discount Total. I leave it blank, hit enter and it
works. How do I get rid of Discount Total prompt?
...
Order Query2:

SELECT DISTINCTROW [OrderQuery1].CustomerID, [OrderQuery1].AccountID,
[OrderQuery1].InvoiceNum, [OrderQuery1].First, [OrderQuery1].Middle,
[OrderQuery1].Last, [OrderQuery1].SellersFee,
Sum([OrderQuery1].LineItemSubtotal) AS [Discount Total],
[SellersFee]+[Discount Total] AS [Invoice Total]
FROM [OrderQuery1]
GROUP BY [OrderQuery1].CustomerID, [OrderQuery1].AccountID,
[OrderQuery1].InvoiceNum, [OrderQuery1].First, [OrderQuery1].Middle,
[OrderQuery1].Last, [OrderQuery1].SellersFee, [SellersFee]+[Discount
Total] ORDER BY [OrderQuery1].InvoiceNum;


Same thing I mentionned above for sort and order applies to grouping.
You have to replace the name of the calculated field by its expression
if you want to reuse it in another grouped field. At a first glance that
should be:

SELECT DISTINCTROW [OrderQuery1].CustomerID, [OrderQuery1].AccountID,
[OrderQuery1].InvoiceNum, [OrderQuery1].First, [OrderQuery1].Middle,
[OrderQuery1].Last, [OrderQuery1].SellersFee,
Sum([OrderQuery1].LineItemSubtotal) AS [Discount Total],
[SellersFee]+Sum([OrderQuery1].LineItemSubtotal) AS [Invoice Total]
FROM [OrderQuery1]
GROUP BY [OrderQuery1].CustomerID, [OrderQuery1].AccountID,
[OrderQuery1].InvoiceNum, [OrderQuery1].First, [OrderQuery1].Middle,
[OrderQuery1].Last, [OrderQuery1].SellersFee, [SellersFee]+
Sum([OrderQuery1].LineItemSubtotal)
ORDER BY [OrderQuery1].InvoiceNum;

BTW DISTINCTROW is useless if you only have 1 source.

--
HTH
Karl
*********
Access-FAQ (German): http://www.donkarl.com
Nov 12 '05 #5

P: n/a
MX1
Got it. Thanks!

"Karl Donaubauer" <No****@donkarl.com> wrote in message
news:bu************@ID-46617.news.uni-berlin.de...
MX1 wrote:
Here you go. I keep getting prompted for Customer? which is correct, but it also prompts me for Discount Total. I leave it blank, hit enter and it works. How do I get rid of Discount Total prompt?
...
Order Query2:

SELECT DISTINCTROW [OrderQuery1].CustomerID, [OrderQuery1].AccountID,
[OrderQuery1].InvoiceNum, [OrderQuery1].First, [OrderQuery1].Middle,
[OrderQuery1].Last, [OrderQuery1].SellersFee,
Sum([OrderQuery1].LineItemSubtotal) AS [Discount Total],
[SellersFee]+[Discount Total] AS [Invoice Total]
FROM [OrderQuery1]
GROUP BY [OrderQuery1].CustomerID, [OrderQuery1].AccountID,
[OrderQuery1].InvoiceNum, [OrderQuery1].First, [OrderQuery1].Middle,
[OrderQuery1].Last, [OrderQuery1].SellersFee, [SellersFee]+[Discount
Total] ORDER BY [OrderQuery1].InvoiceNum;


Same thing I mentionned above for sort and order applies to grouping.
You have to replace the name of the calculated field by its expression
if you want to reuse it in another grouped field. At a first glance that
should be:

SELECT DISTINCTROW [OrderQuery1].CustomerID, [OrderQuery1].AccountID,
[OrderQuery1].InvoiceNum, [OrderQuery1].First, [OrderQuery1].Middle,
[OrderQuery1].Last, [OrderQuery1].SellersFee,
Sum([OrderQuery1].LineItemSubtotal) AS [Discount Total],
[SellersFee]+Sum([OrderQuery1].LineItemSubtotal) AS [Invoice Total]
FROM [OrderQuery1]
GROUP BY [OrderQuery1].CustomerID, [OrderQuery1].AccountID,
[OrderQuery1].InvoiceNum, [OrderQuery1].First, [OrderQuery1].Middle,
[OrderQuery1].Last, [OrderQuery1].SellersFee, [SellersFee]+
Sum([OrderQuery1].LineItemSubtotal)
ORDER BY [OrderQuery1].InvoiceNum;

BTW DISTINCTROW is useless if you only have 1 source.

--
HTH
Karl
*********
Access-FAQ (German): http://www.donkarl.com

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.