Function in a query

[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

 

[Alastair MacFarlane]

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

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>

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 Snell]

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>

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

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>

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

 

[Alastair MacFarlane]

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

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>

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

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>

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