Problem With Parameter Values

C

Chase

Below is the SQL of a query where I have set up a text box "txtFldMth" on a
form to pick up the field. The field Name is "Jan-07" and is in text format.
Even though the text box on the form reads "Jan-07" it still asks me to enter
the parameter Pegasus.[Forms]![frmImport]![txtFldMth]. Can you help resolve
this?

INSERT INTO tblTempITACADataStore ( Retail, Period, SubGroup, Guarantee,
InsType, BusiArea, PremType, SalesMode, Chnnl )
SELECT Pegasus.[Forms]![frmImport]![txtFldMth], tblCalendar.Period,
tblTempResolPrd.ProdCode, tblTempResolPrd.GuaranteeCode, "DIR" AS InsType,
"AR011" AS BusiArea, "PER" AS PremType, "OM" AS SMode,
tblTempResolChnnl.SalesChnnl
FROM (Pegasus INNER JOIN tblTempResolChnnl ON Pegasus.Branch_Name =
tblTempResolChnnl.Branch_Name) INNER JOIN tblTempResolPrd ON
Pegasus.Product_Summary = tblTempResolPrd.Product_Summary, tblTempfrmIn INNER
JOIN tblCalendar ON tblTempfrmIn.EndPeriod = tblCalendar.PeriodNo
GROUP BY tblCalendar.Period, tblTempResolPrd.ProdCode,
tblTempResolPrd.GuaranteeCode, "DIR", "AR011", "PER", "OM",
tblTempResolChnnl.SalesChnnl
HAVING (((Sum([Pegasus].[Forms]![frmImport]![txtFldMth]))<>0));

Thanks

Chase
 
K

KARL DEWEY

Delete Pegasus. from the two lines to read as below ---
SELECT [Forms]![frmImport]![txtFldMth], tblCalendar.Period,

HAVING (((Sum([Forms]![frmImport]![txtFldMth]))<>0));
 
M

Michel Walsh

A parameter cannot be a table name, neither a field name.


You can try a Switch (or a Choose), like:


HAVING 0 <> SUM( SWITCH( param="field1", field1, param="field2", field2,
....., ..., true, defaultFieldToUse) )



but would probably be very slow. Preferable, maybe, to 'write' dynamically
the SQL statement, in a string, and to 'execute' that string.


Vanderghast, Access MVP
 
C

Chase

Karl

The query would not work the error message was "This expression is typed
incorrectly or it is too complex to be evaluated"

Chase

KARL DEWEY said:
Delete Pegasus. from the two lines to read as below ---
SELECT [Forms]![frmImport]![txtFldMth], tblCalendar.Period,

HAVING (((Sum([Forms]![frmImport]![txtFldMth]))<>0));


--
KARL DEWEY
Build a little - Test a little


Chase said:
Below is the SQL of a query where I have set up a text box "txtFldMth" on a
form to pick up the field. The field Name is "Jan-07" and is in text format.
Even though the text box on the form reads "Jan-07" it still asks me to enter
the parameter Pegasus.[Forms]![frmImport]![txtFldMth]. Can you help resolve
this?

INSERT INTO tblTempITACADataStore ( Retail, Period, SubGroup, Guarantee,
InsType, BusiArea, PremType, SalesMode, Chnnl )
SELECT Pegasus.[Forms]![frmImport]![txtFldMth], tblCalendar.Period,
tblTempResolPrd.ProdCode, tblTempResolPrd.GuaranteeCode, "DIR" AS InsType,
"AR011" AS BusiArea, "PER" AS PremType, "OM" AS SMode,
tblTempResolChnnl.SalesChnnl
FROM (Pegasus INNER JOIN tblTempResolChnnl ON Pegasus.Branch_Name =
tblTempResolChnnl.Branch_Name) INNER JOIN tblTempResolPrd ON
Pegasus.Product_Summary = tblTempResolPrd.Product_Summary, tblTempfrmIn INNER
JOIN tblCalendar ON tblTempfrmIn.EndPeriod = tblCalendar.PeriodNo
GROUP BY tblCalendar.Period, tblTempResolPrd.ProdCode,
tblTempResolPrd.GuaranteeCode, "DIR", "AR011", "PER", "OM",
tblTempResolChnnl.SalesChnnl
HAVING (((Sum([Pegasus].[Forms]![frmImport]![txtFldMth]))<>0));

Thanks

Chase
Click to expand...
Click to expand...
 
K

KARL DEWEY

I just noticed that you are telling it to GROUP BY "DIR", "AR011", "PER",
"OM" which are contents of fields. You must group by the field names and not
by content of the field.

--
KARL DEWEY
Build a little - Test a little


Chase said:
Karl

The query would not work the error message was "This expression is typed
incorrectly or it is too complex to be evaluated"

Chase

KARL DEWEY said:
Delete Pegasus. from the two lines to read as below ---
SELECT [Forms]![frmImport]![txtFldMth], tblCalendar.Period,

HAVING (((Sum([Forms]![frmImport]![txtFldMth]))<>0));


--
KARL DEWEY
Build a little - Test a little


Chase said:
Below is the SQL of a query where I have set up a text box "txtFldMth" on a
form to pick up the field. The field Name is "Jan-07" and is in text format.
Even though the text box on the form reads "Jan-07" it still asks me to enter
the parameter Pegasus.[Forms]![frmImport]![txtFldMth]. Can you help resolve
this?

INSERT INTO tblTempITACADataStore ( Retail, Period, SubGroup, Guarantee,
InsType, BusiArea, PremType, SalesMode, Chnnl )
SELECT Pegasus.[Forms]![frmImport]![txtFldMth], tblCalendar.Period,
tblTempResolPrd.ProdCode, tblTempResolPrd.GuaranteeCode, "DIR" AS InsType,
"AR011" AS BusiArea, "PER" AS PremType, "OM" AS SMode,
tblTempResolChnnl.SalesChnnl
FROM (Pegasus INNER JOIN tblTempResolChnnl ON Pegasus.Branch_Name =
tblTempResolChnnl.Branch_Name) INNER JOIN tblTempResolPrd ON
Pegasus.Product_Summary = tblTempResolPrd.Product_Summary, tblTempfrmIn INNER
JOIN tblCalendar ON tblTempfrmIn.EndPeriod = tblCalendar.PeriodNo
GROUP BY tblCalendar.Period, tblTempResolPrd.ProdCode,
tblTempResolPrd.GuaranteeCode, "DIR", "AR011", "PER", "OM",
tblTempResolChnnl.SalesChnnl
HAVING (((Sum([Pegasus].[Forms]![frmImport]![txtFldMth]))<>0));

Thanks

Chase
Click to expand...
Click to expand...
Click to expand...
 
Top