Criteria Expression in a Crosstab Query Column Heading

[Guest]

I have created a crosstab query that calculates the sum of points by dates
for employees. I have a column heading in my crosstab query that is Date. I
am trying to do an expression in my Criteria so I can select a date range. I
currently have in my criteria...

Between [Enter First Date] And [Enter Second Date]

when I do this it gives the error "The Microsoft Jet Engine Does Not
Recognize "[Enter First Date]" as a valid field name or expression".

I think I have to include the name of the field in my expression somewhere
but I cannot get it to work correct. Anyone know the proper experssion to
put in the criteria of a Crosstab Column Heading? Thanks in Advance.

 

[Marshall Barton]

I have created a crosstab query that calculates the sum of points by dates
for employees. I have a column heading in my crosstab query that is Date. I
am trying to do an expression in my Criteria so I can select a date range. I
currently have in my criteria...

Between [Enter First Date] And [Enter Second Date]

when I do this it gives the error "The Microsoft Jet Engine Does Not
Recognize "[Enter First Date]" as a valid field name or expression".

I think I have to include the name of the field in my expression somewhere
but I cannot get it to work correct. Anyone know the proper experssion to
put in the criteria of a Crosstab Column Heading? Thanks in Advance.

Crosstab queries do not allow you to ignore parameter
declarations the way simple select queries do.

Use the Parameters item on the Query menu to specify the
parameter and its data type.

 

[Duane Hookom]

You have to enter your parameters and data types in Query->Parameters
[Enter First Date] Date/Time
[Enter Second Date] Date/Time

 

[Guest]

thanks, I have now done that and after i enter my date range it still
displays all dates. Shouldn't that now just show me the date range i
selected?

You have to enter your parameters and data types in Query->Parameters
[Enter First Date] Date/Time
[Enter Second Date] Date/Time


--
Duane Hookom
MS Access MVP
--

I have created a crosstab query that calculates the sum of points by dates
for employees. I have a column heading in my crosstab query that is Date.
I
am trying to do an expression in my Criteria so I can select a date range.
I
currently have in my criteria...

Between [Enter First Date] And [Enter Second Date]

when I do this it gives the error "The Microsoft Jet Engine Does Not
Recognize "[Enter First Date]" as a valid field name or expression".

I think I have to include the name of the field in my expression somewhere
but I cannot get it to work correct. Anyone know the proper experssion to
put in the criteria of a Crosstab Column Heading? Thanks in Advance.

 

[Duane Hookom]

Did you leave the "Between...." in the criteria? If you can't figure this
out, post your sql view back to the news group.

--
Duane Hookom
MS Access MVP
--

thanks, I have now done that and after i enter my date range it still
displays all dates. Shouldn't that now just show me the date range i
selected?

You have to enter your parameters and data types in Query->Parameters
[Enter First Date] Date/Time
[Enter Second Date] Date/Time


--
Duane Hookom
MS Access MVP
--

I have created a crosstab query that calculates the sum of points by
dates
for employees. I have a column heading in my crosstab query that is
Date.
I
am trying to do an expression in my Criteria so I can select a date
range.
I
currently have in my criteria...

Between [Enter First Date] And [Enter Second Date]

when I do this it gives the error "The Microsoft Jet Engine Does Not
Recognize "[Enter First Date]" as a valid field name or expression".

I think I have to include the name of the field in my expression
somewhere
but I cannot get it to work correct. Anyone know the proper experssion
to
put in the criteria of a Crosstab Column Heading? Thanks in Advance.

 

[Guest]

Here is my sql view...

PARAMETERS [Enter First Date] DateTime, [Enter Second Date] DateTime;
TRANSFORM Sum(absenteetest.Point) AS SumOfPoint
SELECT absenteetest.Employee, Sum(absenteetest.Point) AS [Total Of Point]
FROM absenteetest
GROUP BY absenteetest.Employee
PIVOT Format([Date],"Short Date");

Did you leave the "Between...." in the criteria? If you can't figure this
out, post your sql view back to the news group.

--
Duane Hookom
MS Access MVP
--

thanks, I have now done that and after i enter my date range it still
displays all dates. Shouldn't that now just show me the date range i
selected?

You have to enter your parameters and data types in Query->Parameters
[Enter First Date] Date/Time
[Enter Second Date] Date/Time


--
Duane Hookom
MS Access MVP
--

I have created a crosstab query that calculates the sum of points by
dates
for employees. I have a column heading in my crosstab query that is
Date.
I
am trying to do an expression in my Criteria so I can select a date
range.
I
currently have in my criteria...

Between [Enter First Date] And [Enter Second Date]

when I do this it gives the error "The Microsoft Jet Engine Does Not
Recognize "[Enter First Date]" as a valid field name or expression".

I think I have to include the name of the field in my expression
somewhere
but I cannot get it to work correct. Anyone know the proper experssion
to
put in the criteria of a Crosstab Column Heading? Thanks in Advance.

 

[Duane Hookom]

You don't have any criteria so your query will include all records. Your
first post suggested you had this and seem to have removed it.


--
Duane Hookom
MS Access MVP
--

Here is my sql view...

PARAMETERS [Enter First Date] DateTime, [Enter Second Date] DateTime;
TRANSFORM Sum(absenteetest.Point) AS SumOfPoint
SELECT absenteetest.Employee, Sum(absenteetest.Point) AS [Total Of Point]
FROM absenteetest
GROUP BY absenteetest.Employee
PIVOT Format([Date],"Short Date");

Did you leave the "Between...." in the criteria? If you can't figure this
out, post your sql view back to the news group.

--
Duane Hookom
MS Access MVP
--

thanks, I have now done that and after i enter my date range it still
displays all dates. Shouldn't that now just show me the date range i
selected?

:

You have to enter your parameters and data types in Query->Parameters
[Enter First Date] Date/Time
[Enter Second Date] Date/Time


--
Duane Hookom
MS Access MVP
--

I have created a crosstab query that calculates the sum of points by
dates
for employees. I have a column heading in my crosstab query that is
Date.
I
am trying to do an expression in my Criteria so I can select a date
range.
I
currently have in my criteria...

Between [Enter First Date] And [Enter Second Date]

when I do this it gives the error "The Microsoft Jet Engine Does Not
Recognize "[Enter First Date]" as a valid field name or expression".

I think I have to include the name of the field in my expression
somewhere
but I cannot get it to work correct. Anyone know the proper
experssion
to
put in the criteria of a Crosstab Column Heading? Thanks in
Advance.