Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Reports _ How To Filter Report Query With In Operator

Posted by: Peter Hibbs Dec 6 2017, 10:02 AM

Hi,

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 http://www.UtterAccess.com/forum/index.php?showtopic=1984616 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 :-

"Condiments","Confections","Produce"

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.


 MVF_Report_Demo.zip ( 426K ): 4
 

Posted by: nuclear_nick Dec 6 2017, 10:12 AM

 Local_Parameters.txt ( 11.88K ): 3


I've been waiting for the right time...

Attached is a 'replacement' for tempvars, IMO, storing and retrieving the information from a table.

Because of my 'custom' err handling, it has been removed, feel free to add your own.

It should work with 2003, as it's made with DAO.

Let me know what you think.

One word of caution... when you use 'GetParameter' in a query, it helps if you surround it with a conversion type, if it is not text. For instance, 'Clng(GetParameter(<parametername>)).

If you have any other questions, let me know.

Posted by: Peter Hibbs Dec 6 2017, 12:03 PM

Hi Nick,

Sorry for the delay, got called away as soon as I hit Submit.

Anyway, thanks for the code although I don't think it will work in this situation. I tried my original version out using a 2010 version and used TempVars in place of the Text box and it still didn't work so I am guessing that your code will not work either. Also, I copied the code into a Module and Compiled it and it came up with an error that it could not find routine FixValueForSQL. The code looks interesting and I am sure it would be useful in an Access 2003 environment but not for this particular problem. If you can let me know how to fix the Compile issue I will have a look at it in more detail later.

Thanks again,

Peter.

Posted by: nuclear_nick Dec 6 2017, 12:46 PM

Not a problem. Sorry I didn't fully put in all the 'extra' code.

 Local_Parameters.txt ( 14.28K ): 3


There.

The way I would do it is to set up the string, and add the 'in'...

CODE
strValue = "In ('Condiments','Confections','Produce')"

... set that to a 'parameter'...
CODE
SetParameter "SQLinString",strValue

... then in the query itself, in the criteria...
CODE
WHERE MyItems GetParameter("SQLinString")


I think that will work...

Posted by: Peter Hibbs Dec 6 2017, 01:36 PM

Hi Nick,

OK, got that. However it still errors out with Compile.

Attribute VB_Name = "Local Parameters" at the top of the code gives an error (it did on the first version but I remmed it out) and also this line :-

If Not ObjectExists(db, acTable, LOCAL_PARAM_TBL) Then

in Public Function SetParameter errors out with ObjectExists highlighted.

I don't think I will be able to test anything until we can fix these erors, sorry.

Peter.

Posted by: nuclear_nick Dec 6 2017, 01:58 PM

Sorry... I should have put it in a blank database and tested...

CODE
'---5----1----5----2----5----3----5----4----5----5----5----6----5----7----5----8
' Author    : NTodd
' Last Date : 6/1/2011
' Purpose   :
'---5----1----5----2----5----3----5----4----5----5----5----6----5----7----5----8
Public Function ObjectExists(db As Database, ObjectType As AcObjectType, _
    ObjectName As String) As Boolean

  Dim i As Long
  Dim Objects As Object
  Dim Result As Boolean

  Result = False
  i = 0
  
  Select Case ObjectType
  
    Case acTable
      Set Objects = db.TableDefs
    
    Case acQuery
      Set Objects = db.QueryDefs
  
    Case acForm
      Set Objects = db.Containers("Forms").Documents
    
    Case acReport
      Set Objects = db.Containers("Reports").Documents
    
  End Select
  
  While (i < Objects.Count) And Not Result
  
    Result = (Objects(i).Name = ObjectName)
    i = i + 1
    
  Wend
  
  ObjectExists = Result

End Function


The first line just names the module if you use the 'import file' functionality in the IDE, so if you just copy/paste, then yeah, that line can be omitted.

I think that should be the last of it... I'll be going over it again while you add the above... smile.gif

Posted by: nuclear_nick Dec 6 2017, 02:16 PM

 Local_Parameters.txt ( 15.2K ): 0


Alright... did what I should have done in the first place...

I put the 'module' into a blank database and debug/compiled it until it worked.

Sorry I didn't think of that first.

Posted by: Peter Hibbs Dec 6 2017, 02:30 PM

Hi Nick,

OK, your code compiles OK now but unfortunately it does not solve the problem. I didn't think it would because I don't think the issue is with the VBA coding itself, I think it is something to do with how the queries work regarding the construction of the criteria part. There may be some specific formatting of the criteria string which might fix it or it may not be possible using this method, hopefully the former.

I think your code will be very useful though for any 2003 DBs that I may get involved with in the future so thanks for that.

If you (or anyone else) has any other thoughts on how to fix the problem I will be happy to hear about them.

Thank for your help,

Peter.

Posted by: nuclear_nick Dec 6 2017, 02:41 PM

I'm looking for exactly how, because I believe I've used what I did before in the construction of the 'in'...

I'll keep you posted.

Posted by: tina t Dec 6 2017, 05:02 PM

hi Peter, i'm wondering if an alternate approach would work: write some code in the specific report's Open event procedure, to set and apply a filter, as

CODE
Private Sub Report_Open(Cancel As Integer)
  
      Me.Filter = "FieldName In (" & [Forms]![frmPrintReport]![txtCat] & ")"
      Me.FilterOn = True
  
  End Sub

you'd have to make sure to use single outer quotes if the txtCat control uses double quotes around the values, or vice versa, of course. i didn't test this idea; figured you can run a test if you're interested in trying it. :)

hth
tina

Posted by: Peter Hibbs Dec 6 2017, 05:51 PM

Hi Tina,

WOW, that works perfectly and only requires two lines of code. Very clever! Thanks very much.

As a matter of interest I did come up with another solution. What I did was to replace the WHERE clause of the query with the actual categories string from the Text box just before the report is opened. In the query itself I set the criteria to -

In ('ZZZZ')

and then replaced that part of the query string with the category names, it looks something like this :-

CODE
    vSaved = CurrentDb.QueryDefs("qryCategories").SQL         'save the query string
    vSQL = Replace(vSaved, "'ZZZZ'", Me.txtCat)                                 'replace the criteria part with the category names
    CurrentDb.QueryDefs("qryCategories").SQL = vSQL                      'and copy the string back to the query
    DoCmd.OpenReport "rptCategories", acViewPreview                     'open report
    CurrentDb.QueryDefs("qryCategories").SQL = vSaved
'restore the original query string after report opened

Just thought I would mention it in case anyone could use a similar system but your solution is much simpler and easier to implement so I will be using that instead.

Thanks Tina and Nick for some novel ideas.

Peter.


Posted by: tina t Dec 6 2017, 08:31 PM

cool, Peter, glad it worked for you! and you're welcome, Nick and i are always glad to help. :) tina

Posted by: nuclear_nick Dec 7 2017, 11:41 AM

I figure it fair... I still use your calendar. smile.gif

Glad you got it! Good luck with your project!

Posted by: projecttoday Dec 8 2017, 08:18 AM

Just wondering why you wouldn't load these selections into a temporary table and do an inner join of that to your table.

Posted by: Peter Hibbs Dec 8 2017, 04:11 PM

Robert,

Well, the database itself is a lot more complicated than the demo version I posted and the MVF Combo control is used by several reports and queries so I guess it would get pretty complex. The solution from Tina works perfectly and is a lot easier to implement in my situation so that is what I am going with.

Thanks for your interest though.

Peter.