getting a report from a crosstab query

[abourg8646 via AccessMonster.com]

I placed 7 text boxes in the page header and used the staement that you
supplied. I'm still getting the same response #Name?. The form isn't open
that I know of. When I click on the report then it asks for the end Date. The
values are all correct. #Name?. I would also like to say that I really
appreciate all the help that you MVPs have provided me. Is there any good on
line courses that you could recommend.

This shouldn't be that difficult. The detail section of your report should
have text boxes bound to the fields/column generated by your crosstab which
includ [d0], [d2],...

In the page header (or other section), you can add text boxes to use a
column titles with control sources like:
=DateAdd("d",-0,Forms!frmDate!txtEndDate)

Did you use these form and control names? Is the form remaining open when
the report is opened?

Do you see the correct values in the detail section?

What do you see the the text boxes with the DateAdd() functions?

It is still leaving D6,D5,D4,D3,D2,D1,D0 as column manes. When I put =DateAdd
statement then I get columns of #Name?.

[quoted text clipped - 25 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[Duane Hookom]

If you want to reference the value of a control from a form, the form must be
open and contain a value in the control.

You might want to check out Crystal's tutorials at
http://www.youtube.com/user/LearnAccessByCrystal.

--
Duane Hookom
Microsoft Access MVP

I placed 7 text boxes in the page header and used the staement that you
supplied. I'm still getting the same response #Name?. The form isn't open
that I know of. When I click on the report then it asks for the end Date. The
values are all correct. #Name?. I would also like to say that I really
appreciate all the help that you MVPs have provided me. Is there any good on
line courses that you could recommend.

This shouldn't be that difficult. The detail section of your report should
have text boxes bound to the fields/column generated by your crosstab which
includ [d0], [d2],...

In the page header (or other section), you can add text boxes to use a
column titles with control sources like:
=DateAdd("d",-0,Forms!frmDate!txtEndDate)

Did you use these form and control names? Is the form remaining open when
the report is opened?

Do you see the correct values in the detail section?

What do you see the the text boxes with the DateAdd() functions?

It is still leaving D6,D5,D4,D3,D2,D1,D0 as column manes. When I put =DateAdd
statement then I get columns of #Name?.

[quoted text clipped - 25 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[abourg8646 via AccessMonster.com]

when I started then my crosstab quert displayed the dates in the column
header. ex.

NAME: Post: Time: Shift 5/2/2007 5/3/2007 5/4/2007 5/5/2007 ....
John ACC 0600 1st 0600 0600 OFF VAC

But it would let me create a report. The SQL was as follows:
PARAMETERS [Start date] DateTime, [End Date] DateTime;
TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Name], Shift, Post, [Time]
FROM [Employee Query]
WHERE [Date] Between [Start Date] And [End Date]
GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

The we changed the SQL to
PARAMETERS Forms!frmDate!txtEndDate DateTime;
TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Name], Shift, Post, [Time]
FROM [Employee Query]
GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT "D" & DateDiff("d",[Date],Forms!frmDate!txtEndDate)
IN ("D6","D5","D4","D3","D2","D1","D0") ;

This worked good but it didn't show the date it looked like this:
NAME: Post: Time: Shift D6 D5 D4 D3 ....
John ACC 0600 1st 0600 0600 OFF VAC ....

Instead of seeing D6, D5, D4, I need to see The actual dates.

If you want to reference the value of a control from a form, the form must be
open and contain a value in the control.

You might want to check out Crystal's tutorials at
http://www.youtube.com/user/LearnAccessByCrystal.

I placed 7 text boxes in the page header and used the staement that you
supplied. I'm still getting the same response #Name?. The form isn't open

[quoted text clipped - 23 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[Duane Hookom]

"Instead of seeing D6, D5, D4, I need to see The actual dates." I want you
to see the actual dates in your report also. Everything I have posted
supports this. All recent posts have referenced your form and your report.
These posts as well as the FAQ link I posted support this.

Can you tell me what you have done to follow through on my suggestions
within your report. Don't tell me anything about your query since the correct
values are displaying in your query. We are now concerned only about the
report and how it pulls values from your OPEN form.

--
Duane Hookom
Microsoft Access MVP

when I started then my crosstab quert displayed the dates in the column
header. ex.

NAME: Post: Time: Shift 5/2/2007 5/3/2007 5/4/2007 5/5/2007 ....
John ACC 0600 1st 0600 0600 OFF VAC

But it would let me create a report. The SQL was as follows:
PARAMETERS [Start date] DateTime, [End Date] DateTime;
TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Name], Shift, Post, [Time]
FROM [Employee Query]
WHERE [Date] Between [Start Date] And [End Date]
GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

The we changed the SQL to
PARAMETERS Forms!frmDate!txtEndDate DateTime;
TRANSFORM First([Employee Query].Schedule) AS FirstOfSchedule
SELECT [Employee Name], Shift, Post, [Time]
FROM [Employee Query]
GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT "D" & DateDiff("d",[Date],Forms!frmDate!txtEndDate)
IN ("D6","D5","D4","D3","D2","D1","D0") ;

This worked good but it didn't show the date it looked like this:
NAME: Post: Time: Shift D6 D5 D4 D3 ....
John ACC 0600 1st 0600 0600 OFF VAC ....

