Dynamic Criteria in Query

[ions]

Hello Access Expert

Is it possible to call a function as the criteria for a field in a query.

I have used a function that returns a boolean into the criteria field and it worked but when I create a string expression for the criteria field it doesn't seem to work.

For example I have created this simple function to generate a criteria

Public Function AcceptedDays() As Variant

    Dim Days As String
    
    Days = ""
    If Forms![DrillDown]![Sunday] Then
        Days = "1"
    End If
   
    If Forms![DrillDown]![Monday] Then
        If Days = "" Then
            Days = "2"
        Else
            Days = Days + " OR 2"
        End If
        
    End If
    
    If Forms![DrillDown]![Tuesday] Then
        If Days = "" Then
            Days = "3"
        Else
            Days = Days + " OR 3"
        End If
    End If
    
    If Forms![DrillDown]![Wednesday] Then
        If Days = "" Then
            Days = "4"
        Else
            Days = Days + " OR 4"
        End If
    End If
    
    If Forms![DrillDown]![Thursday] Then
        If Days = "" Then
            Days = "5"
        Else
            Days = Days + " OR 5"
        End If
    End If
    
    If Forms![DrillDown]![Friday] Then
       If Days = "" Then
            Days = "6"
        Else
            Days = Days + " OR 6"
        End If
    End If
    
    If Forms![DrillDown]![Saturday] Then
        If Days = "" Then
            Days = "7"
        Else
            Days = Days + " OR 7"
        End If
    End If
    
    AcceptedDays = Days

End Function

If however, I literarly type what the function produces into the query grid field the query generates the correct result. On the other hand if I send the result of the above function the query doesn't work. Any explanations ?

Thanks so much.

 

[Ziggy1]

can you post a sample of how you have it setup? Did you Debug your function by putting a "Block" in the code so you can see the results step by step?

If you can explain how the function works with the form that would help (how/what is entered).

 

[Jon K]

I have used a function that returns a boolean into the criteria field and it worked but when I create a string expression for the criteria field it doesn't seem to work.
. . . . . . . . . . . . . . . . .

If however, I literarly type what the function produces into the query grid field the query generates the correct result. On the other hand if I send the result of the above function the query doesn't work. Any explanations ?

Access treats what is returned by the function as one whole value. Hence it will work when the function returns one number e.g. 2, but when more than one numbers are returned e.g. 2 or 3 or 5, it will not work.


There is a workaround. You can use a built-in function in the Where Clause of the query SQL statement and make your user-defined function return a string that can be used inside the built-in function.

See the attached database, in which I used the InStr() function in the Where Clause of the SQL statement and made the AcceptedDays() function return a string such as 2,3,5.
.

 

[ions]

Jon K....

Excellent Idea Joh K.

Thanks a bunch ..... amazing.... exactly what I was looking for.

Now i don't have to contruct these huge SQL Statments. Much prefer working with the query grid.

AGain thanks so much....

Hopefully one day I will be able to contribute to this forumn and pay the community back.