ListBox and ComboBox for Report Criteria

[BrokenBiker]

I've search through several databases that use multi-select list boxes as criteria for queries, but there was only one that used it to open reports. I can't remember the guy that made it, but I got it from this board.

At any rate, I can't get it to work. I get an error saying it's too complicated. The various codes that I've read concerning multi-list criteria in list boxes is above me. Is there someone here that could point out errors and help correct it?

The attached db has both the original form and report based from combo boxes and the attempt at using the list box. NOTE: You have to enter both start and end dates.

 

[whatever]

I have had a quick look and can find these :

1. The too complicated error is due to the field TEC being defined as text so you need to build quotes round your parameter string e.g. stWhat = stWhat & "'" & Me!TEC.ItemData(vItm) & "'" enclosing a single quote in two doubles to build the string.

2. Change the your select statement to "SELECT qryTECRateListBox.*, * FROM qryTECRateListBox WHERE TEC IN (" & Me!txtCriteria & ")"

3. Your syntax for the open report should read something like DoCmd.OpenReport "rptTECPercentListBox", acViewPreview

4. You need to take out the TEC criteria from qryTECRateListBox this gives you a list of all the 'TEC' which are then reduced by the SQL you created in 2.

This should then produce you a list of only those TECs that you have selected


HTH.



Kevin

 

[BrokenBiker]

Thanks for the help. I've tried it out at home using OfficeXP and got an error. This is the code that I have now.

Dim vItm As Variant
Dim stWhat As String
Dim stCriteria As String
Dim stSQL As String
Dim loqd As QueryDef

stWhat = "": stCriteria = ","
For Each vItm In Me!TEC.ItemsSelected
stWhat = stWhat & "'" & Me!TEC.ItemData(vItm) & "'"
stWhat = stWhat & stCriteria
Next vItm
Me!txtCriteria = CStr(Left$(stWhat, Len(stWhat) - Len(stCriteria)))
Set loqd = CurrentDb.QueryDefs("qryMultiSelect")
stSQL = "SELECT * "
SELECT qryTECRateListBox.*, * FROM qryTECRateListBox WHERE TEC IN ("& Me!txtCriteria & ")
stSQL = stSQL & " IN (" & Me!txtCriteria & ")"
loqd.SQL = stSQL
loqd.Close

DoCmd.OpenReport "rptTECPercentListBox", acViewPreview


The bold text is where I'm getting an error. While writing it in, the error said that a 'case' was expected. I tried running it from the form to see what would happen (maybe an error code or something), but the only error received from that was a syntax error.

I've used quite a bit of code before, but I'm lacking quite a bit of code 'basics.' This is over my head. I can read it make some basic sense out of it, but as far as adding a Case to it or fixing the syntax error....I'm lost.

 

[whatever]

Change This:

stSQL = "SELECT * "
SELECT qryTECRateListBox.*, * FROM qryTECRateListBox WHERE TEC IN ("& Me!txtCriteria & ")
stSQL = stSQL & " IN (" & Me!txtCriteria & ")"


to :

stSQL = "SELECT qryTECRateListBox.*, * FROM qryTECRateListBox WHERE TEC IN ("& Me!txtCriteria & ") "

debug.print stSQL



NB: The debug print statement allows you to view the sql string in the immediate window before implementing the change to your querydef.

 

[BrokenBiker]

Whoo!!!

Thanks! It works like a champ. Now I'm trying to include an 'all' function into it.

Thanks again! This is going to make a HUGE difference!

When I'm done w/ the 'all' function I'm going to load it under the Sample DBs forum. I'll make sure to give you credit for it.

 

[whatever]

I hope you get this working but I would just add a command button which selects everything in the list then your code can stay the same and you can avoid the "Add All to List" complications... which will involve you changing the SQL criteria depending if 'all' has been selected.

Good Luck.


kevin

 

[BrokenBiker]

Thanks for all the help, whatever. I decided not to pursue the 'all' function. It might come in handy in other portions of our db, but for this report it's really not necessary. I'm posting the finished version in the samble db forum.

http://www.access-programmers.co.uk/forums/showthread.php?p=482892#post482892

Thanks, again!

-BrokenBiker