Helpt with InStr Function

[Guest]

I have an unbound form with combo boxes to open a report. I want to be able
to pull "3" reports for three different companies at the same time.

I have this expression in a query:
InStr([Ipeds code separated by commas,Blank=All],[Inst Base Data
1-19-06B2]![Ipeds_cd])

It works fine in the query, but I want to be able to use this on a form in a
combo box. How can I do this. Thanks for any help.

 

[Ken Snell \(MVP\)]

Use it in what way? To display the value that results from the InStr
function as a column in the combo box's dropdown list? Just include the
expression in the combo box's RowSource query, and be sure that the
ColumnCount is large enough for all the fields in that query, and that
you've set the ColumnWidths property to show the desired columns.

 

[Guest]

Thanks for replying.

What I would like to do is to be able to pull reports for different
companies at the same time. Right now I have an unbound form with combo
boxes and text boxes to enter criteria to get a report. Which works fine.
But, if I need to pull, lets say, 20 reports for 20 different companies I
have to do it one at time. I know how to pull "20" specific records using
InStr function in a query but I don't know how to incorporate this into the
form in a the combo box. For example, in the query I add a column using the
InStr function like this:

New Field: InStr([Ipeds code separated by commas,Blank=All],[Ipeds_cd]

Total: Expression

Criteria: >0 Or Is null

Then It will prompt me to enter the Ipeds_cd, where I enter 2 or 3 or 5
different Ipeds code by separating it by a comma like this
(100654,215293,100724) and then I get the records I specify only. I would
like to do this using the combo box in the unbound form to pull the report.

I've tried:

New Field: InStr([Ipeds code separated by commas,Blank=All],[forms]![Main
Menu]![Ipeds_cd] but it does not work. Hopefully you understand me now.
Thanks!

Use it in what way? To display the value that results from the InStr
function as a column in the combo box's dropdown list? Just include the
expression in the combo box's RowSource query, and be sure that the
ColumnCount is large enough for all the fields in that query, and that
you've set the ColumnWidths property to show the desired columns.

--

Ken Snell
<MS ACCESS MVP>

I have an unbound form with combo boxes to open a report. I want to be
able
to pull "3" reports for three different companies at the same time.

I have this expression in a query:
InStr([Ipeds code separated by commas,Blank=All],[Inst Base Data
1-19-06B2]![Ipeds_cd])

It works fine in the query, but I want to be able to use this on a form in
a
combo box. How can I do this. Thanks for any help.

 

[Ken Snell \(MVP\)]

Perhaps the approach that you can best use is to put all the company options
in a listbox whose Multiselect property is set to Yes. Then the user can
select all the companies desired for the reports, and then you can use VBA
programming to loop through the selected items in order to print a report
for each company individually; or to print one report that contains all the
companies.

I have a sample database on the web that shows how you can use various
controls, including a multiselect listbox, to filter a report. See
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm for the database. The
code in the form shows how you can read the values of the listbox and, in
the case of this sample, use all of them in "AND" logic to filter the
report. Modifying the code for "OR" logic is straightforward. Also,
modifying the code to print one report for each selection would mean putting
a DoCmd.OpenReport action inside a loop that reads each selection - not
quite as straightforward, but if that is what you want to do, post back
after you've looked at the sample db and we can pursue that.

Or post back if you have other questions -- or success! ;-)

--

Ken Snell
<MS ACCESS MVP>

Thanks for replying.

What I would like to do is to be able to pull reports for different
companies at the same time. Right now I have an unbound form with combo
boxes and text boxes to enter criteria to get a report. Which works fine.
But, if I need to pull, lets say, 20 reports for 20 different companies I
have to do it one at time. I know how to pull "20" specific records using
InStr function in a query but I don't know how to incorporate this into
the
form in a the combo box. For example, in the query I add a column using
the
InStr function like this:

New Field: InStr([Ipeds code separated by commas,Blank=All],[Ipeds_cd]

Total: Expression

Criteria: >0 Or Is null

Then It will prompt me to enter the Ipeds_cd, where I enter 2 or 3 or 5
different Ipeds code by separating it by a comma like this
(100654,215293,100724) and then I get the records I specify only. I would
like to do this using the combo box in the unbound form to pull the
report.

I've tried:

New Field: InStr([Ipeds code separated by commas,Blank=All],[forms]![Main
Menu]![Ipeds_cd] but it does not work. Hopefully you understand me now.
Thanks!

Use it in what way? To display the value that results from the InStr
function as a column in the combo box's dropdown list? Just include the
expression in the combo box's RowSource query, and be sure that the
ColumnCount is large enough for all the fields in that query, and that
you've set the ColumnWidths property to show the desired columns.

--

Ken Snell
<MS ACCESS MVP>

I have an unbound form with combo boxes to open a report. I want to be
able
to pull "3" reports for three different companies at the same time.

I have this expression in a query:
InStr([Ipeds code separated by commas,Blank=All],[Inst Base Data
1-19-06B2]![Ipeds_cd])

It works fine in the query, but I want to be able to use this on a form
in
a
combo box. How can I do this. Thanks for any help.