Crosstab query from another query with a parameter comming from a

[Guest]

I have created a crosstab query based on another query with date parameter
comming from a form. I get the following message "jet doesnt recognize
[Forms]![frmReportsMain]![Invdate] as a valid field name bla bla bla ...".

initial query

SELECT dbo_TAG.grade,dbo_tag.qty,dbo_tag.desc
FROM dbo_TAG
WHERE dbo_TAG.SOURCEDATE)<=[Forms]![frmReportsMain]![Invdate]

Crosstab query based on initial query

TRANSFORM Sum([initial query].qty) AS PKG
SELECT [initial query].desc
FROM [initial query]
GROUP BY [initial query].desc,
PIVOT [initial query].GRADE;

Did somebody knows how to go over that whitout getting this error.

Thanks

FRED

 

[Duane Hookom]

Open the original query in design view and select Query->Parameters and then
enter
[Forms]![frmReportsMain]![Invdate] Date/Time
Save you query and try again.

 

[Guest]

now I have

initial query

PARAMETERS [Forms]![frmReportsMain]![Inv_date] DateTime;
SELECT dbo_TAG.grade,dbo_tag.qty,dbo_tag.desc
FROM dbo_TAG
WHERE dbo_TAG.SOURCEDATE)<=[Forms]![frmReportsMain]![Invdate]

Crosstab query based on initial query

TRANSFORM Sum([initial query].qty) AS PKG
SELECT [initial query].desc
FROM [initial query]
GROUP BY [initial query].desc,
PIVOT [initial query].GRADE;

It still doesn't work ????

FRED

Open the original query in design view and select Query->Parameters and then
enter
[Forms]![frmReportsMain]![Invdate] Date/Time
Save you query and try again.

--
Duane Hookom
MS Access MVP
--

I have created a crosstab query based on another query with date parameter
comming from a form. I get the following message "jet doesnt recognize
[Forms]![frmReportsMain]![Invdate] as a valid field name bla bla bla ...".

initial query

SELECT dbo_TAG.grade,dbo_tag.qty,dbo_tag.desc
FROM dbo_TAG
WHERE dbo_TAG.SOURCEDATE)<=[Forms]![frmReportsMain]![Invdate]

Crosstab query based on initial query

TRANSFORM Sum([initial query].qty) AS PKG
SELECT [initial query].desc
FROM [initial query]
GROUP BY [initial query].desc,
PIVOT [initial query].GRADE;

Did somebody knows how to go over that whitout getting this error.

Thanks

FRED

 

[Duane Hookom]

I am having trouble seeing your results and "It still doesn't work" isn't
helping much.

Is SOURCEDATE an actual date type field? Do you know that "desc" is a
reserved word in SQL?
How did you get an extra ")" and an extra "," in your queries?

What happens if you try:
PARAMETERS [Forms]![frmReportsMain]![Inv_date] DateTime;
SELECT dbo_TAG.grade,dbo_tag.qty,dbo_tag.[desc] As Description
FROM dbo_TAG
WHERE dbo_TAG.SOURCEDATE<=[Forms]![frmReportsMain]![Invdate]

and then:
TRANSFORM Sum([initial query].qty) AS PKG
SELECT [initial query].Description
FROM [initial query]
GROUP BY [initial query].Description
PIVOT [initial query].GRADE;

--
Duane Hookom
MS Access MVP
--

now I have

initial query

PARAMETERS [Forms]![frmReportsMain]![Inv_date] DateTime;
SELECT dbo_TAG.grade,dbo_tag.qty,dbo_tag.desc
FROM dbo_TAG
WHERE dbo_TAG.SOURCEDATE)<=[Forms]![frmReportsMain]![Invdate]

Crosstab query based on initial query

TRANSFORM Sum([initial query].qty) AS PKG
SELECT [initial query].desc
FROM [initial query]
GROUP BY [initial query].desc,
PIVOT [initial query].GRADE;

It still doesn't work ????

FRED

Open the original query in design view and select Query->Parameters and
then
enter
[Forms]![frmReportsMain]![Invdate] Date/Time
Save you query and try again.

--
Duane Hookom
MS Access MVP
--

I have created a crosstab query based on another query with date
parameter
comming from a form. I get the following message "jet doesnt recognize
[Forms]![frmReportsMain]![Invdate] as a valid field name bla bla bla
...".

initial query

SELECT dbo_TAG.grade,dbo_tag.qty,dbo_tag.desc
FROM dbo_TAG
WHERE dbo_TAG.SOURCEDATE)<=[Forms]![frmReportsMain]![Invdate]

Crosstab query based on initial query

TRANSFORM Sum([initial query].qty) AS PKG
SELECT [initial query].desc
FROM [initial query]
GROUP BY [initial query].desc,
PIVOT [initial query].GRADE;

Did somebody knows how to go over that whitout getting this error.

Thanks

FRED

 

[Guest]

thanks

it works

I am having trouble seeing your results and "It still doesn't work" isn't
helping much.

Is SOURCEDATE an actual date type field? Do you know that "desc" is a
reserved word in SQL?
How did you get an extra ")" and an extra "," in your queries?

What happens if you try:
PARAMETERS [Forms]![frmReportsMain]![Inv_date] DateTime;
SELECT dbo_TAG.grade,dbo_tag.qty,dbo_tag.[desc] As Description
FROM dbo_TAG
WHERE dbo_TAG.SOURCEDATE<=[Forms]![frmReportsMain]![Invdate]

and then:
TRANSFORM Sum([initial query].qty) AS PKG
SELECT [initial query].Description
FROM [initial query]
GROUP BY [initial query].Description
PIVOT [initial query].GRADE;

--
Duane Hookom
MS Access MVP
--

now I have

initial query

PARAMETERS [Forms]![frmReportsMain]![Inv_date] DateTime;
SELECT dbo_TAG.grade,dbo_tag.qty,dbo_tag.desc
FROM dbo_TAG
WHERE dbo_TAG.SOURCEDATE)<=[Forms]![frmReportsMain]![Invdate]

Crosstab query based on initial query

TRANSFORM Sum([initial query].qty) AS PKG
SELECT [initial query].desc
FROM [initial query]
GROUP BY [initial query].desc,
PIVOT [initial query].GRADE;

It still doesn't work ????

FRED

Open the original query in design view and select Query->Parameters and
then
enter
[Forms]![frmReportsMain]![Invdate] Date/Time
Save you query and try again.

--
Duane Hookom
MS Access MVP
--

I have created a crosstab query based on another query with date
parameter
comming from a form. I get the following message "jet doesnt recognize
[Forms]![frmReportsMain]![Invdate] as a valid field name bla bla bla
...".

initial query

SELECT dbo_TAG.grade,dbo_tag.qty,dbo_tag.desc
FROM dbo_TAG
WHERE dbo_TAG.SOURCEDATE)<=[Forms]![frmReportsMain]![Invdate]

Crosstab query based on initial query

TRANSFORM Sum([initial query].qty) AS PKG
SELECT [initial query].desc
FROM [initial query]
GROUP BY [initial query].desc,
PIVOT [initial query].GRADE;

Did somebody knows how to go over that whitout getting this error.

Thanks

FRED