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
|
|||
|
|||
Use crosstab column heading in a calculation
Duane,
I'll try that. Did you check out that thread? I have no familiarity with forms as datasheets, which is why I was trying to avoid using them. Jesper said he had a workable solution and Paul Overway apparently concurred. But I can't tell what "it" is in his statement, "The first time it runs I create a crosstab query ..." and I have no idea how he set up his subform. Bill Duane Hookom wrote: I would use code to set the parameter values in the sql like: strSQL = strSQL & "TRANSFORM ..." & vbCrLf strSQL = strSQL & "SELECT ..." & vbCrLf strSQL = strSQL & "FROM tblSwapParameters AS Params, Params2 " & vbCrLf strSQL = strSQL & "WHERE swapid=" & [Forms]![frmSwaps]![SwapID] & vbCrLf strSQL = strSQL & " And somefield=" & [Forms]![frmSwaps]![Conf95] & vbCrLf strSQL = strSQL & " GROUP BY swapid, ..." & vbCrLf strSQL = strSQL & " PIVOT CalcMonth;" Duane, [quoted text clipped - 52 lines] Bill -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#22
|
|||
|
|||
Use crosstab column heading in a calculation
I believe their solution is to assign the crosstab query as the object
source of the control on the main form. -- Duane Hookom MS Access MVP -- "Bill R via AccessMonster.com" u9289@uwe wrote in message news:5a8e766574b8b@uwe... Duane, I'll try that. Did you check out that thread? I have no familiarity with forms as datasheets, which is why I was trying to avoid using them. Jesper said he had a workable solution and Paul Overway apparently concurred. But I can't tell what "it" is in his statement, "The first time it runs I create a crosstab query ..." and I have no idea how he set up his subform. Bill Duane Hookom wrote: I would use code to set the parameter values in the sql like: strSQL = strSQL & "TRANSFORM ..." & vbCrLf strSQL = strSQL & "SELECT ..." & vbCrLf strSQL = strSQL & "FROM tblSwapParameters AS Params, Params2 " & vbCrLf strSQL = strSQL & "WHERE swapid=" & [Forms]![frmSwaps]![SwapID] & vbCrLf strSQL = strSQL & " And somefield=" & [Forms]![frmSwaps]![Conf95] & vbCrLf strSQL = strSQL & " GROUP BY swapid, ..." & vbCrLf strSQL = strSQL & " PIVOT CalcMonth;" Duane, [quoted text clipped - 52 lines] Bill -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#23
|
|||
|
|||
Use crosstab column heading in a calculation
When I do that, I get error msgs like "... can't use crosstab query..."
It sure is frustrating having created this beautiful crosstab query with it's complex calculation that returns everything I want to show my client, and then having no way to display the results in a subform. This is something that should be a breeze, but it's a virtual impossibility! Why else would anyone want to create such a query if not to display it's results? There may be other reasons, but displaying its results seems like a pretty common one to me. Duane Hookom wrote: I believe their solution is to assign the crosstab query as the object source of the control on the main form. Duane, [quoted text clipped - 25 lines] Bill -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#24
|
|||
|
|||
Use crosstab column heading in a calculation
You can easily show the results of a crosstab in a form or subform if the
crosstab has defined column headings. You can create reports with either static or dynamic if you find the solutions. I have given you a link to a sample application that I created that accepts dynamic columns in a subform. -- Duane Hookom MS Access MVP -- "Bill R via AccessMonster.com" u9289@uwe wrote in message news:5a8eed0d898ff@uwe... When I do that, I get error msgs like "... can't use crosstab query..." It sure is frustrating having created this beautiful crosstab query with it's complex calculation that returns everything I want to show my client, and then having no way to display the results in a subform. This is something that should be a breeze, but it's a virtual impossibility! Why else would anyone want to create such a query if not to display it's results? There may be other reasons, but displaying its results seems like a pretty common one to me. Duane Hookom wrote: I believe their solution is to assign the crosstab query as the object source of the control on the main form. Duane, [quoted text clipped - 25 lines] Bill -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#25
|
|||
|
|||
Use crosstab column heading in a calculation
Duane,
Right, I'm developing the code to do that, but that's the point. If only there were a control that would simply display the results of any query on a subform, regardless of it's # of fields or it's design. Or, better yet, if a subform could be used as a kind of blank display screen to display the results of any query. Maybe in my next life! Thanks for all your help, Bill Duane Hookom wrote: You can easily show the results of a crosstab in a form or subform if the crosstab has defined column headings. You can create reports with either static or dynamic if you find the solutions. I have given you a link to a sample application that I created that accepts dynamic columns in a subform. When I do that, I get error msgs like "... can't use crosstab query..." It sure is frustrating having created this beautiful crosstab query with [quoted text clipped - 16 lines] Bill -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#26
|
|||
|
|||
Use crosstab column heading in a calculation
I went back to the thread link you posted a while back. Try this:
Create a new blank form and add a subform control: Name: fsubOne Source Object:---nothing here----- Add a combo box to the main form: Name: cboQueries Row Source: -------------------------------------------------- SELECT msysObjects.Name FROM msysObjects WHERE (((msysObjects.Name) Not Like "~*") AND ((msysObjects.Type)=5)) ORDER BY msysObjects.Name; -------------------------------------------------- After Update code: -------------------------------------------------- Private Sub cboQueries_AfterUpdate() If Not IsNull(Me.cboQueries) Then Me.fsubOne.SourceObject = "Query." & Me.cboQueries End If End Sub -------------------------------------------------- -- Duane Hookom MS Access MVP -- "Bill R via AccessMonster.com" u9289@uwe wrote in message news:5a974d2956668@uwe... Duane, Right, I'm developing the code to do that, but that's the point. If only there were a control that would simply display the results of any query on a subform, regardless of it's # of fields or it's design. Or, better yet, if a subform could be used as a kind of blank display screen to display the results of any query. Maybe in my next life! Thanks for all your help, Bill Duane Hookom wrote: You can easily show the results of a crosstab in a form or subform if the crosstab has defined column headings. You can create reports with either static or dynamic if you find the solutions. I have given you a link to a sample application that I created that accepts dynamic columns in a subform. When I do that, I get error msgs like "... can't use crosstab query..." It sure is frustrating having created this beautiful crosstab query with [quoted text clipped - 16 lines] Bill -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#27
|
|||
|
|||
Use crosstab column heading in a calculation
Holy C**p!
That's great! Thanks, Duane Bill Duane Hookom wrote: I went back to the thread link you posted a while back. Try this: Create a new blank form and add a subform control: Name: fsubOne Source Object:---nothing here----- Add a combo box to the main form: Name: cboQueries Row Source: -------------------------------------------------- SELECT msysObjects.Name FROM msysObjects WHERE (((msysObjects.Name) Not Like "~*") AND ((msysObjects.Type)=5)) ORDER BY msysObjects.Name; -------------------------------------------------- After Update code: -------------------------------------------------- Private Sub cboQueries_AfterUpdate() If Not IsNull(Me.cboQueries) Then Me.fsubOne.SourceObject = "Query." & Me.cboQueries End If End Sub -------------------------------------------------- Duane, [quoted text clipped - 21 lines] Bill -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#28
|
|||
|
|||
Use crosstab column heading in a calculation
My thoughts also. Thanks for pushing me to try this out....
-- Duane Hookom MS Access MVP -- "Bill R via AccessMonster.com" u9289@uwe wrote in message news:5a99f72116c58@uwe... Holy C**p! That's great! Thanks, Duane Bill Duane Hookom wrote: I went back to the thread link you posted a while back. Try this: Create a new blank form and add a subform control: Name: fsubOne Source Object:---nothing here----- Add a combo box to the main form: Name: cboQueries Row Source: -------------------------------------------------- SELECT msysObjects.Name FROM msysObjects WHERE (((msysObjects.Name) Not Like "~*") AND ((msysObjects.Type)=5)) ORDER BY msysObjects.Name; -------------------------------------------------- After Update code: -------------------------------------------------- Private Sub cboQueries_AfterUpdate() If Not IsNull(Me.cboQueries) Then Me.fsubOne.SourceObject = "Query." & Me.cboQueries End If End Sub -------------------------------------------------- Duane, [quoted text clipped - 21 lines] Bill -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#29
|
|||
|
|||
Use crosstab column heading in a calculation
I understand from the post that it works with tables, too.
BTW, it still doesn't work for crosstab queries, which is what the post had purported. I'm using a more mundane solution. Thanks, Bill Duane Hookom wrote: My thoughts also. Thanks for pushing me to try this out.... Holy C**p! That's great! [quoted text clipped - 32 lines] Bill -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#30
|
|||
|
|||
Use crosstab column heading in a calculation
So, why don't you just specify which columns you want?
In Query Design View, right click in the upper window and set the Column Headings property to something like 1,2,23 or whatever you want displayed there. Or, you could place a filter on the Crosstab Query by specifying a criterion (such as 69 ) on the Column Heading field in Query Design View. Alternatively, you could define another Select Query that uses your Crosstab Query as its data source and select only the fields (via a criterion in your Select Query) that you want to use. Don't expect the results of the Select Query to be modifiable, though. -- Vincent Johns Please feel free to quote anything I say here. Bill R via AccessMonster.com wrote: When I open the form I get the msg: "You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or a subreport. Before you bind a subform or a subreport to a crosstab query, set the query's ColumnHeadings property" Duane Hookom wrote: You don't ask for much do you ;-) You can only show records in a subform where you know the fields so you can set the control sources. I have not seen solutions for dynamic subforms. I have created solutions for dynamic fields in reports. I do create a somewhat dynamic subform in the Query By Form applet available for download at http://www.rogersaccesslibrary.com/O...p#Hookom,Duane Duane, [quoted text clipped - 17 lines] Bill |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need to Improve Code Copying/Pasting Between Workbooks | David | General Discussion | 1 | January 6th, 2006 03:56 AM |
creating a bar graph | Johnfli | General Discussion | 0 | October 26th, 2005 08:16 PM |
Crosstab Column Heading Sort | MJatAflac | Running & Setting Up Queries | 3 | June 29th, 2005 01:54 AM |
How do I set up a report using dates as my report header? | Robin | Setting Up & Running Reports | 16 | November 13th, 2004 01:00 PM |
Using Validation to force entry into cells? | Mark | General Discussion | 16 | October 27th, 2004 09:23 PM |