I searched a few days to come up with all these answers, so I'd like
to share my knowledge.

I wanted to export to excel using multiple criteria based on either
region number or county name (text) using a list box to select which.
My goal was to not embed any plain text sql.

For some reason the query def wouldn't work for me, maybe because I
was using - In([qryCriteria]).

Instead I set up two single field tables (jtCounty, jtRegion). These
are emptied then updated from the list box selections.

Two make table queries are then used - one for region, one for county
since the fields are different types (number, text). The table
(jtCounty or jtRegion) is joined to the main table where I'm getting
all my info.

Procedure -

1. Select list rowsource to populate lstMain. I used two buttons:
cmdByRegion, cmdByCounty.

2. Select items from lstMain.

3. Add to criteria list (lstMainSelected) with the cmdAddToSelected or
clear both lists with cmdClearSelected.

4. Finally, use cmdExportExcel to create the spreadsheet.

The make table queries create a table called tblExcelExport. I use Ken
Getz's module for the Save As dialog box:

http://www.mvps.org/access/api/api0001.htm


-----------------------------------------------------------------------
Option Compare Database
Public strFilterBy As String

Private Sub cmdAddToSelected_Click()
Dim i As Integer
Dim strCriteria As String

EmptyTable

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb

If Me!lstMain.RowSource = "qLkpRegion" Then
Set rst = db.OpenRecordset("jtRegion")
Else
Set rst = db.OpenRecordset("jtCounty")
End If

Set lst1 = Me!lstMain
Set lst2 = Me!lstMainSelected

lst2.RowSource = ""

With lst1
For i = 0 To .ListCount - 1
If (.Selected(i)) <> 0 Then
lst2.AddItem .ItemData(i)
rst.AddNew
rst!field1 = .ItemData(i)
rst.Update
End If
Next
End With

End Sub

Private Sub cmdClearSelected_Click()
Dim varItem As Variant

Me!lstMainSelected.RowSource = ""

For Each varItem In Me!lstMain.ItemsSelected
Me!lstMain.Selected(varItem) = False
Next

End Sub

Private Sub cmdExportExcel_Click()
On Error Resume Next
Dim db As DAO.Database
Dim strFilter As String
Dim strInputFileName As String

Set db = DBEngine(0)(0)
db.Execute "DROP TABLE tblExcelExport;"


If Me!lstMain.RowSource = "qLkpRegion" Then
DoCmd.OpenQuery "qryExcelExport_Region"
Else
DoCmd.OpenQuery "qryExcelExport_County"
End If

strFilter = ahtAddFilterItem(strFilter, "Excel File (*.xls)",
"*.xls")

strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, _
OpenFile:=False, _
DialogTitle:="Save File As...", _
Flags:=ahtOFN_HIDEREADONLY)

If Len(strInputFileName) <> 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tblExcelExport", strInputFileName, True
End If

End Sub

Private Sub cmdByRegion_Click()
Me!lstMain.RowSource = "qLkpRegion"
End Sub

Private Sub cmdByCounty_Click()
Me!lstMain.RowSource = "qLkpCounty"
End Sub

Private Sub EmptyTable()

Dim db As DAO.Database
Dim strSql As String
Set db = DBEngine(0)(0)

If Me!lstMain.RowSource = "qLkpRegion" Then
strSql = "DELETE FROM jtRegion;"
Else
strSql = "DELETE FROM jtCounty;"
End If

db.Execute strSql, dbFailOnError

End Sub

Glad you got it working!

One enhancement you might consider is to put jtCounty and jtRegion into a
temporary database, so that you can avoid the bloating that will occur from
repeated deleting and adding the tables.

Tony Toews has an example at http://www.granite.ab.ca/access/temptables.htm

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

<christianlott1 (AT) yahoo (DOT) com> wrote

On Jun 30, 12:48*pm, "Douglas J. Steele"
<NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote:
Thanks Douglas. The tblExcelExport is the one that gets deleted, the
jtRegion/jtCounty get emptied. The tblExcelExport is not linked to any
other table. jtRegion/jtCounty are only linked to the main table in
the query, not the db design.

Will it still bloat?

Thanks.

Any time you delete rows from tables and repopulate them, you're going to
get bloat. That's because Access doesn't actually return the space that was
used unless you do a Compact and Repair of the database.

How much bloat you'll get depends on how large the tables are and how
frequently you perform the deletions and repopulations.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

<christianlott1 (AT) yahoo (DOT) com> wrote

On Jun 30, 12:48 pm, "Douglas J. Steele"
<NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote:
Thanks Douglas. The tblExcelExport is the one that gets deleted, the
jtRegion/jtCounty get emptied. The tblExcelExport is not linked to any
other table. jtRegion/jtCounty are only linked to the main table in
the query, not the db design.

Will it still bloat?

Thanks.

On Jun 30, 1:28*pm, "Douglas J. Steele"
<NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote:
Then maybe I should delete and create the tables each time I open the
form? They are simple one field tables.

Will this bloat as well?

Thanks.

