2nd Post Function in Query

[Alastair MacFarlane]

Ken and all,

Thanks for the reply Ken. Somehow, the code below does not work.
Instinctively I think it should.

Works to show all A's:
Public Function MyAgencyContact () as String
strLeftLetter = "A"
strRightLetter = "D"
MyAgencyContact = "'[" & strLeftLetter & "]*'"
End Function

Does not work to show all A through to D:
Public Function MyAgencyContact () as String
strLeftLetter = "A"
strRightLetter = "D"
MyAgencyContact = "'[" & strLeftLetter & "-" & strRightLetter & "]*'"
End Function

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.

Originally I had MyAgencyContact = "Like '[" & strLeftLetter & "-" &
strRightLetter & "]*'". 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

 

[PC Datasheet]

Alastair,

"-" is not a legitimate operator!

The simple way to do what you want is to put the following expression in a field
in your query:

=Right([AgencyContact],1)

and then enter the following expression for the criteria of this field:

Between "A" And "D"


--
PC Datasheet
A Resource for Access, Excel and Word Applications
(e-mail address removed)
www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel

 

[Alastair MacFarlane]

PC Datasheet,

Thanks for the help Mr/Ms PC Datasheet!

Alastair

Alastair,

"-" is not a legitimate operator!

The simple way to do what you want is to put the following expression in a field
in your query:

=Right([AgencyContact],1)

and then enter the following expression for the criteria of this field:

Between "A" And "D"


--
PC Datasheet
A Resource for Access, Excel and Word Applications
(e-mail address removed)
www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel

Ken and all,

Thanks for the reply Ken. Somehow, the code below does not work.
Instinctively I think it should.

Works to show all A's:
Public Function MyAgencyContact () as String
strLeftLetter = "A"
strRightLetter = "D"
MyAgencyContact = "'[" & strLeftLetter & "]*'"
End Function

Does not work to show all A through to D:
Public Function MyAgencyContact () as String
strLeftLetter = "A"
strRightLetter = "D"
MyAgencyContact = "'[" & strLeftLetter & "-" & strRightLetter & "]*'"
End Function

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.

Originally I had MyAgencyContact = "Like '[" & strLeftLetter & "-" &
strRightLetter & "]*'". 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

 

[PC Datasheet]

Just noticed that =Right([AgencyContact],1) should have been
=Left([AgencyContact],1)

Steve
PC Datasheet

PC Datasheet,

Thanks for the help Mr/Ms PC Datasheet!

Alastair

Alastair,

"-" is not a legitimate operator!

The simple way to do what you want is to put the following expression in a field
in your query:

=Right([AgencyContact],1)

and then enter the following expression for the criteria of this field:

Between "A" And "D"


--
PC Datasheet
A Resource for Access, Excel and Word Applications
(e-mail address removed)
www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel

Ken and all,

Thanks for the reply Ken. Somehow, the code below does not work.
Instinctively I think it should.

Works to show all A's:
Public Function MyAgencyContact () as String
strLeftLetter = "A"
strRightLetter = "D"
MyAgencyContact = "'[" & strLeftLetter & "]*'"
End Function

Does not work to show all A through to D:
Public Function MyAgencyContact () as String
strLeftLetter = "A"
strRightLetter = "D"
MyAgencyContact = "'[" & strLeftLetter & "-" & strRightLetter & "]*'"
End Function

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.

Originally I had MyAgencyContact = "Like '[" & strLeftLetter & "-" &
strRightLetter & "]*'". 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]

I have replied in our orginal thread. I posted the solution there.

 

[Alastair MacFarlane]

Steve,

Thanks! I noticed it too and changed it, but thanks for the re-post!

Alastair

Just noticed that =Right([AgencyContact],1) should have been
=Left([AgencyContact],1)

Steve
PC Datasheet

PC Datasheet,

Thanks for the help Mr/Ms PC Datasheet!

Alastair

Alastair,

"-" is not a legitimate operator!

The simple way to do what you want is to put the following expression

in a
field

in your query:

=Right([AgencyContact],1)

and then enter the following expression for the criteria of this field:

Between "A" And "D"


--
PC Datasheet
A Resource for Access, Excel and Word Applications
(e-mail address removed)
www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel



Ken and all,

Thanks for the reply Ken. Somehow, the code below does not work.
Instinctively I think it should.

Works to show all A's:
Public Function MyAgencyContact () as String
strLeftLetter = "A"
strRightLetter = "D"
MyAgencyContact = "'[" & strLeftLetter & "]*'"
End Function

Does not work to show all A through to D:
Public Function MyAgencyContact () as String
strLeftLetter = "A"
strRightLetter = "D"
MyAgencyContact = "'[" & strLeftLetter & "-" & strRightLetter & "]*'"
End Function

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.

Originally I had MyAgencyContact = "Like '[" & strLeftLetter & "-" &
strRightLetter & "]*'". 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