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 |
#21
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|