Results 1 to 7 of 7
  1. #1
    orcinus is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    67

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

    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

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    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?

  3. #3
    orcinus is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    67
    Quote Originally Posted by NTC View Post
    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')

  4. #4
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    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.

  5. #5
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    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.

  6. #6
    orcinus is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    67
    Quote Originally Posted by NTC View Post
    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!!

  7. #7
    orcinus is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    67
    Quote Originally Posted by weekend00 View Post
    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..

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query to work as a Vlookup function
    By dharmik in forum Queries
    Replies: 21
    Last Post: 01-04-2012, 08:12 AM
  2. Replies: 2
    Last Post: 12-22-2010, 09:57 AM
  3. Can't get NZ Function to Work
    By Xiaoding in forum Queries
    Replies: 6
    Last Post: 04-14-2010, 09:54 AM
  4. trouble creating correct criteria
    By pshawk in forum Queries
    Replies: 1
    Last Post: 02-12-2010, 01:52 AM
  5. Criteria from Combobox dont work.
    By Ryan in forum Queries
    Replies: 3
    Last Post: 08-31-2009, 07:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums