Displaying Form Filter Criteria in a Report

[David Bolten]

I have a form with 3 filter criteria activated by vba. Each filter criterion
is a combo box based on a SQL SELECT from a table. The bound column is the
key field ID number (column 1 with column width set to 0), but column 2 is
seen on screen in the form since it is a meaningful text and it has a width
of 1.0 inches.

I can display the key field in the report to show the filter criteria (it
shows up as the ID number), but I cannot figure out how to display the
"meaningful" text in the report.

The form filter criteria looks like this:
SELECT tblItemType.IDItemType, tblItemType.txtTypeName FROM tblItemType
WHERE (((tblItemType.[Active?])=Yes)) ORDER BY [txtTypeName];

The report control source for the criterion is:
=[Forms]![CREATE SHOPPING LIST]![cboItem]
Of course, the item shown is the IDItemType that is the key field. I want
the txtTypeName to be displayed.

Any thoughts?

Thanks.

 

[Allen Browne]

You may be able to use an expression like this:
=[Forms]![CREATE SHOPPING LIST]![cboItem].Column(1)

Note that the first column is zero, so 1 is the 2nd column.

If you can't get that working, you could use a DLookup() expression to get
the value. For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

 

[David Bolten]

Thanks a bunch, Allen!!!

I was close to figuring this one out. I just didn't realize that the first
column is 0...or how to write it. Your first suggestion works just fine.

Dave

You may be able to use an expression like this:
=[Forms]![CREATE SHOPPING LIST]![cboItem].Column(1)

Note that the first column is zero, so 1 is the 2nd column.

If you can't get that working, you could use a DLookup() expression to get
the value. For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with 3 filter criteria activated by vba. Each filter
criterion is a combo box based on a SQL SELECT from a table. The bound
column is the key field ID number (column 1 with column width set to 0),
but column 2 is seen on screen in the form since it is a meaningful text
and it has a width of 1.0 inches.

I can display the key field in the report to show the filter criteria (it
shows up as the ID number), but I cannot figure out how to display the
"meaningful" text in the report.

The form filter criteria looks like this:
SELECT tblItemType.IDItemType, tblItemType.txtTypeName FROM tblItemType
WHERE (((tblItemType.[Active?])=Yes)) ORDER BY [txtTypeName];

The report control source for the criterion is:
=[Forms]![CREATE SHOPPING LIST]![cboItem]
Of course, the item shown is the IDItemType that is the key field. I want
the txtTypeName to be displayed.

Any thoughts?

Thanks.