Using combo-box selection to populate a SQL Function criteria

[Ernie]

I am currently using a function as the data source for a report that
displays inventory at certain warehouses. I am using LIKE @Warehouse
and LIKE @PartNumber parameter query prompts in the CRITERIA section of
the function to help filter the report. The user can enter specific
warehouse or part data OR they can enter % as a wildcard to display ALL
inventory and/or ALL part data.

The problem with this is that the user has to know the warehouse and
part number data or the report is blank.

To solve, I have created a form with Warehouse and PartNumber comb
boxes that the user can select a warehouse and part number.

HOW do I pass these selections (including the wildcard %) to the
function's criteria field to generate the same report?

Any help is GREATLY appreciated.

 

[Guest]

in the Criteria field of the Query, while in design view put:

[Forms]![YourFormName].[ComboBoxName]

The [Forms]! is literal - you must put that to tell it to look for a
form....the remainder is your specific value(s)

also the Form must be open when you run the query....so that it can find the
value

 

[Ernie]

That works for an Access .mdb database (and I have used that many times
with forms and comb boxes) however, it does not work like that using
Access .adp and SQL back-end functions. I need specific details as to
what to put in the Function Criteria field that allows me to use the
selection from my combo boxes as criteria. When I enter what you
suggest in the criteria field, it reformats it as
N'[Forms!]![FormName]![ComboBoxName]' and yields no results in the
report.

in the Criteria field of the Query, while in design view put:

[Forms]![YourFormName].[ComboBoxName]

The [Forms]! is literal - you must put that to tell it to look for a
form....the remainder is your specific value(s)

also the Form must be open when you run the query....so that it can find the
value

--
NTC

I am currently using a function as the data source for a report that
displays inventory at certain warehouses. I am using LIKE @Warehouse
and LIKE @PartNumber parameter query prompts in the CRITERIA section of
the function to help filter the report. The user can enter specific
warehouse or part data OR they can enter % as a wildcard to display ALL
inventory and/or ALL part data.

The problem with this is that the user has to know the warehouse and
part number data or the report is blank.

To solve, I have created a form with Warehouse and PartNumber comb
boxes that the user can select a warehouse and part number.

HOW do I pass these selections (including the wildcard %) to the
function's criteria field to generate the same report?

Any help is GREATLY appreciated.- Hide quoted text -- Show quoted text -

 

[Guest]

ah - ok....yeah I don't work in those formats so the advice wasn't quite
right - sorry... probably another more hard core MVPer will have advice....

wondering out loud - can one take a look at the Access query initially set
up in design view and working....and then take a look at it via sql
view...and use that sql command approach as an example?? not sure....
--
NTC

That works for an Access .mdb database (and I have used that many times
with forms and comb boxes) however, it does not work like that using
Access .adp and SQL back-end functions. I need specific details as to
what to put in the Function Criteria field that allows me to use the
selection from my combo boxes as criteria. When I enter what you
suggest in the criteria field, it reformats it as
N'[Forms!]![FormName]![ComboBoxName]' and yields no results in the
report.

in the Criteria field of the Query, while in design view put:

[Forms]![YourFormName].[ComboBoxName]

The [Forms]! is literal - you must put that to tell it to look for a
form....the remainder is your specific value(s)

also the Form must be open when you run the query....so that it can find the
value

--
NTC

I am currently using a function as the data source for a report that
displays inventory at certain warehouses. I am using LIKE @Warehouse
and LIKE @PartNumber parameter query prompts in the CRITERIA section of
the function to help filter the report. The user can enter specific
warehouse or part data OR they can enter % as a wildcard to display ALL
inventory and/or ALL part data.

The problem with this is that the user has to know the warehouse and
part number data or the report is blank.

To solve, I have created a form with Warehouse and PartNumber comb
boxes that the user can select a warehouse and part number.

HOW do I pass these selections (including the wildcard %) to the
function's criteria field to generate the same report?

Any help is GREATLY appreciated.- Hide quoted text -- Show quoted text -

 

[Guest]

Hi, Ernie

I have just faced the same problem and I am serching for "best practice".

At this point I am working on a perhaps more creative solution. Right now I
am trying to:
1) Link the form containing the combo to a "universal" table.
2)Make a VBA which copy the information from the combo to a hidden text box
connected to a specific field in the unversal table.
3) Make a "universal" query with the universal table.
4) Use the universal query in whatever query you want.


Comments are welcome...

GB, Denmark

----------------------------------

ah - ok....yeah I don't work in those formats so the advice wasn't quite
right - sorry... probably another more hard core MVPer will have advice....

wondering out loud - can one take a look at the Access query initially set
up in design view and working....and then take a look at it via sql
view...and use that sql command approach as an example?? not sure....
--
NTC

That works for an Access .mdb database (and I have used that many times
with forms and comb boxes) however, it does not work like that using
Access .adp and SQL back-end functions. I need specific details as to
what to put in the Function Criteria field that allows me to use the
selection from my combo boxes as criteria. When I enter what you
suggest in the criteria field, it reformats it as
N'[Forms!]![FormName]![ComboBoxName]' and yields no results in the
report.

in the Criteria field of the Query, while in design view put:

[Forms]![YourFormName].[ComboBoxName]

The [Forms]! is literal - you must put that to tell it to look for a
form....the remainder is your specific value(s)

also the Form must be open when you run the query....so that it can find the
value

--
NTC



:
I am currently using a function as the data source for a report that
displays inventory at certain warehouses. I am using LIKE @Warehouse
and LIKE @PartNumber parameter query prompts in the CRITERIA section of
the function to help filter the report. The user can enter specific
warehouse or part data OR they can enter % as a wildcard to display ALL
inventory and/or ALL part data.

The problem with this is that the user has to know the warehouse and
part number data or the report is blank.

To solve, I have created a form with Warehouse and PartNumber comb
boxes that the user can select a warehouse and part number.

HOW do I pass these selections (including the wildcard %) to the
function's criteria field to generate the same report?

Any help is GREATLY appreciated.- Hide quoted text -- Show quoted text -