Can I use more than one Combo column as query parameters?

[Frank Martin]

I am making a Statement form for customers owing money for various months.

I want to use the one query for the basis of the statement report.

For this I will have to use two parameters, one for the customer and one for
the month ( Format([InvoiceDate],"mmmyy")).

To avoid confusion I want only one combo box on the Statement form, so I
will need to use more than one column of this combo as parameters for the
underlying query of the FrmStatement

I know how to use one column using the "bound column", but how do I use two
columns as 'bound'?

Please help, Frank

 

[Wayne Morgan]

You can get the value of any column of the combo box by using the Column
property of the combo box. The index number of the Column property is zero
based.

=Forms!frmMyForm!cboMyCombo.Column(1)

would get the value from the 2nd column.

However, since the items in the rows of the combo box most likely are from
the same record, I don't see where this is helping much. If you are limiting
your selection by the record currently selected in the combo box, then
adding another parameter from the same record probably isn't going to change
the out come.

 

[Frank Martin]

Thanks, I tried this but it only returns the top month of a customer set.
All I need to do is to select one record in the combo and have the two
fields 'Customer' and 'Date' be the parameters in the query used for the
statement report.
I can get one of them to work just fine.

You can get the value of any column of the combo box by using the Column
property of the combo box. The index number of the Column property is zero
based.

=Forms!frmMyForm!cboMyCombo.Column(1)

would get the value from the 2nd column.

However, since the items in the rows of the combo box most likely are from
the same record, I don't see where this is helping much. If you are
limiting your selection by the record currently selected in the combo box,
then adding another parameter from the same record probably isn't going to
change the out come.

--
Wayne Morgan
MS Access MVP

I am making a Statement form for customers owing money for various months.

I want to use the one query for the basis of the statement report.

For this I will have to use two parameters, one for the customer and one
for the month ( Format([InvoiceDate],"mmmyy")).

To avoid confusion I want only one combo box on the Statement form, so I
will need to use more than one column of this combo as parameters for the
underlying query of the FrmStatement

I know how to use one column using the "bound column", but how do I use
two columns as 'bound'?

Please help, Frank

 

[Wayne Morgan]

Do use the Date parameter, you may need to declare it as a Date data type
before it will work properly.

Parameters Forms!frmMyForm!txtFromCombo DateTime;
SELECT...etc
WHERE DateField = Forms!frmMyForm!txtFromCombo

I too am having trouble getting the syntax just right to refer to the column
of the combo box from within the query. As a work around, create a hidden
textbox on the form, set the Control Source of the textbox to the column of
the combo box, and have the query refer to the textbox.

 

[Frank Martin]

This is it, though I do all the designing via the query grid.
***
SELECT QryCustTRIALBALANCE.AddressesID, QryCustTRIALBALANCE.People,
QryCustTRIALBALANCE.Expr1, Sum(QryCustTRIALBALANCE.SumOfExpr3) AS
SumOfSumOfExpr3, QryCustTRIALBALANCE.Expr2
FROM QryCustTRIALBALANCE
GROUP BY QryCustTRIALBALANCE.AddressesID, QryCustTRIALBALANCE.People,
QryCustTRIALBALANCE.Expr1, QryCustTRIALBALANCE.Expr2
HAVING (((QryCustTRIALBALANCE.People)<>"Test company incorporated"))
ORDER BY QryCustTRIALBALANCE.People, QryCustTRIALBALANCE.Expr2 DESC;
***
Thanks.

Will you post the query's SQL view?

--
Wayne Morgan
MS Access MVP

Thanks, I have gone over this again and the problem seems to be with the
column property.

I have done as you suggested and put two text boxes on the form, with the
intention of using these as the Statement query parameters.

However the same problem arises when putting the values into these text
boxes.

For Text1 the expression is "=[Combo6].[Column](0)" and this returns the
customer names.

For Text2 the expression is "=[Combo6].[Column](2)" and this returns the
month in the format "mmmyy".

Text1 seems to work, though one cannot know which of the identical
customer names it returns (if one customer has transactions in several
months.)
Text2 returns only the latest month and ignors all the earlier ones.

I have never seen this problem before.

Regards, Frank