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. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
getting a report from a crosstab query
that worked great but is there any way to display the date instead of DO,D1,
D2 Duane Hookom wrote: Let's assume you want 7 days of the schedule ending on Forms!frmDate!txtEndDate 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") ; cam you give me an example other then the on on Tek-tips. That one looks like greek to me [quoted text clipped - 6 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 |
#12
|
|||
|
|||
getting a report from a crosstab query
The previous link explained how to display the actual dates as column
headings in your report. You use text boxes with control sources like: =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) -- Duane Hookom Microsoft Access MVP "abourg8646 via AccessMonster.com" wrote: that worked great but is there any way to display the date instead of DO,D1, D2 Duane Hookom wrote: Let's assume you want 7 days of the schedule ending on Forms!frmDate!txtEndDate 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") ; cam you give me an example other then the on on Tek-tips. That one looks like greek to me [quoted text clipped - 6 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 |
#13
|
|||
|
|||
getting a report from a crosstab query
Its still not showing the date in the column and when I add the line below
then it removes the value. Duane Hookom wrote: The previous link explained how to display the actual dates as column headings in your report. You use text boxes with control sources like: =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) that worked great but is there any way to display the date instead of DO,D1, D2 [quoted text clipped - 14 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 |
#14
|
|||
|
|||
getting a report from a crosstab query
What's not showing the date in the column where? Are you still messing around
in the query or are you now working in your report? I don't understand what line you added where and what value it removes. How about telling us what you have done? -- Duane Hookom Microsoft Access MVP "abourg8646 via AccessMonster.com" wrote: Its still not showing the date in the column and when I add the line below then it removes the value. Duane Hookom wrote: The previous link explained how to display the actual dates as column headings in your report. You use text boxes with control sources like: =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) that worked great but is there any way to display the date instead of DO,D1, D2 [quoted text clipped - 14 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 |
#15
|
|||
|
|||
getting a report from a crosstab query
I copied the SQL and place that in my crosstab SQL. This displayed D6,D5nD4,
D3,D2,D1,D0 as column headers. I then went to reports and 1st I treid to copy the DateDiff line into the control source on the form marked D6. That didn't work. 2nd I tried to add text boxes to the report an use the datediff statement in rhe control source. That still didn't work. Duane Hookom wrote: What's not showing the date in the column where? Are you still messing around in the query or are you now working in your report? I don't understand what line you added where and what value it removes. How about telling us what you have done? Its still not showing the date in the column and when I add the line below then it removes the value. [quoted text clipped - 14 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 |
#16
|
|||
|
|||
getting a report from a crosstab query
Are you putting just ONE of the expressions as the source for each
control? I know that may be a basic question, but I've seen people do just that and then be confused about why something did not work. '================================================= === John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================= === abourg8646 via AccessMonster.com wrote: I copied the SQL and place that in my crosstab SQL. This displayed D6,D5nD4, D3,D2,D1,D0 as column headers. I then went to reports and 1st I treid to copy the DateDiff line into the control source on the form marked D6. That didn't work. 2nd I tried to add text boxes to the report an use the datediff statement in rhe control source. That still didn't work. Duane Hookom wrote: What's not showing the date in the column where? Are you still messing around in the query or are you now working in your report? I don't understand what line you added where and what value it removes. How about telling us what you have done? Its still not showing the date in the column and when I add the line below then it removes the value. [quoted text clipped - 14 lines] GROUP BY [Employee Name], Shift, Post, [Time] PIVOT [Employee Query].[Date]; |
#17
|
|||
|
|||
getting a report from a crosstab query
yes. Was I suppose to put all of them in D0.
John Spencer wrote: Are you putting just ONE of the expressions as the source for each control? I know that may be a basic question, but I've seen people do just that and then be confused about why something did not work. '================================================ ==== John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================ ==== I copied the SQL and place that in my crosstab SQL. This displayed D6,D5nD4, D3,D2,D1,D0 as column headers. I then went to reports and 1st I treid to copy [quoted text clipped - 14 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 |
#18
|
|||
|
|||
getting a report from a crosstab query
NO, just one in each of the controls.
And you do need the equal sign. You need to add controls in the report that replace the labels for your date columns and set the control source for each new control to a variation of the following: =DateAdd("d",-6,Forms!frmDate!txtEndDate) If that does not work then you need to tell us what you mean by does not work. Are you getting an error value, the wrong information, no information? If an error what exactly does the error say? If the wrong value, what value are you getting and why is it wrong. '================================================= === John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================= === abourg8646 via AccessMonster.com wrote: yes. Was I suppose to put all of them in D0. John Spencer wrote: Are you putting just ONE of the expressions as the source for each control? I know that may be a basic question, but I've seen people do just that and then be confused about why something did not work. '================================================= === John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================= === I copied the SQL and place that in my crosstab SQL. This displayed D6,D5nD4, D3,D2,D1,D0 as column headers. I then went to reports and 1st I treid to copy [quoted text clipped - 14 lines] GROUP BY [Employee Name], Shift, Post, [Time] PIVOT [Employee Query].[Date]; |
#19
|
|||
|
|||
getting a report from a crosstab query
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?. John Spencer wrote: NO, just one in each of the controls. And you do need the equal sign. You need to add controls in the report that replace the labels for your date columns and set the control source for each new control to a variation of the following: =DateAdd("d",-6,Forms!frmDate!txtEndDate) If that does not work then you need to tell us what you mean by does not work. Are you getting an error value, the wrong information, no information? If an error what exactly does the error say? If the wrong value, what value are you getting and why is it wrong. '================================================ ==== John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================ ==== yes. Was I suppose to put all of them in D0. [quoted text clipped - 14 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 |
#20
|
|||
|
|||
getting a report from a crosstab query
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? -- Duane Hookom Microsoft Access MVP "abourg8646 via AccessMonster.com" wrote: 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?. John Spencer wrote: NO, just one in each of the controls. And you do need the equal sign. You need to add controls in the report that replace the labels for your date columns and set the control source for each new control to a variation of the following: =DateAdd("d",-6,Forms!frmDate!txtEndDate) If that does not work then you need to tell us what you mean by does not work. Are you getting an error value, the wrong information, no information? If an error what exactly does the error say? If the wrong value, what value are you getting and why is it wrong. '================================================ ==== John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================ ==== yes. Was I suppose to put all of them in D0. [quoted text clipped - 14 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 |
Thread Tools | |
Display Modes | |
|
|