CODEWITHSUNDEEP
vbams-accessms-access-2007
KSM
KSM

Reputation: 262

How to send a query string from function

I'm having a hard time coming up with a good title, but i hope i can explain the situation better. I currently have a query with a criteria which is ("11:00 - 21:00" Or "11:01 - 21:00"), this works perfectly fine when executed, however i will need this criteria in multiple queries therefore i decided to come up with function like below 

Function timeIntervals()

    timeIntervals = "11:00 - 21:00" & " Or " & "11:01 - 21:00"

End Function

and call it in each query, therefore each time i require to modify this string i can do it through that one instance, however when running this string above it does not function, im assuming its caused by the quotes on the Or, ive tried triple quotes """ and chr(34), however it doesn't work, can someone suggest a work around thank you!

Upvotes: 0

Views: 175

Answers (2)

Daniel
Daniel

Reputation: 13122

As Remou indicated, you won't be able to get this to work. If you really want to do the check via a VBA function, you could write something like this:

Function timeIntervals(Value) As Boolean
    If Value = "11:00 - 21:00" Or Value = "11:01 - 21:00" Then
        timeIntervals = True
    End If
End Function

Pass the value you want to check, and if the resulting function is true you then display the row. Something like: where timeIntervals(myvalue) = true.

Probably the best solution though is to make a table for the timeIntervals. Then in your query simply write something like:

Where MyValue IN(Select timeValue from timeIntervals)

Using this latter method you can update the table which will update the results for all users, and doesn't require a re-release of your front-end.

Upvotes: 3

Fionnuala
Fionnuala

Reputation: 91356

No matter what you do with quotes, that is not going to work, because Or will be returned as a string, not the boolean Or that you want. You could use a hidden form with two textboxes, then you can say:

WHERE timeIntervals = Forms!MyForm!Text1 Or Forms!MyForm!Text2

as long as the form remains open, your queries and any forms or reports based on them will work, furthermore, it will be very easy to change the intervals without modifying code.

Upvotes: 0

Related Questions