Function result used in a Query (1 Viewer)

I have searched through and looked at several excellent threads regarding the use of Variables from VBA as queries. Using this knowledge I have created a function that returns a string and I have put that function into a Query and it works for simple results such as when a single character string such as "2" or "6" is returned.

However, sometimes the results of my code return multiple values such as 1,2 and 6 all of which I want to use as the Query criterea. If I enter this by hand in the criterea field using the format "1" or "2" or "6" it returns the result I want. However, when I set the function equal to "1" or "2" or "6" it doesn't work. I have also tried setting the function to be "'1' or '2' or '3'" and it still doesn't work. I even tried using the Include (1,2,6) format and that didn't work either.

Does anyone have any suggestions?

Could you post your function here so we can have alook at it please?

Where Yourfield = "A" Or YourField = "B" is a compound condition. You can't use the form - Where YourField = Somevalue to get that effect. Your SQL is Dynamic and dynamic SQL can only be created with code. Use VBA to build your query and then execute it or pass the SQL string to a form or report so they can change their recordsource.

Tony,

Thanks for the response.

I don't actually have the code written yet that gives me the multiple conditions for the query. I wanted to make sure that I could make the query work first before I wrote the actual code. What I have been doing to test is to create a string value in a subroutine and then have the make the Global Function equal to the string. Here is my test code:

Private Sub txtSubID_AfterUpdate()
If Not txtSubID.Value Then

'gstrSubID = "1"
gstrSubID = "'1' Or '2'"

Else
gstrSubID = "*"

End If
End Sub

The user enters a value into a text box and this sub looks at the value and decides if it null or if there is a value. If it is null it passes a "*" to the Global Function and if it is not null I just put in a test value for now. The Global Function looks like this:

Public Function GetSubID() As String
GetSubID = gstrSubID
End Function

When I set the gstrSubID to a single character like "1" or the "*" it works but when I put in an expression with an Or or an Include it doesn't work.

Pat,

Thanks for your post.

Being a relative neophyte at programming in Access I'm not sure I understand your response. I don't understand what you mean by it being Dynamic. When I pass the string to the Global Function it is fixed. If you mean that just using an Or is defined as being Dynamic then I guess I can't do that.

I will have to learn how to create the Query in code and then execute it as you suggest.

Take a look at this post. It might help you. Or, you can search for others.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47909&highlight=SQL+and+VBA

Pat,

I have browsed through the link that you suggested and I am somewhat lost. Obviously I have a lot to learn.

I am wondering how to execute the query in code and have it populate my report properly. I have a form with a command button which calls the report and the report in turn calls the query. Since I want to run the query from within the VBA code how do I stop the query from running automatically when the report is called? Once I do this "disconnect" how do I get the results of the query to populate the report?

In terms of getting the query string written, I am doing my queries in the DataSheet view. Can I just switch to SQL view and Copy the entire query into my VBA application, make it equal to a string variable, and then execute it from within the app?

Dave

There is no reason to run the query in the form or to use a disconnected recordset.

What you do is save the SQL string in a global variable or in a hidden field on your form. Then in the Open event of the report, add a single line of code to change the report's recordsource. This change isn't permanent, it is simply for this execution.

Me.Recordsource = Forms!YourForm!HiddenControl

or

Me.Recordsource = gSQLString

Pat,

I will try your suggestions.

Thanks very much for all your help and patience.

Dave