Function Returns Correct, but won't work in Query Criteria

[orcinus]

The below code does not seem to want to work in a query when the function name is set as the criteria. The criteria (04) is in the underlying table and so should populate. When I run the query, however, I do not get an error, the query runs, opens but no data is returned.

The function in the query in the criteria field is typed thus: fcnFldValRR2()

when I type in the criteria field in the query: In ('02','04')
I get records returned. This worked before. For some reason, it is not working now..?

Note, when I run the code in the code window, I get the following results:
fcnFldValRR2 = "in ('02','04')"

Any help is appreciated..


Public Function fcnFldValRR2() As String
'this code selects the value of a grouped query which has the rerate month max value in it; it is
'used for criteria for output data..
Dim d As Database
Dim r As Recordset
Dim strSQL As String
Dim i As Integer
Dim intRecCnt As Integer
Dim strFldVal(12) As String '1st rerate month pulled
Dim strFLDValAll As String 'all fldval strings combined


Set d = DBEngine(0)(0)
strSQL = "SELECT tbl_Mbr_ANOC_Data.RERATE_MTH_NUM FROM tbl_Mbr_ANOC_Data GROUP BY tbl_Mbr_ANOC_Data.RERATE_MTH_NUM;"
Set r = d.OpenRecordset(strSQL)
intRecCnt = DCount("[RERATE_MTH_NUM]", "qry_Rerate_Mth_Num_Daily")
r.MoveFirst
For i = 1 To intRecCnt

strFldVal(i) = Format(Nz(r.Fields(0)), "00")

If i < intRecCnt Then
strFLDValAll = strFLDValAll & "'" & strFldVal(i) & "',"

ElseIf i = intRecCnt Then
strFLDValAll = strFLDValAll & "'" & strFldVal(i) & "'"
End If

r.MoveNext
Next
fcnFldValRR2 = "In (" & strFLDValAll & ")"
End Function

[NTC]

if the function returns correct (I take your word on that point) - do a sanity check and put the value into an unbound control in an open form.

then modify your query so the criteria calls the value at the form's control.....does it work?

[orcinus]

if the function returns correct (I take your word on that point) - do a sanity check and put the value into an unbound control in an open form.

then modify your query so the criteria calls the value at the form's control.....does it work?

OK, I created form named form1 and an unbound control named Text0 and set its control source equal to: =fcnFldValRR2()

When opening the form I get the following value in the text0 control:
In ('02','04')

When leaving the form open and placing: [forms]![form1]![text0] in the criteria field (where the function formerly was) and hitting enter, again, the query opens, but I get no results.

I then did a second experiment. I went to the open form and copied the contents of text0* directly into the query criteria field. I then got data!

Another test I did was to place fcnFldValRR2() as a query field as:

check: fcnFldValRR2()

and when running the query, with in ('02','04') manually typed into the criteria field, I got the records and in the field check I got In ('02','04') in all the rows I got returned. So, it appears to work there as well!

I suppose I am missing some qualification, but don't know what it is..

*In ('02','04')

[NTC]

these 2 tests seem contradictory:

*When leaving the form open and placing: [forms]![form1]![text0] in the criteria field (where the function formerly was) and hitting enter, again, the query opens, but I get no results.
*copied the contents of text0* directly into the query criteria field. I then got data!

while these 2 tests are consistent:

* and when running the query, with in ('02','04') manually typed into the criteria field, I got the records
* copied the contents of text0* directly into the query criteria field. I then got data!


what's clear is you can't call the function as a criteria. why? not sure - never had that requirement before. as a work-around & another test you might change your form1.text0 control . right now you have it sourced on the function and it doesn't work. change that to be unbound. In the form's CurrentEvent - have the function value put into the unbound control. the query will then probably work based on your experiments is my guess.

now at least you know the function works, the query works, and it is distilled down to whether or not a query can call a function value....seems like we have the answer as 'no'. but that it is able to call it as a field appears contradictory. possibly putting that query into sql view will display a syntax error and could be manually tweaked - perhaps quotation marks...typing outloud. an interesting case.

[weekend00]

when you put "fcnFldValRR2()" in criteria and fcnFldValRR2()="in ('02','04')"

the where condition will be where [yourfield]="in ('02','04')" and of course it will not return any record.
what you need is where [yourfield] in ('02','04'), but I don't think we can make it this way. an alternate way is: where instr(fcnFldValRR2(), [your field])>0. to do this in query design view, put instr(fcnFldValRR2(), [your field]) as a field and put >0 as criteria.

[orcinus]

these 2 tests seem contradictory:

*When leaving the form open and placing: [forms]![form1]![text0] in the criteria field (where the function formerly was) and hitting enter, again, the query opens, but I get no results.
*copied the contents of text0* directly into the query criteria field. I then got data!

while these 2 tests are consistent:

* and when running the query, with in ('02','04') manually typed into the criteria field, I got the records
* copied the contents of text0* directly into the query criteria field. I then got data!


what's clear is you can't call the function as a criteria. why? not sure - never had that requirement before. as a work-around & another test you might change your form1.text0 control . right now you have it sourced on the function and it doesn't work. change that to be unbound. In the form's CurrentEvent - have the function value put into the unbound control. the query will then probably work based on your experiments is my guess.

now at least you know the function works, the query works, and it is distilled down to whether or not a query can call a function value....seems like we have the answer as 'no'. but that it is able to call it as a field appears contradictory. possibly putting that query into sql view will display a syntax error and could be manually tweaked - perhaps quotation marks...typing outloud. an interesting case.

I think I didn't make myself too clear.

What I meant by when I said that I copied the contents of text0 into the query crtieria field was that it was a second test. In other words, after testing the form referenced field in the criteria field, I deleted that and then selected the value from the text0 field in the opened form, copied that value and placed it in the criteria field - Then I got the correctly returned records.

This was only an effort to show that the values weren't the problem (i.e. that the function perhaps worked, but maybe the values just weren't in the underlying data table!).

Also, a function does work in this way. In fact, I have two other functions that are included in the criteria in the same query and they work!!

[orcinus]

when you put "fcnFldValRR2()" in criteria and fcnFldValRR2()="in ('02','04')"

the where condition will be where [yourfield]="in ('02','04')" and of course it will not return any record.
what you need is where [yourfield] in ('02','04'), but I don't think we can make it this way. an alternate way is: where instr(fcnFldValRR2(), [your field])>0. to do this in query design view, put instr(fcnFldValRR2(), [your field]) as a field and put >0 as criteria.

This works! Thanks!

I think you were correct that it was including the quotes. I didn't go down that path because I thought this worked before..Not sure where the disconnect was..anyway, hadn't thought of the method you suggested..

Much appreciated..

And thanks to NTC as well for your input. In implementing the solution here, it appears my other functions in the query criteria work becuase they are single criteria fields. They don't require the "In" and so no quotes for that piece "get in the way" in the query criteria grid..