Using User Defined Function output from VBA as an input in access query

Question

I have created a function that provide the value based on user selection.I want to use the output of the fuinction in another query.

My function is :-

Public Function provisionvariable() As String


For Each sItem In Forms![Access Form].Provision.ItemsSelected

v_provision = "" & Forms![Access Form].Provision.Column(0, sItem) & ""



Provision = Provision & "" + v_provision + ","


'MsgBox Provision

Next

Provision = Left(Provision, Len(Provision) - 1)
provisionvariable = Provision
MsgBox provisionvariable
End Function

The output of the function is BBNI,FP

I want to use the output as where condition in access query

My query is

 ***SELECT DISTINCT quarter, provision, [currencycode]+'-not found in Master' AS Comment
       FROM t_00_unearned_unincepted_alloc_basis AS inp
       WHERE     **provision in (provisionvariable()) AND**
        NOT EXISTS 
        (SELECT 1 
        FROM     t_01_le_currency_master key1 
        WHERE   inp.[group_stat]=key1.[group_stat] AND 
                inp.le=key1.le AND 
                inp.[currencycode]=key1.[original_currency]);***

Now the problem is output of function is BBNI,FP but access takes it as single string i.e. 'BBNI,FP' in the query.

Is it possible to have it as two string ('bbni','FP') rather than 'BBNI,FP'

Any Suggestions Much Appreciated

Thanks

score 0 · Answer 1 · answered Oct 07 '14 at 07:05

0

Try:

Provision = Provision & "'" & v_provision & "',"

And to ensure you aren't repeatedly appending to a module-level variable, you should also do a local

Dim Provision As String

answered Oct 07 '14 at 07:05

Tom Robinson

1,850
1
15
14

I am doing that in my function. For example if output of function is BBNI,FP. When i use the function provisionvariable() in my access query it say 'BBNI,FP' as single string and query doesnot show any output. – Mohit Oct 07 '14 at 10:43
What i want is query should take it as two seperated string seperated by CSV – Mohit Oct 07 '14 at 10:51
What @TomRobinson suggested is correct. Your query will only work if the clause looks like "WHERE (((Provision) In ('BBNI','FP','etc','etc')))..." You need the commas and you need to delimit each string with a single quote (or double-quote, which gets messy). – Wayne G. Dunn Oct 07 '14 at 16:49
I tried @TomRobinson approach, it is taking it as 'BBNI','fP' as a single string. it is not yielding any result – Mohit Oct 08 '14 at 03:29
Based on the debugging I have seen that access is automatically adding Single quotes '' in the query. – Mohit Oct 08 '14 at 03:32

score 0 · Answer 2 · edited May 23 '17 at 11:49

Based on the following post, "a function cannot be used for IN () clauses" How to call VBA-function from inside sql-query?

So you need to build the SQL (which would include the 'In' list) then execute it. Since I have no idea if multi-user environment, or how you will use the query output, I would build the SQL, save as a QueryDef object, then do whatever you want.

The following is what the code to build the SQL could look like:

Dim strSQL  As String
strSQL = "SELECT DISTINCT quarter, provision, [currencycode]+'-not found in Master' AS Comment " & _
   "FROM t_00_unearned_unincepted_alloc_basis AS inp " & _
   "WHERE  provision  In (" & ProvisionVariable() & ") AND " & _
   . . . .

Debug.Print strSQL

The following is my version of your Function (note the single-quote delimiters are added):

Public Function ProvisionVariable() As String
Dim sItem           As Variant
Dim v_provision     As String
Dim provision       As String

    provision = ""
    For Each sItem In Forms![Access Form].Provision.ItemsSelected
        v_provision = "'" & Forms![Access Form].Provision.Column(0, sItem) & "'"
        provision = provision & "" + v_provision + ","
    Next sItem

    provision = left(provision, Len(provision) - 1)
    ProvisionVariable = provision
    Debug.Print ProvisionVariable
End Function

I read the post where it see that a function cannot be used for IN () clauses. I ran my query with a function with in clause and there was no error. I think we can run the query with in clause. — Mohit, Oct 09 '14 at 04:11
I want query outside the code because these queries are validation on the data table . so need to check the output explicitly without looking into code — Mohit, Oct 09 '14 at 04:24
First, just because something doesn't produce an error doesn't mean that it works. If you are going to insist that the query remain with the call of the function EMBEDDED, then I can't help you. If you want to produce the desired results and are willing to alter your code ONE TIME, then there is a solution. You don't mention how/where your query is used, but I'm guessing it is either the RecordSource for another form/control, or you execute it via VBA? Once your code is changed, you would not need to change it again. — Wayne G. Dunn, Oct 09 '14 at 11:37

Using User Defined Function output from VBA as an input in access query

2 Answers2