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 » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

getting a report from a crosstab query



 
 
Thread Tools Display Modes
  #21  
Old June 22nd, 2009, 12:25 AM posted to microsoft.public.access.reports
abourg8646 via AccessMonster.com
external usenet poster
 
Posts: 45
Default getting a report from a crosstab query

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.

Duane Hookom wrote:
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];


--
Arthur Bourgeois Jr

Message posted via http://www.accessmonster.com

  #22  
Old June 22nd, 2009, 01:40 AM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default getting a report from a crosstab query

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


"abourg8646 via AccessMonster.com" wrote:

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.

Duane Hookom wrote:
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];


--
Arthur Bourgeois Jr

Message posted via http://www.accessmonster.com


  #23  
Old June 23rd, 2009, 12:38 AM posted to microsoft.public.access.reports
abourg8646 via AccessMonster.com
external usenet poster
 
Posts: 45
Default getting a report from a crosstab query

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.


Duane Hookom wrote:
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];


--
Arthur Bourgeois Jr

Message posted via http://www.accessmonster.com

  #24  
Old June 23rd, 2009, 12:46 AM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default getting a report from a crosstab query

"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


"abourg8646 via AccessMonster.com" wrote:

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.


Duane Hookom wrote:
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];


--
Arthur Bourgeois Jr

Message posted via http://www.accessmonster.com


  #25  
Old June 23rd, 2009, 01:27 AM posted to microsoft.public.access.reports
abourg8646 via AccessMonster.com
external usenet poster
 
Posts: 45
Default getting a report from a crosstab query

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.

Duane Hookom wrote:
"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];


--
Arthur Bourgeois Jr

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200906/1

  #26  
Old June 23rd, 2009, 02:12 AM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default getting a report from a crosstab query

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:
quoteIn 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


"abourg8646 via AccessMonster.com" wrote:

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.

Duane Hookom wrote:
"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];


--
Arthur Bourgeois Jr

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200906/1


  #27  
Old June 23rd, 2009, 03:33 AM posted to microsoft.public.access.reports
abourg8646 via AccessMonster.com
external usenet poster
 
Posts: 45
Default getting a report from a crosstab query

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.

Duane Hookom wrote:
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:
quoteIn 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
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];


--
Arthur Bourgeois Jr

Message posted via http://www.accessmonster.com

  #28  
Old June 23rd, 2009, 04:17 AM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default getting a report from a crosstab query

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


"abourg8646 via AccessMonster.com" wrote:

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.

Duane Hookom wrote:
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:
quoteIn 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
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];


--
Arthur Bourgeois Jr

Message posted via http://www.accessmonster.com


  #29  
Old June 23rd, 2009, 11:14 PM posted to microsoft.public.access.reports
abourg8646 via AccessMonster.com
external usenet poster
 
Posts: 45
Default getting a report from a crosstab query

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



Duane Hookom wrote:
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];


--
Arthur Bourgeois Jr

Message posted via http://www.accessmonster.com

 




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 05:50 AM.


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