Problem with query

[kc-mass]

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

 

[John Spencer]

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]

Thanks John

I'll try That

Kevin

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

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