Prev: Group By or MAX Question
Next: Real 10 random records
From: kc-mass on 12 May 2010 16:07
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


From: John Spencer on 12 May 2010 19:11
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
>
>
From: kc-mass on 12 May 2010 20:21
Thanks John

I'll try That

Kevin


"John Spencer" <spencer(a)chpdm.edu> wrote in message
news:eo2cshi8KHA.3540(a)TK2MSFTNGP06.phx.gbl...
> 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
>>

 | 
Pages: 1
Prev: Group By or MAX Question
Next: Real 10 random records