Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Caplande is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2020
    Posts
    12

    Customized function in query criteria

    My question: Is it possible to insert a customized function in a query criteria. All my tries seem to say it does'nt work. Am I wrong?
    Thanks for your answer.

    Georges

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tell us about the function.What is its purpose? Show us the code.

  3. #3
    Caplande is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2020
    Posts
    12
    Function is this one:

    Code:
    Public Function CRitereTest()
    CRitereTest = "Like '*'"
    End Function

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I cannot recall if a function with no declared data type returns a string or a variant. If it's a variant, the query might not be handling it properly - assuming you can do this at all (I think so, but can't recall for sure).

    I'd try Public Function (CRitereTEst) As String
    and if that didn't work, add
    CRiterTest = """Like '*'""
    If neither of those work, post the query sql or a compacted and zipped db copy.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Caplande is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2020
    Posts
    12

    Customized function in request criteria

    None of the changes you TestDeCritere.zipare proposing to me work. I therefore send you a very simple database which illustrates my question

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    MOnCritere = "Like """ * """"
    Not sure why you would even bother with this. You might as well specify no criteria at all - you will get the same result: all records.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Caplande is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2020
    Posts
    12
    The solution MOnCritere = "Like """ * """" gives 0 result

    Of course, not putting a condition would provide the result I expect.
    However, the criterion that I actually use is more complex. It comes in the form:

    MOnCritere = iif (myCondition, criteriaYes, "Like '*'")

    Did I explain myself well?

  8. #8
    Caplande is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2020
    Posts
    12
    The solution MOnCritere = "Like """ * """" gives 0 result

    Of course, not putting a condition would provide the result I expect.
    However, the criterion that I actually use is more complex. It comes in the form:

    MOnCritere = iif (myCondition, criteriaYes, "Like '*'")

    Did I explain myself well?

    It seems that the criteria of type: Like, Between etc... are problematic (I tried them).
    However, the criterion:
    MOnCritere = "Jardinage"
    works.
    My question becomes: Is there a solution for Like, Between etc... ?

  9. #9
    Caplande is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2020
    Posts
    12
    It seems that the criteria of type: Like, Between etc... are problematic (I tried them).
    However, the criterion:
    MOnCritere = "Jardinage"
    works.
    My question becomes: Is there a solution for Like, Between etc... ?

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Funny, it worked yesterday. Today it raises a type mismatch error. It would help if you said what you are doing in the beginning. Posting that you are doing one thing then saying 'but really I am doing this' doesn't help us. My suggestion now would be to drop the false part of the IIF:
    iif (myCondition, criteriaYes)

    Worked for me but I had to use an actual field value for the True test because there is no "myCondition" in the db.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My question becomes: Is there a solution for Like, Between etc... ?
    I think not for the method you are trying if you consider how a query works. Whatever you put in the design grid becomes the criteria for looking up values in that field. When you enter the call to a function there, it can return a value in a field (e.g. 4 as the id number) and you will get records returned that match 4. However, your function idea does not return a value, it returns an operator. That operator becomes the value to be looked up in the field. Thus the query is looking for the value of "LIKE '*'" in a field, and of course, there is no such value. AFAIK you could only do this particular task by modifying the sql in vba so that it reflects what you want that field's criteria to be.
    Last edited by Micron; 05-09-2020 at 09:34 AM. Reason: correction

  12. #12
    Caplande is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2020
    Posts
    12
    Quote Originally Posted by Micron View Post
    Funny, it worked yesterday. Today it raises a type mismatch error. It would help if you said what you are doing in the beginning. Posting that you are doing one thing then saying 'but really I am doing this' doesn't help us. My suggestion now would be to drop the false part of the IIF:
    iif (myCondition, criteriaYes)

    Worked for me but I had to use an actual field value for the True test because there is no "myCondition" in the db.
    I completed my example, as you ask me. In addition, I created a second scenario where I directly insert the "iif (...)" in the query criteria area (query "R_Formula_directly_in_query").
    The initial request is called "R_Function_in query_criterion".
    When the control of the form "Formulaire1" takes the value "Vrai" neither of the two scenarios works. When it takes the value "Faux" everything is OK.

    TestDeCritere.zip

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    #1 - If you are just using the scroll bar of your listbox to show an item, it will never work. Showing the item doesn't select it, you have to click on it, otherwise Null is passed to the function. You would use a combo box for this, not a listbox

    #2 - I think you did not understand what I said about returning an operator (or expression) to the query.
    MOnCritere = IIf(Forms("Formulaire1")![li_interrupteur] = "Vrai", "Like '*'", "Jardinage")
    This will return "Like '*'" as criteria not Like "*" using the Like operator. In other words, the query will search the field for values equal to Like '*' as in

    Table with Like '*' in field
    id Raison_sociale Famille
    20 ENTREPRISE T Bātiment
    21
    Like '*'

    query result using your expression:
    Famille
    Like '*'

    Again, you have not shown why you need to use the Like operator to return everything when you don't need it at all.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    Caplande is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2020
    Posts
    12
    Ok Micron, I understood that it is not possible to insert in a query criteria an expression (or a function) which returns an expression like "Like", "Between" etc ...
    I tried, by this means, to avoid creating 2 separate queries (one for Ld_Interrupteur = "Vrai", one for Ld_Interrupteur = "Faux"). This experience shows it's not possible. I will therefore create 2 requests for this process.

    I just sent you a very simple example which translates exactly the context of my problem, because the project I am working on contains confidential data that I should have deleted / modified ... too much work!
    Many thanks for your very precious help...and your patience

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I just sent you a very simple example
    I don't know what that means.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Proper Syntax for IIf function in Query Criteria
    By Ecologist_Guy in forum Queries
    Replies: 4
    Last Post: 03-13-2017, 11:19 AM
  2. VBA function result as query criteria
    By lefty2cox in forum Programming
    Replies: 13
    Last Post: 12-22-2015, 10:41 AM
  3. Replies: 15
    Last Post: 07-09-2015, 01:39 AM
  4. Query Criteria Function and Operator List?
    By samanthaM in forum Access
    Replies: 2
    Last Post: 06-03-2012, 04:52 PM
  5. Replies: 2
    Last Post: 12-22-2010, 09:57 AM

Tags for this Thread

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