enter parameter value

G

Guest

I created a query where I did some calculations and then named the field
(example: pounds: tons*2000). All the fields I created like this cause a
"enter parameter value" prompt to appear. I read that this is because the
query is referencing something it doesn't recognize. How do I fix this? The
SQL statement is as follows:

SELECT tblCrates.Location, tblMaster.Month, tblCrates.[Number of Crates],
tblLactation.[Number of sows farrowed], tblLactation.[Number of litters
weaned], tblLactation.[Average age at weaning], tblMaster.Ingredient, [Number
of sows farrowed]*[Average age at weaning] AS [Lactation Days],
tblMaster.[Volume \ Tons], [Pounds]/[Lactation Days] AS [ADFI Per Lact Day],
([Number of sows farrowed]+[Number of litters weaned])/2 AS [Average Farrowed
and Weaned], tblLactation.[Days per month], [Days per month]*[Number of
Crates] AS [Crate Days], [Pounds]/[Crate Days] AS [ADFI crate days,lb],
([ADFI Per Lact Day]+[ADFI crate days,lb])/2 AS [ADFI,lb], [Volume \
Tons]*2000 AS Pounds, tblLactation.Year
FROM (tblCrates INNER JOIN tblMaster ON tblCrates.Location =
tblMaster.Location) INNER JOIN tblLactation ON (tblMaster.Month =
tblLactation.Month) AND (tblCrates.Location = tblLactation.Location)
GROUP BY tblCrates.Location, tblMaster.Month, tblCrates.[Number of Crates],
tblLactation.[Number of sows farrowed], tblLactation.[Number of litters
weaned], tblLactation.[Average age at weaning], tblMaster.Ingredient, [Number
of sows farrowed]*[Average age at weaning], tblMaster.[Volume \ Tons],
[Pounds]/[Lactation Days], ([Number of sows farrowed]+[Number of litters
weaned])/2, tblLactation.[Days per month], [Days per month]*[Number of
Crates], [Pounds]/[Crate Days], ([ADFI Per Lact Day]+[ADFI crate days,lb])/2,
[Volume \ Tons]*2000, tblLactation.Year
HAVING (((tblMaster.Ingredient)="Lactation" Or
(tblMaster.Ingredient)="gestation"));

Thank you for your help.
 
S

Steve Schapel

Vulcan,

I think the main problem you are referring to is caused by the fact that
you are using the name of the calculated field in other calculated
fields. For example, [Pounds]/[Lactation Days] AS [ADFI Per Lact Day]
uses the field Pounds as part of the calculated field [ADFI Per Lact
Day] but the field Pounds is not aliased in the query until after this,
being [Volume \ Tons]*2000 AS Pounds. I think it will work ok if you
were to put the Pounds field before (i.e. to the left in the query
design grid) the [ADFI Per Lact Day] field, for the sake of the example.
Otherwise, you could re-define it as...
ADFI Per Lact Day: [Volume \ Tons]*2000/[Lactation Days]
.... so your calculated fields are only referencing fields from the
underlying tables.

Another few comments, not directly related to your question...
- I would recommend against the use of characters like the \ in
[Volume \ Tons] and the , in [ADFI,lb]
- Month and Year are Reserved Words (i.e. have a special meaning) in
Access, and as such should not be used as the names of fields or controls
- You have a Group By clause in your query that seems to include all
fields in the query, and the query contains no aggregate functions, so
this is puzzling, and unnecessarily complicated. If you are using the
query design view, just go to the View menu, and untick Totals.

--
Steve Schapel, Microsoft Access MVP

I created a query where I did some calculations and then named the field
(example: pounds: tons*2000). All the fields I created like this cause a
"enter parameter value" prompt to appear. I read that this is because the
query is referencing something it doesn't recognize. How do I fix this? The
SQL statement is as follows:

SELECT tblCrates.Location, tblMaster.Month, tblCrates.[Number of Crates],
tblLactation.[Number of sows farrowed], tblLactation.[Number of litters
weaned], tblLactation.[Average age at weaning], tblMaster.Ingredient, [Number
of sows farrowed]*[Average age at weaning] AS [Lactation Days],
tblMaster.[Volume \ Tons], [Pounds]/[Lactation Days] AS [ADFI Per Lact Day],
([Number of sows farrowed]+[Number of litters weaned])/2 AS [Average Farrowed
and Weaned], tblLactation.[Days per month], [Days per month]*[Number of
Crates] AS [Crate Days], [Pounds]/[Crate Days] AS [ADFI crate days,lb],
([ADFI Per Lact Day]+[ADFI crate days,lb])/2 AS [ADFI,lb], [Volume \
Tons]*2000 AS Pounds, tblLactation.Year
FROM (tblCrates INNER JOIN tblMaster ON tblCrates.Location =
tblMaster.Location) INNER JOIN tblLactation ON (tblMaster.Month =
tblLactation.Month) AND (tblCrates.Location = tblLactation.Location)
GROUP BY tblCrates.Location, tblMaster.Month, tblCrates.[Number of Crates],
tblLactation.[Number of sows farrowed], tblLactation.[Number of litters
weaned], tblLactation.[Average age at weaning], tblMaster.Ingredient, [Number
of sows farrowed]*[Average age at weaning], tblMaster.[Volume \ Tons],
[Pounds]/[Lactation Days], ([Number of sows farrowed]+[Number of litters
weaned])/2, tblLactation.[Days per month], [Days per month]*[Number of
Crates], [Pounds]/[Crate Days], ([ADFI Per Lact Day]+[ADFI crate days,lb])/2,
[Volume \ Tons]*2000, tblLactation.Year
HAVING (((tblMaster.Ingredient)="Lactation" Or
(tblMaster.Ingredient)="gestation"));

Thank you for your help.
Click to expand...
 
T

Tanis

Is [Volume \ Tons] really a field name? Change it to VolumeTons or a
least something without the \ in it. Incidentally, it is nice to hav
meaningful field names, but, avoid spaces like the plague. Not sur
about the , in the field name [ADFI crate days,lb] either
 
Top