using a string function as a criteria?

[Howard]

The field in my table that I use to search contains strings such as
Y_10_S01, Y_11_S01 or Y_11_S02 that have a natural alphabetical order.

If I use either Between "Y_10_S01" and "Y_11_S01" (with double quotes) or
Between 'Y_10_S01' and 'Y_11_S01' (single quotes) directly into the query it
works fine.

However if I first store the critera in a string variable inside a module
e.g.

MyCriteria = "Between 'Y_10_S01' and 'Y_11_S01'" (single quotes encloed
withing double) and
and a function

Function GetCriteria() as string
GetCriteria = MyCriteria
End function

is called in the query criteria instead, the query returns nothing.
A debug msgbox is showing the function to be correctly returning Between
'Y_10_S01' and 'Y_11_S01'

What am I doing wrong?
Howard

 

[Stefan Hoffmann]

hi Howard,

MyCriteria = "Between 'Y_10_S01' and 'Y_11_S01'" (single quotes encloed
withing double) and
and a function
Function GetCriteria() as string
GetCriteria = MyCriteria
End function
What am I doing wrong?

I assume you're trying this (switch your query to the SQL view):

SELECT *
FROM yourTable
WHERE yourField = GetCriteria()

The problem here is, that the Expression Service cannot unpack your
string, so that it is useable as condition in Jet.

WHERE yourField = GetCriteria()

will be translated for Jet to

WHERE yourField = "Between 'Y_10_S01' and 'Y_11_S01'"

This will obviously return nothing. One possible solution:


Public Function MatchCriteria(AValue As String) as Boolean

MatchCriteria = AValue >= "Y_10_S01" And AValue <= "Y_11_S01"

End function


mfG
--> stefan <--

 

[Howard]

hi Howard,

I assume you're trying this (switch your query to the SQL view):

SELECT *
FROM yourTable
WHERE yourField = GetCriteria()

The problem here is, that the Expression Service cannot unpack your
string, so that it is useable as condition in Jet.

WHERE yourField = GetCriteria()

will be translated for Jet to

WHERE yourField = "Between 'Y_10_S01' and 'Y_11_S01'"

This will obviously return nothing. One possible solution:


Public Function MatchCriteria(AValue As String) as Boolean

MatchCriteria = AValue >= "Y_10_S01" And AValue <= "Y_11_S01"

End function


mfG
--> stefan <--

Thanks for the fast reply Stefan but could you please explain how I should
use your function? The criteria won't always be as in my example. I have a
modal selection form with two combo boxes from which the the user selects
the upper and lower value (making them both the same if they only want one
value) and that form is used to give the variable MyCriteria its value.
Showing the selection form and then displaying a report based on my query
are done in sequence when a button is clicked on another form. If there is a
better way to do all this I'm happy to alter it.

Howard

 

[Stefan Hoffmann]

hi Howard,

Thanks for the fast reply Stefan but could you please explain how I should
use your function? The criteria won't always be as in my example. I have a
modal selection form with two combo boxes from which the the user selects
the upper and lower value (making them both the same if they only want one
value) and that form is used to give the variable MyCriteria its value.
Showing the selection form and then displaying a report based on my query
are done in sequence when a button is clicked on another form. If there is a
better way to do all this I'm happy to alter it.

Maybe this works for you:

Option Compare Database
Option Explicit

Private m_LBound As String
Private m_UBound As String

Public Sub SetBounds(ALBound As String, AUBound As String)

m_LBound = ALBound
m_UBound = AUBound

End Sub

Public Function MatchCriteria(AValue As String) as Boolean

MatchCriteria = AValue >= m_LBound And AValue <= m_UBound

End function

Set the bounds before closing your modal form.


mfG
--> stefan <--

 

[Howard]

hi Howard,

Maybe this works for you:

Option Compare Database
Option Explicit

Private m_LBound As String
Private m_UBound As String

Public Sub SetBounds(ALBound As String, AUBound As String)

m_LBound = ALBound
m_UBound = AUBound

End Sub

Public Function MatchCriteria(AValue As String) as Boolean

MatchCriteria = AValue >= m_LBound And AValue <= m_UBound

End function

Set the bounds before closing your modal form.


mfG
--> stefan <--

Thank you, that did it

Howard