Instead of seeing D6, D5, D4, I need to see The actual dates.

If you want to reference the value of a control from a form, the form must be
open and contain a value in the control.

You might want to check out Crystal's tutorials at
http://www.youtube.com/user/LearnAccessByCrystal.

I placed 7 text boxes in the page header and used the staement that you
supplied. I'm still getting the same response #Name?. The form isn't open

[quoted text clipped - 23 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[abourg8646 via AccessMonster.com]

I made a form and called it date. I then added 6 more txt boxes and added the
-DateAdd statements to the control source under property. I'm probly doind
something wrong because i'm not sure what a OPEN form is.

"Instead of seeing D6, D5, D4, I need to see The actual dates." I want you
to see the actual dates in your report also. Everything I have posted
supports this. All recent posts have referenced your form and your report.
These posts as well as the FAQ link I posted support this.

Can you tell me what you have done to follow through on my suggestions
within your report. Don't tell me anything about your query since the correct
values are displaying in your query. We are now concerned only about the
report and how it pulls values from your OPEN form.

when I started then my crosstab quert displayed the dates in the column
header. ex.

[quoted text clipped - 37 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[Duane Hookom]

The form needs only one text box for the user to enter the ending date of the
report. The "6 more txt boxes" are on the report. One of my previous posts
was referencing the report and stated:
<quote>In the page header (or other section), you can add text boxes to use
as
column titles with control sources like:
=DateAdd("d",-0,Forms!frmDate!txtEndDate)
</quote>
--
Duane Hookom
Microsoft Access MVP

I made a form and called it date. I then added 6 more txt boxes and added the
-DateAdd statements to the control source under property. I'm probly doind
something wrong because i'm not sure what a OPEN form is.

"Instead of seeing D6, D5, D4, I need to see The actual dates." I want you
to see the actual dates in your report also. Everything I have posted
supports this. All recent posts have referenced your form and your report.
These posts as well as the FAQ link I posted support this.

Can you tell me what you have done to follow through on my suggestions
within your report. Don't tell me anything about your query since the correct
values are displaying in your query. We are now concerned only about the
report and how it pulls values from your OPEN form.

when I started then my crosstab quert displayed the dates in the column
header. ex.

[quoted text clipped - 37 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[abourg8646 via AccessMonster.com]

I re-did my form and the record source is the crosstab. I then made a txt box.
I named it EndDate. What do I put into the contral source.

The form needs only one text box for the user to enter the ending date of the
report. The "6 more txt boxes" are on the report. One of my previous posts
was referencing the report and stated:
<quote>In the page header (or other section), you can add text boxes to use
as
column titles with control sources like:
=DateAdd("d",-0,Forms!frmDate!txtEndDate)

I made a form and called it date. I then added 6 more txt boxes and added the
-DateAdd statements to the control source under property. I'm probly doind

[quoted text clipped - 15 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[Duane Hookom]

I thought you wanted to create a report based on your crosstab. When/why did
your needs change to a form?

Did you actually try create column headings in your report?

I feel like I am aiming at a continuously moving target. Please find some
specifications and stick to them.

--
Duane Hookom
Microsoft Access MVP

I re-did my form and the record source is the crosstab. I then made a txt box.
I named it EndDate. What do I put into the contral source.

The form needs only one text box for the user to enter the ending date of the
report. The "6 more txt boxes" are on the report. One of my previous posts
was referencing the report and stated:
<quote>In the page header (or other section), you can add text boxes to use
as
column titles with control sources like:
=DateAdd("d",-0,Forms!frmDate!txtEndDate)

I made a form and called it date. I then added 6 more txt boxes and added the
-DateAdd statements to the control source under property. I'm probly doind

[quoted text clipped - 15 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];

 

[abourg8646 via AccessMonster.com]

1st I would like to say thankyou for all the help. I've learned a lot.
Everytime you would use a key word like form or something else then I was
thinking that I was in the wrong spot and thats why I seemed like a moving
target. I got the report working this morning. I kept reading over everything
you wrote and it kept giving me an error (#Name?). You gave me this statement
to use:

=DateAdd("d",-6,Forms!frmDate!txtEndDate)
=DateAdd("d",-5,Forms!frmDate!txtEndDate)
=DateAdd("d",-4,Forms!frmDate!txtEndDate)
=DateAdd("d",-3,Forms!frmDate!txtEndDate)
=DateAdd("d",-2,Forms!frmDate!txtEndDate)
=DateAdd("d",-1,Forms!frmDate!txtEndDate)
=DateAdd("d",-0,Forms!frmDate!txtEndDate)

I tried about 20 times and was getting frustated until I finaly realised that
the statement you supplied was part of the parameter and I changed it to this

=DateAdd("d",-0,[Forms!frmDate!txtEndDate])

I'm not sure if I did it right or if you assumed that I would know this. But
it worked and I can't say it enough THANK YOU

I thought you wanted to create a report based on your crosstab. When/why did
your needs change to a form?

Did you actually try create column headings in your report?

I feel like I am aiming at a continuously moving target. Please find some
specifications and stick to them.

I re-did my form and the record source is the crosstab. I then made a txt box.
I named it EndDate. What do I put into the contral source.

[quoted text clipped - 12 lines]

GROUP BY [Employee Name], Shift, Post, [Time]
PIVOT [Employee Query].[Date];