[AccessD] Creating Filter Form for Report

If you get too long a string of 'OR' items, Access will complain of the
string being more than 2000-some characters.

Then you move to an IN string (where lngMyVariable in (1234, 2345,
3456)), because you waste fewer characters on OR that way.

However, if that gets too long, a truly mysterious group of overflow
errors is generated.  Then you have to move to a temp table.

The experience that generated this knowledge did not involve users
selecting thousands of items from a multi-select listbox directly, but
instead a group of criteria working together to produce puzzling
results.


Liz


 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Thursday, May 25, 2006 11:16 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Creating Filter Form for Report

Bryan,

Below is code I use to open a report filtered by a multi-select list
box.

Good Luck,

Mark A. Matte

P.S...Don't forget to tweak the LEN function to trim up your filter.

****************************************
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
    Set frm = Me
    Set ctl = frm!lstBusType
    strSQL = "[BusTypeID]="
    For Each varItem In ctl.ItemsSelected

        strSQL = strSQL & ctl.ItemData(varItem) & " OR [BusTypeID]="
    Next varItem
strSQL = Left$(strSQL, Len(strSQL) - 16)
DoCmd.OpenReport "rptBusinessType", acViewPreview, , strSQL
********************************************


>From: "Bryan Carbonnell" <carbonnb at gmail.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem 
>solving"<accessd at databaseadvisors.com>
>Subject: [AccessD] Creating Filter Form for Report
>Date: Thu, 25 May 2006 11:14:18 -0400
>
>I need some help (with Access this time :) because my brain isnt'
>working well today and I haven't really done any dev in Access for far
>too long.
>
>Here is what I am trying to do: I need to generate a report that will
>shom me what courses individuals have had. I need to be able to select
>one or a varyiong number of participants and then have it show me what
>courses they have had.
>
>What I was thinking was have a form that lists the individuals in a
>multi-select listbox, and then use that as a parameter for the query
>the report is based on, but I'm having a serious mind gap as to how to
>get the info from the lsit box to the query.
>
>HELP!!! Please??!?!
>
>--
>Bryan Carbonnell - carbonnb at gmail.com
>Life's journey is not to arrive at the grave safely in a well
>preserved body, but rather to skid in sideways, totally worn out,
>shouting "What a great ride!"
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com