Prev: Retrieve all record if check box = 0 (Not checked)
Next: Update Query not updating selected table
From: KARL DEWEY on 27 May 2010 17:13
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");
>
From: KARL DEWEY on 27 May 2010 17:40
Left off closing parenthesis --
WHERE Year([TransDate]) = [Forms]![frmQtr]![txtEnterYr]

--
Build a little, test a little.


"AccessKay" wrote:

> Thanks Karl. It didn't work(syntax errors) but I get your logic.
>
> "KARL DEWEY" wrote:
>
> > 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");
> > >
From: AccessKay on 27 May 2010 17:53
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");
> >
First  |  Prev  | 
Pages: 1 2
Prev: Retrieve all record if check box = 0 (Not checked)
Next: Update Query not updating selected table