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
|
|||
|
|||
Use crosstab column heading in a calculation
Bingo!!!
Thanks a million, Duane. Bill Duane Hookom wrote: You must always define the data type of all parameters in crosstab queries. Select Query-Parameters and enter [Forms]![frmSwaps]![txtSwapID] datatype BTW: without reading all your other postings, the solution for your first posting is to create a query with this sql TRANSFORM First([tblSwapParameters].[SpotMonth]*[tblSwapParameters_1].[SpotMonth]) AS Expr1 SELECT tblSwapParameters.SpotMonth FROM tblSwapParameters, tblSwapParameters AS tblSwapParameters_1 GROUP BY tblSwapParameters.SpotMonth PIVOT tblSwapParameters_1.SpotMonth; PS: [quoted text clipped - 42 lines] Bill -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#12
|
|||
|
|||
Use crosstab column heading in a calculation
Duane,
Now that I've got the simple calculation working, is it possible to include a value from a control on a form in the calculation "Expr1"? What are the restrictions on calculations in a crosstab query? I've already come across a problem in using a control on the main form as criteria in a crosstab query or even in an underlying query for a crosstab query. If I can't use data from a control on a form, I will fall back to "Plan I" and run a make table query based on this crosstab query, and then populate the records and fields using a calculation in VBA. Thanks, Bill Duane Hookom wrote: You must always define the data type of all parameters in crosstab queries. Select Query-Parameters and enter [Forms]![frmSwaps]![txtSwapID] datatype BTW: without reading all your other postings, the solution for your first posting is to create a query with this sql TRANSFORM First([tblSwapParameters].[SpotMonth]*[tblSwapParameters_1].[SpotMonth]) AS Expr1 SELECT tblSwapParameters.SpotMonth FROM tblSwapParameters, tblSwapParameters AS tblSwapParameters_1 GROUP BY tblSwapParameters.SpotMonth PIVOT tblSwapParameters_1.SpotMonth; PS: [quoted text clipped - 42 lines] Bill -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#13
|
|||
|
|||
Use crosstab column heading in a calculation
You can use a reference to a control on a form in the same way that you
would hard-code a value. As I stated earlier, crosstabs expect you to identify the data types of all parameters. -- Duane Hookom MS Access MVP -- "Bill R via AccessMonster.com" u9289@uwe wrote in message news:5a8b11974c13f@uwe... Duane, Now that I've got the simple calculation working, is it possible to include a value from a control on a form in the calculation "Expr1"? What are the restrictions on calculations in a crosstab query? I've already come across a problem in using a control on the main form as criteria in a crosstab query or even in an underlying query for a crosstab query. If I can't use data from a control on a form, I will fall back to "Plan I" and run a make table query based on this crosstab query, and then populate the records and fields using a calculation in VBA. Thanks, Bill Duane Hookom wrote: You must always define the data type of all parameters in crosstab queries. Select Query-Parameters and enter [Forms]![frmSwaps]![txtSwapID] datatype BTW: without reading all your other postings, the solution for your first posting is to create a query with this sql TRANSFORM First([tblSwapParameters].[SpotMonth]*[tblSwapParameters_1].[SpotMonth]) AS Expr1 SELECT tblSwapParameters.SpotMonth FROM tblSwapParameters, tblSwapParameters AS tblSwapParameters_1 GROUP BY tblSwapParameters.SpotMonth PIVOT tblSwapParameters_1.SpotMonth; PS: [quoted text clipped - 42 lines] Bill -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#14
|
|||
|
|||
Use crosstab column heading in a calculation
Thanks,
I finally figured out where to set the datatype of the parameter. I had been looking in query properties, but it's in the Query menu in the toolbar. Bill Duane Hookom wrote: You can use a reference to a control on a form in the same way that you would hard-code a value. As I stated earlier, crosstabs expect you to identify the data types of all parameters. Duane, [quoted text clipped - 36 lines] Bill -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#15
|
|||
|
|||
Use crosstab column heading in a calculation
Duane,
Now that I've got all the calculations working, I would like to use this crosstab query as the datasource for a subform. I would prefer a continuous subform to a datasheet. How do I display in a subform, a datasource whose field names and the number of fields will change depending on the results of the crosstab query? Bill Duane Hookom wrote: You can use a reference to a control on a form in the same way that you would hard-code a value. As I stated earlier, crosstabs expect you to identify the data types of all parameters. Duane, [quoted text clipped - 36 lines] Bill -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#16
|
|||
|
|||
Use crosstab column heading in a calculation
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 Hookom MS Access MVP -- "Bill R via AccessMonster.com" u9289@uwe wrote in message news:5a8c96c5835c7@uwe... Duane, Now that I've got all the calculations working, I would like to use this crosstab query as the datasource for a subform. I would prefer a continuous subform to a datasheet. How do I display in a subform, a datasource whose field names and the number of fields will change depending on the results of the crosstab query? Bill Duane Hookom wrote: You can use a reference to a control on a form in the same way that you would hard-code a value. As I stated earlier, crosstabs expect you to identify the data types of all parameters. Duane, [quoted text clipped - 36 lines] Bill -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#17
|
|||
|
|||
Use crosstab column heading in a calculation
I wouldn't keep asking if you guys weren't so damn helpful!!! ;-)
Thanks for the advice, Bill 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 -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#18
|
|||
|
|||
Use crosstab column heading in a calculation
Duane,
I found a thread that purports to have solved the problem, but I can't seem to make it work. Here's the thread: http://www.accessmonster.com/Uwe/For...8dd03e6294fuwe Here's my code in the On Current event of the main form: Private Sub Form_Current() Dim strSQL As String Dim db As DAO.Database Dim qdf As QueryDef Set db = CurrentDb strSQL = "PARAMETERS [Forms]![frmSwaps]![SwapID] Long, [Forms]![frmSwaps]! [Conf95] IEEEDouble;" & vbCrLf 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 & "GROUP BY swapid, ..." & vbCrLf strSQL = strSQL & "PIVOT CalcMonth;" Set qdf = db.QueryDefs("qryFwdPrices") qdf.SQL = strSQL Me!sfrmFwdPrices.SourceObject = "Query.qryFwdPrices" Set db = Nothing End Sub Can you see where I have strayed from Jesper's example? Thanks, Bill 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 -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#19
|
|||
|
|||
Use crosstab column heading in a calculation
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 -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
#20
|
|||
|
|||
Use crosstab column heading in a calculation
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 Hookom MS Access MVP -- "Bill R via AccessMonster.com" u9289@uwe wrote in message news:5a8de36f0c9bb@uwe... Duane, I found a thread that purports to have solved the problem, but I can't seem to make it work. Here's the thread: http://www.accessmonster.com/Uwe/For...8dd03e6294fuwe Here's my code in the On Current event of the main form: Private Sub Form_Current() Dim strSQL As String Dim db As DAO.Database Dim qdf As QueryDef Set db = CurrentDb strSQL = "PARAMETERS [Forms]![frmSwaps]![SwapID] Long, [Forms]![frmSwaps]! [Conf95] IEEEDouble;" & vbCrLf 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 & "GROUP BY swapid, ..." & vbCrLf strSQL = strSQL & "PIVOT CalcMonth;" Set qdf = db.QueryDefs("qryFwdPrices") qdf.SQL = strSQL Me!sfrmFwdPrices.SourceObject = "Query.qryFwdPrices" Set db = Nothing End Sub Can you see where I have strayed from Jesper's example? Thanks, Bill 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 -- Bill Reed "If you can't laugh at yoursel, laugh at somebody else" Message posted via http://www.accessmonster.com |
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 |