Does not work for 1-9 1

[melaniews]

I copied the following code from another post (Bob Scriver created the code). It is working (well sort of working) in a list box with multiselect set to simple. The problem is that it only works for values that are 10 and above. When I select any of the values 1 through 9 I get nothing in the report. Or if I select 3 and 12 I only get 12.

Any help is appreciated.


Public Function ViewCriteriaResults()
Dim ctlList As Control, varItem As Variant, strCollect As String

'Return control object variable pointing to the list box.
Set ctlList = Forms![criteria]![lstCriteria]
strCollect = Empty

'enumerate through selected items.
For Each varItem In ctlList.ItemsSelected
'Create a list of the values of the bound columns of the listbox selection.
If strCollect = Empty Then
strCollect = " or " & CStr(ctlList.ItemData(varItem))
Else: strCollect = strCollect & " or " & CStr(ctlList.ItemData(varItem))
End If

'Debug.Print strCollect
Next varItem

ViewCriteriaResults = strCollect & " or "
'debug.print ViewCriteriaResults


End Function


TIA,
Melanie

 

[dhookom]

Where/how are you attempting to use the resulting string. This is a very limited usage piece of code that would work only in very specific situations. If you remove the apostrophe before debug.print what value do you get?

Duane
MS Access MVP

 

[melaniews]

Hello Duane,

Thanks for responding. I won't be able to check the debug until Friday when I'm back at that site.

This code is used to capture multiple values in a list box The resulting string is used in a query. It replaces a parameter query where box after box appeared for multiple criteria.

The user simply selects the criteria they want from the list box with multiselect property set to simple. Then they click a command button that runs the report based on the query which calls the module procedure to collect the string for the query. It really works well with any value higher than 9.

Please let me know if you need further information.
TIA,

Melanie

 

[dhookom]

The lines
If strCollect = Empty Then
strCollect = " or " & CStr(ctlList.ItemData(varItem))
Else: strCollect = strCollect & " or " & CStr(ctlList.ItemData(varItem))
End If
are virtually the same. You could change all this to one line:
strCollect = strCollect & " or " & CStr(ctlList.ItemData(varItem))
The code adds " or " either way and if strCollect is empty then the orginal code produces the same results.

It would be interesting to see both the report's record source and the debug.


Duane
MS Access MVP

 

[judgehopkins]

http://www.experts-exchange.com/Manager/Databases/MS_Access/Q_20540085.html#8083700

http://www.experts-exchange.com/Databases/MS_Access/Q_20665150.html#8833568

thread703-565398

These may possibly aid in your research. Check out Rick Sprague's post in the Tek-Tips thread cited above.

"In America, anyone can become president. That's one of the risks you take." - Adlai Stevenson

 

[melaniews]

Okay, I'm back dhookom. Hope you are too.

I do not know how to make the debug run so I can see the value. I have clicked the check box in the SHOW column of the Access query to see the results from what is shown below in SQL. It returns a double digit number, though the number is not the "interest_Id" that is selected. Of course I can't see anything if I pick one of the single digit numbers as it returns nothing.

Here's the query in SQL

SELECT DISTINCT dbo_INTEREST.INDIVIDUAL_ID, [Title] & " " & [FName] & " " & [MName] & " " & [Lname] & " " & [Suffix] AS Name, Trim([TITLE_PREFIX]) AS Title, Trim([FIRST_NAME]) AS FName, Trim([MIDDLE_NAME]) AS Mname, Trim([LAST_NAME]) AS LName, Trim([TITLE_SUFFIX]) AS Suffix, dbo_INDIVIDUAL.ADDRESS1, dbo_INDIVIDUAL.CITY, dbo_INDIVIDUAL.STATE, dbo_INDIVIDUAL.ZIP5, dbo_INDIVIDUAL.ZIP4, dbo_INDIVIDUAL.COUNTRY, dbo_INDIVIDUAL.PRIMARY_PHONE, dbo_INDIVIDUAL.EMAIL, InStr(1,ViewCriteriaResults(),[interest_id]), dbo_INTEREST.INTEREST
FROM dbo_INDIVIDUAL INNER JOIN dbo_INTEREST ON dbo_INDIVIDUAL.INDIVIDUAL_ID = dbo_INTEREST.INDIVIDUAL_ID
WHERE (((dbo_INDIVIDUAL.EMAIL) Is Not Null) AND ((InStr(1,ViewCriteriaResults(),[interest_id]))>0))
ORDER BY dbo_INTEREST.INDIVIDUAL_ID;


I have an additional issue with this. It returns the names and email addresses of those meeting the requested criteria. However, if a person has expressed interest in more than one area, their name and email shows up a second or third or fourth time.

Please let me know what else might be helpful for you to know.

TIA,
Melanie

 

[dhookom]

I am not sure what goes on with Bob's code. Please take a look at the Reports Thread "Passing variable to report". There is generic code in the thread and instructions on how to use it.

Duane
MS Access MVP

 

[melaniews]

Thanks. I checked out that thread. I don't think I really followed it well. I copied some of the ideas out and tried to use them and got nowhere.

I'm back at square one again. Same problem.

Melanie

 

[dhookom]

I would not use the list box selection criteria in the query. I would instead rename your list box "lboNInterest_id". Use the code from

http://www.tek-tips.com/faqs.cfm?spid=703&sfid=3936.

The code in your form to open the report would be:

Dim strWhere as String
strWhere = " 1=1 " & BuildIn(Me.lboNInterest_ID)
Docmd.OpenReport "rptName", acviewPreview, , strWhere


