AGAIN: User defined function in statement (Access97) (1 Viewer)

[RV]

Hello,

I posted this question earlier in the Queries Forum.
Because it's more a VBA subject and I still haven't got any answers, once again:


I've created a user defined function to get all the values which are selected from a
MultiSelect listbox.
The function returns a string containing all values.
The function itself works OK.

I try to refer to the function in an SQL statement in a WHERE clause:

SELECT somecolumns
FROM sometables
WHERE table1.ActivityNumber IN (ListBoxActivities());

being ListBoxActivities the function I would like to refer to.

After one or more items are selected in the listbox, the query is run by clicking on a button.
As result no rows are selected.

I guess the reason why no records are selected is in the function itself, returning a string as result.
I can't figure out how to solve this.
Here's the code used to create define the
function:

Option Compare Database
Option Explicit
Public strValuelist As String

Public Function ListBoxActivities() As String

Dim dbs As Database
Dim varItem As Variant
Dim lst As Listbox

Set lst= Forms![AutomaticRemittance]!Activities
Set dbs = CurrentDb

If lst.ItemsSelected.Count > 0 Then
With lst
For Each varItem In lst.ItemsSelected
strValuelist = strValuelist & "," & lst.ItemData(varItem)
Next varItem
End With
strValuelist = Mid(strValuelist, 2)
End If
Set dbs = Nothing

What's going wrong here?

RV

 

[Jack Cowley]

I think you will find your answer in the article below...

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q100131

 

[RV]

Jack,

this article doesn't deal with the issue I posted.
I'll rephrase my question:

Can I refer to a user defined function in the WHERE clause of a query using the IN operator)
Access Help tells me yes, practice tells me no or I'm missing a point...

Any help would be very much appreciated (I really need a straight answer).

RV

 

[RichMorrison]

<<
I try to refer to the function in an SQL statement in a WHERE clause:

SELECT somecolumns
FROM sometables
WHERE table1.ActivityNumber IN (ListBoxActivities());
>>

If you try to refer to your function like this it won't work. "ListBoxActivities" would be interpreted as a literal.

You want something like this:

Dim strSQL As String

strSQL = "SELECT somecolumns
FROM sometables
WHERE table1.ActivityNumber IN ("
& ListBoxActivities() & ");"

Then use strSQL where needed.

RichM

 

[RV]

Rich,

I understood that it's possible to use a user defined function directly in a SQL statement which is saved in a query.
Am I wrong or doesn't it work in "my way"?

So the only option I have is to hardcode the SQL statement I need in a VBA procedure, right?

I really cant get the logic here which comes with Access, it seems to be a very unlogical tool in some futures......

Greetings,

RV

 

[Rich]

Did you download the queries sample db to see how they use a user defined function?

 

[RichMorrison]

you wrote:
<<
I understood that it's possible to use a user defined function directly in a SQL statement which is saved in a query.
Am I wrong or doesn't it work in "my way"?
>>

You are correct. I misunderstood the example. If you enter the name of a function in the Criteria box of a query that should work.

But....
looking at your code, you should be assigning strValueList to the function ListBoxActivities before the end of the function. I don't see that statement in the example.

I also don't understand the purpose of the statement
<<
strValuelist = Mid(strValuelist, 2)
>>
What's that for ?


HTH,
RichM

 

[RV]

Rich,

yes, I have downloaded the queries sample db.
And yes, I've looked at the function used to get ages.
I dare say I even can understand the code structure.
This sample doesn't help me out.

RV

 

[Pat Hartman]

Have you looked at the string produced by the function? Are you sure that it is correct? Take the string and paste it directly into the query. Does the query work?

Is ActivityNumber a text field? If it is, each element needs to be surrounded by double or single quotes.

 

[RV]

RichMorrison,

>I also don't understand the purpose of the statement strValuelist = Mid(strValuelist, 2)<

It's supposed to strip out the leading comma.

>Looking at your code, you should be assigning strValueList to the function ListBoxActivities<

I've tried that before.
When I then run query I get an error message like:

"The number of arguments you used is not correct".

The query is activated by clicking on a button after selecting the appropiate values from the listbox.

RV

 

[RV]

Pat,

I see you're back from your little trip.
Hope the weather was better than over here in the Netherlands.
I'm not sure the string itself is correct.
That's what I'm trying to figure out right now by testing it.
I've tried to run the query after pasting the string in it. No success, see my last post.

I've implemented some message boxes in the definition of the function.
The results from the message boxes are working OK ON THE OUTSIDE.
I guess the problem is in defining the public variable strValuelist as being a string and in defining the function itself as a string.

The datatype of the ActivityNumber is Integer.

Greetings from a cloudy Holland (litterally and figuratively spoken....).

RV

 

[SimonC]

The problem you have is that "YES" a function (user-defined or otherwise) can be used as a single criteria item (as in WHERE Field1 = Function()) what you can't do with a function is make-up parts of the query string in a compiled query as you go along.

In other words, where you've got something like WHERE Field1 IN (Function()) it will happily replace the single criteria item you've defined with its value but it can't suddenly invent new ones any more than you could have a function that returned field names and expect "SELECT Function() FROM Table1" to return a list of values in the field name returned by the function.

A query isn't a string, you can't just insert a function to add bits in as and where you want to; it's a compiled object.

However you can create a query from a SQL string as RichM shows above and I think this may be your best way of accomplishing what you want.

 

[RV]

SimonC,

thanks for your reply.
>A query isn't a string<

OK, so that's the reason why my construction won't work.
I'll settle for the hardcoded SQL string as suggested by RichM.

Greetings and thanks to you all, you're the best!

RV

 

[RichMorrison]

A little clarification, I hope.

For complex conditions, I will use a string variable that contains SQL statements as the "RecordSource" of a form or report. The SQL string can be built in code attached to a form or report event.

At the end of the code, the statement "Me.RecordSource = strSQL" will cause a requery of a form.

RichM