X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> How To Filter Report Query With In Operator, Any Version    
Peter Hibbs
post Dec 6 2017, 10:02 AM

UtterAccess VIP
Posts: 1,606
Joined: 17-June 10
From: Dorset. UK.


This should be easy (and probably is) but I have not found a solution yet.

I have created a MVF Combo box on a form which is loosely based on a demo by pere de chipstick in the UA Archive. This bit works well but I now need to output the report as a .pdf (or other format) which, in effect, uses the selected items in the Combo box using the IN operator. What I have so far is that when the user selects one or more items in the Combo control they are stored in the control as a comma delimited string. In the AfterUpdate event of the Combo control I convert the string into a new string which is stored in a Text box on the form and which can then be used as the criteria for the IN operator in the query which is bound to the report. So, for example, if the Combo control holds some thing like :-

Condiments, Confections, Produce

then the temporary Text box on the form would hold something like :-


and the Criteria box in the query would look like this :-

In ([Forms]![frmPrintReport]![txtCat])

The problem is that this does not work, the query just returns zero records. I know the query and the IN operator work because if I replace the criteria in the query with :-

In ("Condiments","Confections","Produce") it works correctly.

I have tried numerous variations of the string like doubling up the quotes, using single quotes, using an Eval function, etc, etc but nothing works. Have also tried not using the IN operator and just using multiple OR operators like "Condiments" Or "Confections" Or "Produce" but that doesn't work either.

Note that this is a 'generic' form in that it is used by lots of other reports and is also used to send reports in emails, reports as pdf files, etc so I cannot use the normal DoCmd.OpenReport command since that command does not have those facilities. Also, I cannot use the TempVars facility as this is not available in A2003 (.mdb) files. I just need to find some way of formatting the string so that the query will recognise it (if it is even possible).

In case anyone wants to have a play with this I have created a small demo DB (which is attached) which uses the Northwind data as an example. The Combo control selects one or more categories which are then formatted in the yellow Text box (which would normally be hidden, of course) and that control is used in the query as the criteria for the IN operator. Note that you can also edit this field if you want to try different string formats.

I have also included the alternative method using the DoCmd.OpenReport function in the code to show what it would look like on a report. In the Click event of the Print button you can REM out the DoCmd.OutputTo and UN-REM the DoCmd.OpenReport command to test it. You will also need to remove the criteria in the query itself for this to work.

Does anyone have any idea on how to make this work?

Peter Hibbs.

Attached File(s)
Attached File  MVF_Report_Demo.zip ( 426K )Number of downloads: 24
Go to the top of the page

Posts in this topic

Custom Search

RSSSearch   Top   Lo-Fi    17th November 2018 - 03:54 AM