A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Parameter Error for Year 2010



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2010, 08:53 PM posted to microsoft.public.access.queries
AccessKay
external usenet poster
 
Posts: 106
Default 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  
Old May 27th, 2010, 08:55 PM posted to microsoft.public.access.queries
AccessKay
external usenet poster
 
Posts: 106
Default Parameter Error for Year 2010

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");

  #3  
Old May 27th, 2010, 09:04 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default 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");

  #4  
Old May 27th, 2010, 09:07 PM posted to microsoft.public.access.queries
AccessKay
external usenet poster
 
Posts: 106
Default Parameter Error for Year 2010

Hi Jerry,
I’m thinking about what you said about using the crosstab query’s Column
Heading to define the fields in my report. How could I do this? I tried to
reference the form under the column heading but received an error message
about it being too complicate to perform…

I also now have some idea about why the year 2010 won’t work with my
form/report. It works with my query when I enter the year in the parameter
(asking for form). But I’m thinking that because I only have data for one
quarter in 2010, that my report doesn’t know how to handle the missing fields
for Q2,Q3,and Q4. Have you had any experience with this?

Here is my SQL again:
PARAMETERS [Forms]![frmQtr]![txtEnterYr] Long;
TRANSFORM Sum(Nz([SumOfLbr_Total_Cost])) AS TotalCost
SELECT qryforTotals.Category, qryforTotals.DGroup,
Sum(Nz([SumOfLbr_Total_Cost])) AS [Total Of SumOfLbr_Total_Cost]
FROM qryforTotals
WHERE (((Year([TransDate]))=[Forms]![frmQtr]![txtEnterYr]))
GROUP BY qryforTotals.Category, qryforTotals.DGroup
PIVOT "Qtr " & Format([TransDate],"q");

Thanks again!


"Jerry Whittle" wrote:

Forms and reports based on crosstab queries can be a problem. A crosstab
could produce many fields with different names and a form or report is
expecting just certain fields and names.

If you know all the fields that you want on the form or report, you can use
the crosstab query's Column Headings property to define them. A form or
report will work then.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"AccessKay" wrote:

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");

  #5  
Old May 27th, 2010, 09:13 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default Parameter Error for Year 2010

Forms and reports based on crosstab queries can be a problem. A crosstab
could produce many fields with different names and a form or report is
expecting just certain fields and names.

If you know all the fields that you want on the form or report, you can use
the crosstab query's Column Headings property to define them. A form or
report will work then.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"AccessKay" wrote:

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");

  #6  
Old May 27th, 2010, 10:13 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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");

  #7  
Old May 27th, 2010, 10:40 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Parameter Error for Year 2010

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");

  #8  
Old May 27th, 2010, 10:53 PM posted to microsoft.public.access.queries
AccessKay
external usenet poster
 
Posts: 106
Default 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");

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.