Function in a query

  • Thread starter Thread starter Alastair MacFarlane
  • Start date Start date
A

Alastair MacFarlane

Dear All,

I am trying to use the function "MyAgencyContact" below in the criteria of a
query, such as Agency = the string return value of the function. The return
value in this example is - Like '[A-D]*', which works when I directly type
the string into the criteria box of the query, but it does not work with the
return value of the function. I would imagine that it is something to do
with the word 'Like'. Can someone please enlighten me?

Thanks again...

Alastair MacFarlane

----Code----

Public strLeftLetter As String * 1
Public strRightLetter As String * 1

Public Function MyAgencyContact() As String
strLeftLetter = "A"
strRightLetter = "D"
MyAgencyContact = "Like '[" & strLeftLetter & "-" & strRightLetter & "]*'"

End Function
 
Ken,

Thanks for the reply. Somehow, the code below does not work. Instinctively I
think it should.
strLeftLetter = "A"
strRightLetter = "D"

Works to show all A's:
MyAgencyContact = "'[" & strLeftLetter & "]*'"

Does not work to show all A through to D:
MyAgencyContact = "'[" & strLeftLetter & "-" & strRightLetter & "]*'"

If I have an entry with Airdrie Police, it will show in the first one but
not in the second one. No records are returned from the second query
criteria. What I want to do is display all the values in a query with the
first letter of the entry between A - D.

I have moved like to the criteria grid as suggested and it is now beginning
to annoy me.

Any thoughts I would be appreciated. Thanks again...

Alastair

Ken Snell said:
I believe that you cannot include the "Like" portion of the text string in
what the function returns to the query. Pull that out separately and show it
in the query's criterion, and then use the function to return the remainder
of the text string:

Like MyAgencyContact

Then change the important line of the function to this:
MyAgencyContact = "'[" & strLeftLetter & "-" & strRightLetter & "]*'"


--
Ken Snell
<MS ACCESS MVP>

Alastair MacFarlane said:
Dear All,

I am trying to use the function "MyAgencyContact" below in the criteria
Click to expand...
of
a
query, such as Agency = the string return value of the function. The return
value in this example is - Like '[A-D]*', which works when I directly type
the string into the criteria box of the query, but it does not work with the
return value of the function. I would imagine that it is something to do
with the word 'Like'. Can someone please enlighten me?

Thanks again...

Alastair MacFarlane

----Code----

Public strLeftLetter As String * 1
Public strRightLetter As String * 1

Public Function MyAgencyContact() As String
strLeftLetter = "A"
strRightLetter = "D"
MyAgencyContact = "Like '[" & strLeftLetter & "-" & strRightLetter & "]*'"

End Function
Click to expand...
Click to expand...
 
Change the function's line to this:
MyAgencyContact = "[" & strLeftLetter & "-" & strRightLetter & "]*'"

In other words, delete the ' characters from the line. I just tested in A2K2
without the ' marks in the function, and the query then returned the correct
information.

--
Ken Snell
<MS ACCESS MVP>

Alastair MacFarlane said:
Ken,

Thanks for the reply. Somehow, the code below does not work. Instinctively I
think it should.
strLeftLetter = "A"
strRightLetter = "D"

Works to show all A's:
MyAgencyContact = "'[" & strLeftLetter & "]*'"

Does not work to show all A through to D:
MyAgencyContact = "'[" & strLeftLetter & "-" & strRightLetter & "]*'"

If I have an entry with Airdrie Police, it will show in the first one but
not in the second one. No records are returned from the second query
criteria. What I want to do is display all the values in a query with the
first letter of the entry between A - D.

I have moved like to the criteria grid as suggested and it is now beginning
to annoy me.

Any thoughts I would be appreciated. Thanks again...

Alastair

Ken Snell said:
I believe that you cannot include the "Like" portion of the text string in
what the function returns to the query. Pull that out separately and
Click to expand...
show
it
in the query's criterion, and then use the function to return the remainder
of the text string:

Like MyAgencyContact

Then change the important line of the function to this:
MyAgencyContact = "'[" & strLeftLetter & "-" & strRightLetter & "]*'"


--
Ken Snell
<MS ACCESS MVP>

Alastair MacFarlane said:
Dear All,

I am trying to use the function "MyAgencyContact" below in the
Click to expand...
Click to expand...
criteria
of
a
query, such as Agency = the string return value of the function. The return
value in this example is - Like '[A-D]*', which works when I directly type
the string into the criteria box of the query, but it does not work
Click to expand...
with
the
return value of the function. I would imagine that it is something to do
with the word 'Like'. Can someone please enlighten me?

Thanks again...

Alastair MacFarlane

----Code----

Public strLeftLetter As String * 1
Public strRightLetter As String * 1

Public Function MyAgencyContact() As String
strLeftLetter = "A"
strRightLetter = "D"
MyAgencyContact = "Like '[" & strLeftLetter & "-" & strRightLetter & "]*'"

End Function
Click to expand...
Click to expand...
Click to expand...
 
Ken,

Thanks for the re-posting. I now understand why it did not work in the first
place. After some 'tinkering around', it now works.

Thanks again!

Alastair

Ken Snell said:
Change the function's line to this:
MyAgencyContact = "[" & strLeftLetter & "-" & strRightLetter & "]*'"

In other words, delete the ' characters from the line. I just tested in A2K2
without the ' marks in the function, and the query then returned the correct
information.

--
Ken Snell
<MS ACCESS MVP>

Alastair MacFarlane said:
Ken,

Thanks for the reply. Somehow, the code below does not work.
Click to expand...
Instinctively
I
think it should.
strLeftLetter = "A"
strRightLetter = "D"

Works to show all A's:
MyAgencyContact = "'[" & strLeftLetter & "]*'"

Does not work to show all A through to D:
MyAgencyContact = "'[" & strLeftLetter & "-" & strRightLetter & "]*'"

If I have an entry with Airdrie Police, it will show in the first one but
not in the second one. No records are returned from the second query
criteria. What I want to do is display all the values in a query with the
first letter of the entry between A - D.

I have moved like to the criteria grid as suggested and it is now beginning
to annoy me.

Any thoughts I would be appreciated. Thanks again...

Alastair

Ken Snell said:
I believe that you cannot include the "Like" portion of the text
Click to expand...
Click to expand...
string
in
what the function returns to the query. Pull that out separately and
Click to expand...
show
it
in the query's criterion, and then use the function to return the remainder
of the text string:

Like MyAgencyContact

Then change the important line of the function to this:
MyAgencyContact = "'[" & strLeftLetter & "-" & strRightLetter & "]*'"


--
Ken Snell
<MS ACCESS MVP>

Dear All,

I am trying to use the function "MyAgencyContact" below in the
Click to expand...
criteria
of
a
query, such as Agency = the string return value of the function. The
return
value in this example is - Like '[A-D]*', which works when I
Click to expand...
directly
type
the string into the criteria box of the query, but it does not work with
the
return value of the function. I would imagine that it is something
Click to expand...
Click to expand...
to
do
with the word 'Like'. Can someone please enlighten me?

Thanks again...

Alastair MacFarlane

----Code----

Public strLeftLetter As String * 1
Public strRightLetter As String * 1

Public Function MyAgencyContact() As String
strLeftLetter = "A"
strRightLetter = "D"
MyAgencyContact = "Like '[" & strLeftLetter & "-" & strRightLetter & "]*'"

End Function
Click to expand...
Click to expand...
Click to expand...
 
Back
Top