Problem with a Function as criteria in a query 7

[relewis]

I have made this code which collects the value of the bound column of a multiselect list box and creates a string that I want to use in the criteria field for a query:


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

' Return Control object variable pointing to list box.
Set ctlList = Forms![frmPrintExemptionSummary]![List8]
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 = ctlList.ItemData(varItem)
Else: strCollect = strCollect & " or " & ctlList.ItemData(varItem)
End If

' Debug.Print strCollect
Next varItem

PrintExemptionVendorRows = strCollect
' Debug.Print PrintExemptionVendorRows

End Function

What I end up with is a string looking like this: 11 or 14 or 16 or 88 or 92

I put the statement PrintExemptionVendorRows() in the criteria field of the query I want to run with the above statement as criteria. However, when I run the query I get no results, as if the query doesn't see the function as a query statement, but perhaps as a text string; as if "11 or 14 or 16 or 88 or 92" is within quotes, which WOULD give me no results.

Can anyone help me figure out what I'm doing wrong? Thanks

Robin

 

[Zathras]

I don't know if this is really the answer to your problem, but you should take a look at the posts in thread701-472891 -- it sounds similar.

 

[tedsmith]

You cant have the function return "10 or 14" because VB normally expects language like if A=10 or if A=14.
10 OR 14 is a calculation.

 

[scriverb]

Just change the following in your Function:
If strCollect = Empty Then
strCollect = ctlList.ItemData(varItem)
Else: strCollect = strCollect & "," & ctlList.ItemData(varItem)
End If

This will return a string looking like: "11,14,16,88,92"

Now create another column in your query with the following:

Position: Instr(1,PrintExemptionVendorRows(),FieldName)
Place in the criteria line for this new column the following(without the quotes): ">0"

Each row of your query will have either a 0 or an integer number of the location of the field value being analyzed. If the expression returns a zero(0) then the value wasn't found but if the position is any number greater than zero(0) then the value was found in the expression and the critieria expression(>0) will select it. The commas were used between the multiple values to keep the analysis from inaccurately selecting a row with a value of 22 from two list box selections when cancatenated would look like 1225( 12 and 25 ) and return a value of 2. Using the second digit and the first digit of two values which is not what you want. Any character could have been used to do this.

I hope this helps you with your problem. I have used this technique many times before.



Bob Scriver

 

[PaulBricker]

Bob, that is a piece of work. Two stars from me. Write an FAQ please.

Paul

 

[scriverb]

Thank you PaulBricker. I think I will in the near future.

I have been using this technique quite a while now and I have found many uses for it.(queries, IF statements, etc.) It works great in If statements because you have to connect them with Or's and it becomes so word and lengthy. This is really simple and direct once you understand the process. Bob Scriver

 

[PaulBricker]

Bob the forum wouldn't allow me to give you more than one star but I've already used your technique to solve a problem with a database I'm working on so as far as I'm concerned it's a 5 ***** answer.

Paul

 

[scriverb]

Thanks again Paul. I am glad that it has helped you.

I was thinking about Relewis's problem overnight and if I were to design this from scratch rather than just respond to his situation I would make a few adjustments. The way I have suggested he get his answer is a little inefficient because his Function will execute for every row in the table. Since his Function is performing a loop through the listbox selections to build the target string it really only needs to be done once and not for each row. The following would make his problem more efficient:

Create a global variable and a new function at the database module level:
Global vTargetStr as string
Public Function TargetStr() as string
TargetStr = vTargetStr
End Function

Then in the form where the listbox control is located and in the Event procedure that is triggering the printing of the report put this code:

vTargetStr = PrintExemptionVendorRows()
DoCmd.OpenReport "nameofreport", acViewNormal


I would modify the query column to the following:
Position: Instr(1,TargetStr(),FieldName)
Place in the criteria line for this new column the following(without the quotes): ">0"

This way the Function that builds the string only executes once and then the TargetStr() call within each row is just capturing and returning the global variable value for the InStr function.

This is more efficient and if the table is quite large will speed up the process.
Bob Scriver

 

[relewis]

scriverb, it looks as if both your solutions will solve my problem. I'll try them out tomorrow and reply here as to how well it works. IN the meantime, thanks so much for your (and others) help.

Robin

 

[scriverb]

Glad to be of help. If you have any problem just get back to me and I will be glad to help you get this to work. I may not be able to respond until later in the evening tomorrow as I will be out of the office most of the day. Bob Scriver

 

[smurf01]

Bob,
I have a table in Access which contains a field called "completed" the purpose of the field is to show whether the record is complete or outstanding. The values are as follows
0 = outstanding
1 = completed
I have created a parameter query which asks the user which value to use, if the user chooses "0" then the query runs and if there are no records the query is blank, if this is the case they will then need to look for records that are complete,they then have to use "requery and enter the value of "1". What i would like to achieve is that i put some code in that firstly automatically queries for "0" but if there are no records it will automatically requery using the value of "1".

To further enhance this if the query uses "1" then it only displays the last 6 records entered

Hope I have not confused you too much

Please Note : I posted this question in the Office forum and it was suggested that i might want to try either in the VBA forum or the Access forums


Regards

Paul

 

[scriverb]