While you say "one field tables", how many rows are they typically going to
have in them?

Whether you delete the table and recreate it, or delete the data from the
table and append to it makes no difference from the point of view of
bloating.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

<christianlott1 (AT) yahoo (DOT) com> wrote

On Jun 30, 1:28 pm, "Douglas J. Steele"
<NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote:
Then maybe I should delete and create the tables each time I open the
form? They are simple one field tables.

Will this bloat as well?

Thanks.

On Jun 30, 2:07*pm, "Douglas J. Steele"
<NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote:
5 or 6 more or less. County names or just 1-16 (numbers) for regions.

The tblExcelExport will have about 25 columns and four or five hundred
rows at a time.

Maybe there's someway I can compact and repair when I close the form?


Ah. OK. Thanks.

I originally wanted to filter with qdf.parameters but it gave errors I
think because the [parameter] was wrapped with an In() clause.

Either way I'd still have tblExcelExport to deal with..

Thanks.

Why not just use a temporary database? It's far easier than trying to kludge
together some way to force a compact.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

<christianlott1 (AT) yahoo (DOT) com> wrote

On Jun 30, 2:07 pm, "Douglas J. Steele"
<NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote:
5 or 6 more or less. County names or just 1-16 (numbers) for regions.

The tblExcelExport will have about 25 columns and four or five hundred
rows at a time.

Maybe there's someway I can compact and repair when I close the form?


Ah. OK. Thanks.

I originally wanted to filter with qdf.parameters but it gave errors I
think because the [parameter] was wrapped with an In() clause.

Either way I'd still have tblExcelExport to deal with..

Thanks.

christianlott1 (AT) yahoo (DOT) com wrote:
While I'm of the opinion that from a performance standpoint, you've
already chosen the best method, there are other ways to parameterize the
IN clause, which I will list at the end of this.

Here is a way to avoid creating and deleting tables:
Given that there are likely to be fixed sets of counties and regions
from which the user can select, I would suggest creating tables for each
entity, populating them with the entire lists of possible counties and
regions, and adding a [Selected] column to each. Then bind continuous
subforms to each table (instead of using listboxes), and allow the user
to make the selections that get saved in the tables. In the export
query, filter on the [Selected] records. At the end, run update queries
to clear the selections (and/or clear the selections at the beginning of
the process as well).

Now that I think of it, counties and regions could be related couldn't
they? Perhaps a cascading selection scheme is called for: i.e., the user
selects a region and is presented with the list of counties within that
region from which to select. Does your database design support this?

Here is a compilation of posts about how to deal with this issue:

There are two solutions for this problem listed in the following KB
article
(Q210530 - ACC2000: How to Create a Parameter In() Statement),

http://support.microsoft.com/kb/210530/

The first solution uses Instr() to test the field values against the
list in
the parameter. The second involves dynamically creating a SQL statement
in
code.

Thanks to Paul Overway, here is a third solution, using the Eval
function:

WHERE (((Eval([Table]![Field] & " In(" &
[Forms]![Formname]![textboxname] &
")"))=True))

or, using a prompted parameter:

WHERE (((Eval([Table]![Field] & " In(" & [Enter List] & ")"))=True))


Thanks to Jeffrey A. Williams, here's a 4th solution:

If you don't mind adding a table to your database, and you're
comfortable
dealing with possible multi-user issues, this will perform better than
either of the solutions that involve running a function (Instr or Eval)
on
every row of your table:

Create a new table with two fields:

tblCriteria:
Criteria text
Selected boolean (yes/no)

Populate the table with your values and select a couple of items. Now
you
can use this table in your query as such:

Select * from table1
inner join tblcriteria
on table1.[your criteria field] = tblcriteria.criteria
where tblcriteria.selected = true

You can easily setup a form (or subform) that is bound to tblCriteria
and
allow the users the
ability of selecting which values they want.



Thanks to Michel Walsh, here's yet another way:

SELECT Table3.ConName, Table3.State, Table3.Zip
FROM Table3
WHERE "," &[list] & "," LIKE "*," & [ConName] & ",*"

with [param] some string like: '1,4,5,7'

note that there is no space after the comas.


It works simply. If AccountID is 45, clearly ',1,4,5,7,' LIKE
'*,45,*' returns false.
If AccountID is 4, on the other hand, ',1,4,5,7,' LIKE '*,4,*'
returns true.

So, you have, in effect, an IN( ) where the list is a parameter.




--
HTH,
Bob Barrows

On Jun 30, 3:03*pm, "Bob Barrows" <reb01... (AT) NOyahoo (DOT) SPAMcom> wrote:

Yes. This is a better solution. Thanks

Many counties belong to each region. They want to be able to select a
few regions and a few counties outside those regions. My original
design was either county or region, not both. The new design I just
finished can do both but also uses a union query. I can redesign to
not use those two jtRegion/jtCounty tables but the table that loads
the most rows is the tblExcelExport. Too bad DoCmd.TransferSpreadsheet
won't accept a query as a source instead of needing a table

Thanks for the ideas Bob.