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]

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

 

[Wayne Morgan]

Will you post the query's SQL view?

 

[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

 

[Wayne Morgan]

I do most of mine in the design grid also, but the SQL is easier to paste
into a message.

I don't see any where in your query where you are referring to a control on
a form. Are you doing that in one of the queries that this query is built
on?

--
Wayne Morgan
MS Access MVP

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

 

[Frank Martin]

I use two queries for the Statement system.
The first, the one I posted is just the one used for the ComboBox look up,
to supply the parameters "AddressesID" and the month "mmmyy" which I plan to
pass to the query used for the RptStatement and this one is QryInvoice.

But the problem is isolated to the first query (the one I posted) because
the combo box based on it will not return the fields selected for the text
fields on the statement pop-up form.
"=[Combo6].[Column](0)" and
"=[Combo6].[Column](2)"

These two ColumnProperties should return a Customer and its corresponding
month from a list as shown below, based on the combo I posted.

The following is an reduced example of the ComboBox display showing two
columns only:

Customer: Month:
NV Soap Aug05
NV Soap Jul05
NV Soap Jun05
NV Soap May05
MelCo Aug05
MelCo Jul05
SaltCo May05
SaltCo Apr05
SaltCo Feb05

If a record is selected from this combo, say (NVSoap, May05) then the
ColumnProperty will return only (NV Soap, Aug05) which is the top month.
Similarly if (SaltCo, Feb05) is selected then only (SaltCo, May05) is
returned!
In all cases all that is returned is the top month of a selected customer,
and this appears to be a problem with the Column Property.

I do most of mine in the design grid also, but the SQL is easier to paste
into a message.

I don't see any where in your query where you are referring to a control
on a form. Are you doing that in one of the queries that this query is
built on?

--
Wayne Morgan
MS Access MVP

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

 

[Wayne Morgan]

Ok, if the query you sent is the Row Source for Combo6, I count a total of 6
columns. Which column is the bound column? Are the values in this column
unique?

--
Wayne Morgan
MS Access MVP

I use two queries for the Statement system.
The first, the one I posted is just the one used for the ComboBox look
up,
to supply the parameters "AddressesID" and the month "mmmyy" which I plan
to
pass to the query used for the RptStatement and this one is QryInvoice.

But the problem is isolated to the first query (the one I posted) because
the combo box based on it will not return the fields selected for the text
fields on the statement pop-up form.
"=[Combo6].[Column](0)" and
"=[Combo6].[Column](2)"

These two ColumnProperties should return a Customer and its corresponding
month from a list as shown below, based on the combo I posted.

The following is an reduced example of the ComboBox display showing two
columns only:

Customer: Month:
NV Soap Aug05
NV Soap Jul05
NV Soap Jun05
NV Soap May05
MelCo Aug05
MelCo Jul05
SaltCo May05
SaltCo Apr05
SaltCo Feb05

If a record is selected from this combo, say (NVSoap, May05) then the
ColumnProperty will return only (NV Soap, Aug05) which is the top month.
Similarly if (SaltCo, Feb05) is selected then only (SaltCo, May05) is
returned!
In all cases all that is returned is the top month of a selected customer,
and this appears to be a problem with the Column Property.












message

I do most of mine in the design grid also, but the SQL is easier to paste
into a message.

I don't see any where in your query where you are referring to a control
on a form. Are you doing that in one of the queries that this query is
built on?

--
Wayne Morgan
MS Access MVP

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.


message 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

 

[Frank Martin]

No, and this was the problem because I had no unique column of numbers in
the dynaset to allow selection of a desired record.
It all works fine now and I include a copy of a post I made a few minutes
ago.

***
I have much trouble in adding a column of sequencing numbers to a totals
query to select a unique record.

Instead of this I have joined two columns into a new one, and in this case I
have used the "CStr" function to convert the date into a text string.

Then I combine two columns such that the result with be a "de facto" primary
key, and in this case I have done the addition:

Expr8: (CStr([AddressesID]))+(CStr(Format([InvoiceDate],"mmmyy"))), which
gives me a unique set of values in a column on which I can base a combo
selection.

Can someone tell me if this is OK, and there will not be time bombs later
on?

Regards, Frank
****


Now everything works and we are sending out statements at last.

Many thanks, Frank

Ok, if the query you sent is the Row Source for Combo6, I count a total of
6 columns. Which column is the bound column? Are the values in this column
unique?

--
Wayne Morgan
MS Access MVP

I use two queries for the Statement system.
The first, the one I posted is just the one used for the ComboBox look
up,
to supply the parameters "AddressesID" and the month "mmmyy" which I plan
to
pass to the query used for the RptStatement and this one is QryInvoice.

But the problem is isolated to the first query (the one I posted) because
the combo box based on it will not return the fields selected for the
text
fields on the statement pop-up form.
"=[Combo6].[Column](0)" and
"=[Combo6].[Column](2)"

These two ColumnProperties should return a Customer and its corresponding
month from a list as shown below, based on the combo I posted.

The following is an reduced example of the ComboBox display showing two
columns only:

Customer: Month:
NV Soap Aug05
NV Soap Jul05
NV Soap Jun05
NV Soap May05
MelCo Aug05
MelCo Jul05
SaltCo May05
SaltCo Apr05
SaltCo Feb05

If a record is selected from this combo, say (NVSoap, May05) then the
ColumnProperty will return only (NV Soap, Aug05) which is the top month.
Similarly if (SaltCo, Feb05) is selected then only (SaltCo, May05) is
returned!
In all cases all that is returned is the top month of a selected
customer,
and this appears to be a problem with the Column Property.












message

I do most of mine in the design grid also, but the SQL is easier to paste
into a message.

I don't see any where in your query where you are referring to a control
on a form. Are you doing that in one of the queries that this query is
built on?

--
Wayne Morgan
MS Access MVP


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.


message 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