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
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
Tell us about the function.What is its purpose? Show us the code.
Function is this one:
Code:Public Function CRitereTest() CRitereTest = "Like '*'" End Function
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.
None of the changes you TestDeCritere.zipare proposing to me work. I therefore send you a very simple database which illustrates my question
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.
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?
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... ?
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... ?
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 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.My question becomes: Is there a solution for Like, Between etc... ?
Last edited by Micron; 05-09-2020 at 09:34 AM. Reason: correction
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").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.
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
#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.
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
I don't know what that means.I just sent you a very simple example
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.