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 |
#1
|
|||
|
|||
Problem with query
I have not seen anything like this before but:
1. I am querying a table with completed invoices to get those from a selected fiscal period. The period (quarter) is selected from a combobox. Columns 2 and 3 contain the start and end dates of the quarter. The query is only kicked off from a button on the "frmCalculate". The frmCalculate is a subform of frmTab. 2. When I open frmTab I get an error message of : " Undefined Function "Forms!frmTab!frmCalculate.cbostart.column" in expression. 3. The sql is: SELECT tblResults.EmployeeID, Sum(tblResults.InvoiceAmount) AS SumOfInvoiceAmount FROM tblResults WHERE (((tblResults.InvoiceDate) Between Forms!frmTab!frmCalculate!cboStart.column(2) And Forms!frmTab!frmCalculate!cbostart.column(3))) GROUP BY tblResults.EmployeeID; 4. I also tried assigning the values of the date columns to public variables. SELECT tblResults.EmployeeID, Sum(tblResults.InvoiceAmount) AS SumOfInvoiceAmount FROM tblResults WHERE (((tblResults.InvoiceDate) Between [pblStartDate] And [pblEndDate])) GROUP BY tblResults.EmployeeID; That then looks for (asks for) the value of the start and end dates when you open the application. Very frustrating! Any ideas? Kevin |
#2
|
|||
|
|||
Problem with query
Easiest way is to add two hidden controls to your form and assign the
column(2) and column(3) values to the hidden controls. Then you can reference the controls in your query. If you want to use the global variables you will need a FUNCTION in a VBA module that returns the value of each variable. Public function fGVarValue(strVarName as String) SELECT Case strVarName Case "pblStartDate" fGVarValue = pblStartDate Case "pblEndDate" fGVarValue = pblEndDate Case Else fGVarValue = Null END SELECT End Function John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County kc-mass wrote: I have not seen anything like this before but: 1. I am querying a table with completed invoices to get those from a selected fiscal period. The period (quarter) is selected from a combobox. Columns 2 and 3 contain the start and end dates of the quarter. The query is only kicked off from a button on the "frmCalculate". The frmCalculate is a subform of frmTab. 2. When I open frmTab I get an error message of : " Undefined Function "Forms!frmTab!frmCalculate.cbostart.column" in expression. 3. The sql is: SELECT tblResults.EmployeeID, Sum(tblResults.InvoiceAmount) AS SumOfInvoiceAmount FROM tblResults WHERE (((tblResults.InvoiceDate) Between Forms!frmTab!frmCalculate!cboStart.column(2) And Forms!frmTab!frmCalculate!cbostart.column(3))) GROUP BY tblResults.EmployeeID; 4. I also tried assigning the values of the date columns to public variables. SELECT tblResults.EmployeeID, Sum(tblResults.InvoiceAmount) AS SumOfInvoiceAmount FROM tblResults WHERE (((tblResults.InvoiceDate) Between [pblStartDate] And [pblEndDate])) GROUP BY tblResults.EmployeeID; That then looks for (asks for) the value of the start and end dates when you open the application. Very frustrating! Any ideas? Kevin |
#3
|
|||
|
|||
Problem with query
Thanks John
I'll try That Kevin "John Spencer" wrote in message ... Easiest way is to add two hidden controls to your form and assign the column(2) and column(3) values to the hidden controls. Then you can reference the controls in your query. If you want to use the global variables you will need a FUNCTION in a VBA module that returns the value of each variable. Public function fGVarValue(strVarName as String) SELECT Case strVarName Case "pblStartDate" fGVarValue = pblStartDate Case "pblEndDate" fGVarValue = pblEndDate Case Else fGVarValue = Null END SELECT End Function John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County kc-mass wrote: I have not seen anything like this before but: 1. I am querying a table with completed invoices to get those from a selected fiscal period. The period (quarter) is selected from a combobox. Columns 2 and 3 contain the start and end dates of the quarter. The query is only kicked off from a button on the "frmCalculate". The frmCalculate is a subform of frmTab. 2. When I open frmTab I get an error message of : " Undefined Function "Forms!frmTab!frmCalculate.cbostart.column" in expression. 3. The sql is: SELECT tblResults.EmployeeID, Sum(tblResults.InvoiceAmount) AS SumOfInvoiceAmount FROM tblResults WHERE (((tblResults.InvoiceDate) Between Forms!frmTab!frmCalculate!cboStart.column(2) And Forms!frmTab!frmCalculate!cbostart.column(3))) GROUP BY tblResults.EmployeeID; 4. I also tried assigning the values of the date columns to public variables. SELECT tblResults.EmployeeID, Sum(tblResults.InvoiceAmount) AS SumOfInvoiceAmount FROM tblResults WHERE (((tblResults.InvoiceDate) Between [pblStartDate] And [pblEndDate])) GROUP BY tblResults.EmployeeID; That then looks for (asks for) the value of the start and end dates when you open the application. Very frustrating! Any ideas? Kevin |
Thread Tools | |
Display Modes | |
|
|