2nd Post Function in Query

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

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
 
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
 
PC Datasheet,

Thanks for the help Mr/Ms PC Datasheet!

Alastair

PC Datasheet said:
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 said:
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
Click to expand...
Click to expand...
 
Just noticed that =Right([AgencyContact],1) should have been
=Left([AgencyContact],1)

Steve
PC Datasheet


Alastair MacFarlane said:
PC Datasheet,

Thanks for the help Mr/Ms PC Datasheet!

Alastair

PC Datasheet said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Steve,

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

Alastair

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

Steve
PC Datasheet


Alastair MacFarlane said:
PC Datasheet,

Thanks for the help Mr/Ms PC Datasheet!

Alastair

PC Datasheet said:
Alastair,

"-" is not a legitimate operator!

The simple way to do what you want is to put the following expression
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top