passing values into query from function - doesn't work!

[james]

okay, so why won't this work?
i put the message box in to tell me what the code was writing, and it
is EXACTLY the same as if i hard coded it into the query criteria.
so , query criteria typed straight into query works but doesn't if
returned by the following function... by the way Chr(34) is ".
thanks in advance!


Public Function changeBatch() As Variant

Dim changeCriteria As String
Dim lowCriteria As Integer
Dim highCriteria As Integer

lowCriteria = InputBox("Enter the lower batch number:", "Accuracy
Report Creator")
highCriteria = InputBox("Enter the higher batch number:", "Accuracy
Report Creator")

changeCriteria = ">=" & Chr(34) & lowCriteria & Chr(34) & " And <=" &
Chr(34) & highCriteria & Chr(34)

changeBatch = changeCriteria

MsgBox (changeBatch)

End Function

 

[John Vinson]

okay, so why won't this work?

Because you can pass VALUES in the result of a function - but you
cannot pass OPERATORS such as >=.

You'll need to construct the entire SQL string.

John W. Vinson[MVP]

 

[pietlinden]

hang on... are you filtering a report? If so, remove the criteria from
the query and pass them in the Open event of the report. Then you can
build your filter in code and pass it in the Open event...

Dim stDocName As String

stDocName = "rptEmployeeInfo"
DoCmd.OpenReport stDocName, acPreview, , "[PositionTitle] ='" &
Me.PositionTitle & "'"

 

[james]

hang on... are you filtering a report? If so, remove the criteria from
the query and pass them in the Open event of the report. Then you can
build your filter in code and pass it in the Open event...

Dim stDocName As String

stDocName = "rptEmployeeInfo"
DoCmd.OpenReport stDocName, acPreview, , "[PositionTitle] ='" &
Me.PositionTitle & "'"

Unfortunately no, i have about 5 queries which the report shows
averages from. Is there any way i can extract the SQL from my existing
queries or do i have to write it in full...?

 

[james]

and what about passing operators like AND, OR, BETWEEN...? can you
really not pass any of those?

say i wanted to create the query to find "38", "39", or "40", could i
do that?

 

[james]

sorry for so many posts...

what about passing values in from a text box on a form? can i pass
operators then?

 

[John Vinson]

can i pass operators then?

No, to all of the above.

:-{(


John W. Vinson[MVP]

 

[james]

lol... that'd be right.

well i worked around it by creating a seperate form/table for the upper
and lower criteria, then referenced to that form in the query criteria:

Between ([Forms]![frmSettings]![txtCriteriaHigh]) And
([Forms]![frmSettings]![txtCriteriaLow])

turns out that, for what i need, this is quite an elegant solution.
thanks so much for youe help!
james.