If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Parameter Error for Year 2010
Not sure if anyone knows the answer to this but I welcome any suggestions for
things to try. I have a crosstab query layered off of a Union query. I also have a form with a text box for the user to choose the year and a command button that pulls the report. It works fine if I choose the year 2009 or earlier but if I choose 2010 I receive an error message, “…database engine does not recognize “ as a valid field name or expression. I have not a clue as to what is happening. Thanks for any suggestions. Here’s my query…if this helps any. PARAMETERS [Forms]![frmQtr]![txtEnterYr] Text ( 255 ); TRANSFORM Sum(zqryforTotalsQTR.SumOfLbr_Total_Cost) AS SumOfSumOfLbr_Total_Cost SELECT zqryforTotalsQTR.Category, zqryforTotalsQTR.DGroup, Format([TransDate],"yyyy") AS DYear, Sum(zqryforTotalsQTR.SumOfLbr_Total_Cost) AS [Total Of SumOfLbr_Total_Cost] FROM zqryforTotalsQTR WHERE (((Format([TransDate],"yyyy"))=[Forms]![frmQtr]![txtEnterYr])) GROUP BY zqryforTotalsQTR.Category, zqryforTotalsQTR.DGroup, Format([TransDate],"yyyy") PIVOT "Qtr " & Format([TransDate],"q"); |
#2
|
|||
|
|||
Parameter Error for Year 2010
I've found it best to put any parameters and criteria in another query then
base the crosstab on the first query. Also I'd have to wonder about using Text for the parameter data type. I'd rather have it a number and do something like this: WHERE (((Year([TransDate]))=[Forms]![frmQtr]![txtEnterYr])) Lastly does the form have a plain text box , combo box, or list box? Is it bound or something unbound where the user types in the value? You might need to use the .text or .value something like below expecially if it's a combo box. [Forms]![frmQtr]![txtEnterYr].text -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "AccessKay" wrote: Not sure if anyone knows the answer to this but I welcome any suggestions for things to try. I have a crosstab query layered off of a Union query. I also have a form with a text box for the user to choose the year and a command button that pulls the report. It works fine if I choose the year 2009 or earlier but if I choose 2010 I receive an error message, “…database engine does not recognize “ as a valid field name or expression. I have not a clue as to what is happening. Thanks for any suggestions. Here’s my query…if this helps any. PARAMETERS [Forms]![frmQtr]![txtEnterYr] Text ( 255 ); TRANSFORM Sum(zqryforTotalsQTR.SumOfLbr_Total_Cost) AS SumOfSumOfLbr_Total_Cost SELECT zqryforTotalsQTR.Category, zqryforTotalsQTR.DGroup, Format([TransDate],"yyyy") AS DYear, Sum(zqryforTotalsQTR.SumOfLbr_Total_Cost) AS [Total Of SumOfLbr_Total_Cost] FROM zqryforTotalsQTR WHERE (((Format([TransDate],"yyyy"))=[Forms]![frmQtr]![txtEnterYr])) GROUP BY zqryforTotalsQTR.Category, zqryforTotalsQTR.DGroup, Format([TransDate],"yyyy") PIVOT "Qtr " & Format([TransDate],"q"); |
#3
|
|||
|
|||
Parameter Error for Year 2010
Thanks for all the useful tips and suggestions. I built the select query and
then based my crosstab on it but I was having problems with my form relating to the query. I think my brain is shot so I’m going to try again tomorrow. I appreciate your help. "Jerry Whittle" wrote: I've found it best to put any parameters and criteria in another query then base the crosstab on the first query. Also I'd have to wonder about using Text for the parameter data type. I'd rather have it a number and do something like this: WHERE (((Year([TransDate]))=[Forms]![frmQtr]![txtEnterYr])) Lastly does the form have a plain text box , combo box, or list box? Is it bound or something unbound where the user types in the value? You might need to use the .text or .value something like below expecially if it's a combo box. [Forms]![frmQtr]![txtEnterYr].text -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "AccessKay" wrote: Not sure if anyone knows the answer to this but I welcome any suggestions for things to try. I have a crosstab query layered off of a Union query. I also have a form with a text box for the user to choose the year and a command button that pulls the report. It works fine if I choose the year 2009 or earlier but if I choose 2010 I receive an error message, “…database engine does not recognize “ as a valid field name or expression. I have not a clue as to what is happening. Thanks for any suggestions. Here’s my query…if this helps any. PARAMETERS [Forms]![frmQtr]![txtEnterYr] Text ( 255 ); TRANSFORM Sum(zqryforTotalsQTR.SumOfLbr_Total_Cost) AS SumOfSumOfLbr_Total_Cost SELECT zqryforTotalsQTR.Category, zqryforTotalsQTR.DGroup, Format([TransDate],"yyyy") AS DYear, Sum(zqryforTotalsQTR.SumOfLbr_Total_Cost) AS [Total Of SumOfLbr_Total_Cost] FROM zqryforTotalsQTR WHERE (((Format([TransDate],"yyyy"))=[Forms]![frmQtr]![txtEnterYr])) GROUP BY zqryforTotalsQTR.Category, zqryforTotalsQTR.DGroup, Format([TransDate],"yyyy") PIVOT "Qtr " & Format([TransDate],"q"); |
#4
|
|||
|
|||
Parameter Error for Year 2010
Try it this way --
PARAMETERS [Forms]![frmQtr]![txtEnterYr] Long; .......... WHERE Year([TransDate]=[Forms]![frmQtr]![txtEnterYr] -- Build a little, test a little. "AccessKay" wrote: Not sure if anyone knows the answer to this but I welcome any suggestions for things to try. I have a crosstab query layered off of a Union query. I also have a form with a text box for the user to choose the year and a command button that pulls the report. It works fine if I choose the year 2009 or earlier but if I choose 2010 I receive an error message, “…database engine does not recognize “ as a valid field name or expression. I have not a clue as to what is happening. Thanks for any suggestions. Here’s my query…if this helps any. PARAMETERS [Forms]![frmQtr]![txtEnterYr] Text ( 255 ); TRANSFORM Sum(zqryforTotalsQTR.SumOfLbr_Total_Cost) AS SumOfSumOfLbr_Total_Cost SELECT zqryforTotalsQTR.Category, zqryforTotalsQTR.DGroup, Format([TransDate],"yyyy") AS DYear, Sum(zqryforTotalsQTR.SumOfLbr_Total_Cost) AS [Total Of SumOfLbr_Total_Cost] FROM zqryforTotalsQTR WHERE (((Format([TransDate],"yyyy"))=[Forms]![frmQtr]![txtEnterYr])) GROUP BY zqryforTotalsQTR.Category, zqryforTotalsQTR.DGroup, Format([TransDate],"yyyy") PIVOT "Qtr " & Format([TransDate],"q"); |
Thread Tools | |
Display Modes | |
|
|