Duane
MS Access MVP

 

[melaniews]

Ok, great. I'll look at that and see if I can modify this thing. I've worked on it for hours today and gotten nowhere.

I'll be out of here for about 10 days. I'll be back Wednesday or Thursday Aug 6 or 7 so it may be a while before I get back to you.

TIA,
Melanie

 

[melaniews]

Duane,

Just wanted you to know that I copied that code, figured out what it is supposed to do and made it work for me. It was a challenge but well worth it.

Thanks a bunch.

Melanie

 

[melaniews]

One more thing -- and perhaps I need to open a new thread for this one -- please let me know if that is the case.

Because this builds a string with multiple criteria, my form result is returning multiple lines of data for some customers that meet more than one of the criteria. Is there a way to eliminate that duplication? I just want the customer one time even if they meet the criteria a second or third time.

TIA
Melanie

 

[dhookom]

You could eliminate the duplicates in a report by hiding the detail section. For forms, you would need to use the code provided to create the where clause for a query. This would amount to changing the SQL of a saved querydef.

Duane
MS Access MVP

 

[melaniews]

Thanks Duane

I am using a form....
So I go to the query in Access and change to SQL view -- correct so far???

I see the "Where" but have no clue as to what to put here to make it happen. Sorry if this is elementary, I'm new to SQL and VBA.

TIA,
Melanie

 

[dhookom]

This won't be simple...
You need a function that will modify the SQL of your query posted earlier (see below). Then you can use code like:
Dim strSQL as String
Dim strOldSQL As String
strSQL = "SELECT DISTINCT dbo_INTEREST.INDIVIDUAL..."
'include all up to the ((InStr(1,ViewCriteriaResults(),[interest_id]))>0)
'then add the remainder of the where
strSQL = strSQL & BuildIn(Me.lboNInterest_ID)
strSQL = strSQL & &quot ORDER BY dbo_INTEREST.INDIVIDUAL_ID;"
strOldSQL = ChangeSQL("yourqueryname",strSQL)

This will change the SQL of your saved query.

As I am typing this, I am afraid you will get more and more lost.


Function ChangeSQL(pstrQuery as String, pstrSQL As String) as String
Dim db as DAO.Database
Dim qd as DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(pstrQuery)
ChangeSQL = qd.SQL
qd.SQL = pstrSQL
set qd = nothing
set db = nothing
End Function

Duane
MS Access MVP

 

[melaniews]

thanks for the code. I am a little lost and can't get it to work. I think I follow what is supposed to be happening but I'm getting errors in the SQL statement part.

I'm going to repost with a more relevant subject as you have answered my first question.

Thanks for all your help.

Melanie

p.s. Another quick thought is why do I need to modify the SQL this way. Can't I just modify it in the Access SQL view of the query and then save the query?

 

[dhookom]

You would need to change the querydef because none of the fields that you want to query by are in the recordsource of the form.

For instance, if I want to open a form containing order information but only want records that have related OrderDetails records that contain a limited number of different products. Normally I would use a where clause in the OpenForm method but this would require that OrderDetail information would be contained in the Order table.

Duane
MS Access MVP

 

[melaniews]

This is similar to the direction I am moving in now. I need to create a table/query that has the subset of records that fit the criteria. There will be multiple lines for people meeting more than one criteria. I then need to get a unique listing of those people. I think I have been leaving out a step.

There should be a simple way to run a query to capture the subset and then run another query off of that one for unique records. When I open the form, the first query runs and then the second one runs and populates the form.

Is this similar to what you are doing with this code?

Melanie

 

[dhookom]

You 2nd paragraph above is exactly what I was promoting. The first query you mention is the one that the SQL would be modified to include the "IN (...)" where clause. Once this query gets modified then it can be used in another query that returns the unique records for the form.

Duane
MS Access MVP

 

[melaniews]

I'm trying your code again. Here's my code so far exactly as it appears--

Private Sub cmdEmailOnly_Click()

Dim strSQL As String
Dim strOldSQL As String
Dim strWhere As String

strSQL = "SELECT Trim([TITLE_PREFIX]) & ' ' & Trim([FIRST_NAME]) & ' ' & Trim([MIDDLE_NAME]) & ' ' & Trim([LAST_NAME]) & ' ' & Trim([TITLE_SUFFIX]) AS Name, dbo_INDIVIDUAL.EMAIL, dbo_INTEREST.INTEREST_ID, *"
ERROR HERE==>> FROM dbo_INDIVIDUAL INNER JOIN dbo_INTEREST ON dbo_INDIVIDUAL.INDIVIDUAL_ID = dbo_INTEREST.INDIVIDUAL_ID
WHERE (((dbo_INDIVIDUAL.EMAIL) Is Not Null))

strWhere = " 1=1 " & BuildIn(Me.lboTInterest_ID)
strSQL = strSQL & &quot ORDER BY dbo_INTEREST.INDIVIDUAL_ID;"
strOldSQL = ChangeSQL("yourqueryname", strSQL)
DoCmd.OpenForm "frmEmailResults", acFormDS, , strWhere
DoCmd.Maximize

End Sub

I'm not sure what my other query (you have noted it as "yourqueryname&quot is supposed to do.
The DoCmd.OpenForm with the where parameter is going to give me multiple lines I'm sure -- so do I still need it?

Also, I'm getting an error as noted above. I'm probably missing a comma or something

I've created the function procedure as a module -- I think that is what I was supposed to do.

TIA
Melanie