smurf01, why don't you copy and paste your question in this forum as a new question and title and I will be glad to solve this for you. This is not a tough one but one that needs to be explained with a little bit of detail. If I were to answer it here buried in another question other Tek-Tips users wouldn't see it to benefit from the answer. Bob Scriver

 

[relewis]

scriverb,

I have implemented your solution using the global variable, and it works very well except for one little thing.

If the string created looks like this "47,62", I am getting results not only for the records that match 47 or 89 in criteria (which is what I want) but also records that match 4, 7, 6 and 2 (which I don't want).

I can't figure out how to stop Instr() from parsing the numbers between the commas in this way. Do you have any wisdom to impart?

Robin

 

[scriverb]

You Wrote:
If the string created looks like this "47,62", I am getting results not only for the records that match 47 or 89 in criteria (which is what I want) but also records that match 4, 7, 6 and 2 (which I don't want).

I think you meant 62 instead of 89(see red) didn't you. Just a typo. And I have had to deal with this problem. Usually when I use this function comparison I have a two digit string. 04 or 07 or 06 or 02 Since you are making string comparisons just convert your single digit integers jor string to a two digit string before you make the comparisons.

Format(vStringVariable, "00&quot
or
CStr(Format(vIntegerVariable, "00&quot)

This would take a single digit string/integer like 4 or 7 or 6 or 2 and convert to 04 and 07 and 07 and 02 and the comparison would work as expected.

I hope that this works for you. Get back if this is not sufficient for your situation. Each situation is a little different and you have tomodify it slightly. Bob Scriver

 

[scriverb]

UPDATE: Sorry, that numeric interger expression should be:

Format(CStr(vIntegerVariable), "00&quot)

Bob Scriver

 

[relewis]

scriverb,

Thanks for the clarification; however, adding the Format() or Format(Cstr()) bit to the parsing of the string I make did not solve the problem.

What did solve the problem was, the string I am creating is taken from numbers in an autonumber field in my table. Instead of having the autonumber field Increment, I changed it to Random. The randomly generated numbers are long enough that it reduces the chances of some part of a number in one record matching the whole of another number in another record.

Thanks for your help.

Rboin

 

[scriverb]

Another way to solve that problem would to put a final "," at the front and the end of your initial string of values:
",11,14,12316,882354,9223,"

Now the column your are searching should be converted and have a "," added to the end so that you are looking for let's say ",88235," which would not be found in the above example.

This should solve the problem that you described in your last posting.
Bob Scriver

 

[wvandenberg]

I realize this post is really old but I hope someone can give me a hand.

I have tried to recreate what Bob suggested after "thinking about Relewis's problem overnight" and have it half working. If I only select one item, the query runs fine. However, if I select more than one item, the query is empty.

I have the following in a module:

Global vTargetStr As String

Public Function TargetStr() As String
TargetStr = vTargetStr
End Function

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

' Return Control object variable pointing to list box.
Set ctlList = Forms![frmSiteSummary]![lstProjects]
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 = ctlList.Column(1, varItem)
Else: strCollect = strCollect & " Or " & ctlList.Column(1, varItem)
End If

' Debug.Print strCollect
Next varItem

GetlstProjectsRows = strCollect
Debug.Print GetlstProjectsRows

End Function

And I have this behind the button on my form:

Private Sub cmdProjectData_Click()

vTargetStr = GetlstProjectsRows()
DoCmd.OpenQuery "test"

End Sub

When the function equals a string like this: "200-1103-4", the query displays correctly.

When the function equals a string like this: "200-1103-4 Or 200-0902-4", the query does not display any data. But, if I hard code "200-1103-4 Or 200-0902-4" into the query, it works! I just can't figure this out!

If someone could help, I'd be really grateful.

Wendy

 

[scriverb]

Show me the SQL from your query and how are you using the function and selecting the records. Are you trying to use the Instr function to look for that particular value in the string?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[wvandenberg]

The SQL for the query is:

TRANSFORM Max(LAB_DATA.RESULT) AS MaxOfRESULT
SELECT tblProjects.Project, LAB_DATA.LOGINNUM, LAB_DATA.CATEGORY, LAB_DATA.CLIENTID, Format([SMPDATE],"dd-mmm-yy") AS [DATE]
FROM tblSites INNER JOIN (LAB_DATA INNER JOIN tblProjects ON LAB_DATA.SITE = tblProjects.Project) ON tblSites.ID = tblProjects.SiteID
WHERE (((tblProjects.Project)=GetlstProjectsRows()) AND ((InStr(1,TargetStr(),[tblProjects].[Project]))>0))
GROUP BY LAB_DATA.SITE, tblProjects.Project, LAB_DATA.LOGINNUM, LAB_DATA.CATEGORY, LAB_DATA.CLIENTID, LAB_DATA.SMPDATE, Format([SMPDATE],"dd-mmm-yy"), tblProjects.Project
ORDER BY LAB_DATA.LOGINNUM, LAB_DATA.CATEGORY, LAB_DATA.SMPDATE, LAB_DATA.SMPDATE
PIVOT LAB_DATA.ANALYTE;

Yes, I'm using the Instr() in the query to look for a value in the resulting string so the function isn't evaluated for each record.

Wendy