How to send a query string from function

Question

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!

Daniel · Accepted Answer · 2012-12-18 18:11:48Z

3

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.

answered Dec 18, 2012 at 18:11

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fionnuala Over a year ago

Just be aware that it will not be possible to distribute a compiled front-end using this approach. If the time intervals are constant, they can simply be added to the query, if there are variable, they should not be hard-coded.

Daniel Over a year ago

That's true for the first example, which is why I recommend alternatives. However, if you OP uses my latter suggestion (making a new table in the back-end) there is no impedement to distributing the front-end.

Fionnuala · Accepted Answer · 2012-12-18 18:02:20Z

0

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.

answered Dec 18, 2012 at 18:02

Fionnuala

91.5k7 gold badges121 silver badges155 bronze badges

2 Comments

KSM Over a year ago

This comes down to the same thing as me typing in the whole string, but thank you for the help

Fionnuala Over a year ago

No it does not. You can set the value once. You can even store the value by setting the control source property. It is not generally considered good practise to create a function that has to be edited: "therefore each time i require to modify this string i can do it through that one instance"

Collectives™ on Stack Overflow

How to send a query string from function

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related