Access Query Prompting for Value

[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! :)

[Karl Donaubauer]

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

[Roger]

can you post the two queries ?

"MX1" <mx*@mx1.abc> wrote in message news:<3M1Qb.101 763$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! :)

[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.Custom erID, tblOrder.Accoun tID, tblOrder.Invoic eNum,
tblMaster.First , tblMaster.Middl e, tblMaster.Last, tblOrder.Seller sFee,
nz([PurchaseAmt],0) AS PurchaseAmt1, nz([Percent],0) AS Percent1,
CCur(Format(([PurchaseAmt1]*([Percent1]/100))/2,'Standard')) AS
LineItemSubtota l
FROM tblMaster INNER JOIN (tblOrder LEFT JOIN tblOrderDetail ON
tblOrder.Invoic eNum=tblOrderDe tail.InvoiceNum ) ON
tblMaster.Accou ntID=tblOrder.A ccountID
WHERE (((tblOrder.Cus tomerID)=[Customer?]) And ((tblOrder.Invo iceTypeID)=1))
Or (((tblOrder.Inv oiceTypeID)=1) And (((tblOrder.Cus tomerID) Like
[Customer?]) Is Null))
ORDER BY tblOrder.Invoic eNum;

Order Query2:

SELECT DISTINCTROW [OrderQuery1].CustomerID, [OrderQuery1].AccountID,
[OrderQuery1].InvoiceNum, [OrderQuery1].First, [OrderQuery1].Middle,
[OrderQuery1].Last, [OrderQuery1].SellersFee,
Sum([OrderQuery1].LineItemSubtot al) 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*********@na tpro.com> wrote in message
news:8c******** *************** ***@posting.goo gle.com...

can you post the two queries ?

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

news:<3M1Qb.101 763$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! :)

[Karl Donaubauer]

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].LineItemSubtot al) 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].LineItemSubtot al) AS [Discount Total],
[SellersFee]+Sum([OrderQuery1].LineItemSubtot al) AS [Invoice Total]
FROM [OrderQuery1]
GROUP BY [OrderQuery1].CustomerID, [OrderQuery1].AccountID,
[OrderQuery1].InvoiceNum, [OrderQuery1].First, [OrderQuery1].Middle,
[OrderQuery1].Last, [OrderQuery1].SellersFee, [SellersFee]+
Sum([OrderQuery1].LineItemSubtot al)
ORDER BY [OrderQuery1].InvoiceNum;

BTW DISTINCTROW is useless if you only have 1 source.

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

[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].LineItemSubtot al) 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].LineItemSubtot al) AS [Discount Total],
[SellersFee]+Sum([OrderQuery1].LineItemSubtot al) AS [Invoice Total]
FROM [OrderQuery1]
GROUP BY [OrderQuery1].CustomerID, [OrderQuery1].AccountID,
[OrderQuery1].InvoiceNum, [OrderQuery1].First, [OrderQuery1].Middle,
[OrderQuery1].Last, [OrderQuery1].SellersFee, [SellersFee]+
Sum([OrderQuery1].LineItemSubtot al)
ORDER BY [OrderQuery1].InvoiceNum;

BTW DISTINCTROW is useless if you only have 1 source.

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