Building a Where Clause Using PC/Zip as Criteria Item

[Barry Edmund Wright]

I would really appreciate your assistance.

I am using Access 2000 to create a form that Lists Names and Addresses based
on a number of selection criteria one of which is a combo box cboPCZip. All
the selection criteria are used to build a Where clause (strWhere1).

If optBeginsContains = 1 Then 'Return records Beginning with the value in
cboPCZip
strWhere1 = strWhere1 & " And ((tblAddress.strPCZip) like """ & cboPCZip
& "*"")"
Else 'Return records Containing the value in cboPCZip
strWhere1 = strWhere1 & " And ((tblAddress.strPCZip) like """ & "*" &
cboPCZip & "*"")"
End If

Problem:
If cboPCZip is NOT a Number, i.e., 1234-4235 records are found and returned.
However if cboPCZip isNumeric, i.e., 12344235 no records are returned, even
though there are Address records where the PCZip field contains the zip code
12344235.

For Example:
This does NOT work: Where ((tblAddress.strPCZip) like "12344235*")
This does work: Where ((tblAddress.strPCZip) like "1234-4235*")

What am I doing wrong?

Thanks for your help!

[Alan Webb]

Barry,
Probably not a code problem. You've probably got junk data in your postal
codes. Some of your users have typed the same postal code in a variant form
and it is not matching your WHERE clause. Modifying the postal codes to
conform to a standard would be one solution but changing these could have
consequences for anything that has been written assuming the data remains as
it is. Better idea is to add a column to your table with the corrected
postal codes and have your code join on the new postal code column. Also
guard the new postal code column with vicious dogs so users don't create a
new issue by adding junk to that column as well.

"Barry Edmund Wright" <ba*****************@NOSPAMrogers.com> wrote in
message news:hS************@twister01.bloor.is.net.cable.r ogers.com...

I would really appreciate your assistance.

I am using Access 2000 to create a form that Lists Names and Addresses based on a number of selection criteria one of which is a combo box cboPCZip. All the selection criteria are used to build a Where clause (strWhere1).

If optBeginsContains = 1 Then 'Return records Beginning with the value in
cboPCZip
strWhere1 = strWhere1 & " And ((tblAddress.strPCZip) like """ & cboPCZip & "*"")"
Else 'Return records Containing the value in cboPCZip
strWhere1 = strWhere1 & " And ((tblAddress.strPCZip) like """ & "*" &
cboPCZip & "*"")"
End If

Problem:
If cboPCZip is NOT a Number, i.e., 1234-4235 records are found and returned. However if cboPCZip isNumeric, i.e., 12344235 no records are returned, even though there are Address records where the PCZip field contains the zip code 12344235.

For Example:
This does NOT work: Where ((tblAddress.strPCZip) like "12344235*")
This does work: Where ((tblAddress.strPCZip) like "1234-4235*")

What am I doing wrong?

Thanks for your help!

[Bradley]

"Barry Edmund Wright" <ba*****************@NOSPAMrogers.com> wrote in
message news:hS************@twister01.bloor.is.net.cable.r ogers.com...

I would really appreciate your assistance.

I am using Access 2000 to create a form that Lists Names and Addresses based on a number of selection criteria one of which is a combo box cboPCZip. All the selection criteria are used to build a Where clause (strWhere1).

If optBeginsContains = 1 Then 'Return records Beginning with the value in
cboPCZip
strWhere1 = strWhere1 & " And ((tblAddress.strPCZip) like """ & cboPCZip & "*"")"
Else 'Return records Containing the value in cboPCZip
strWhere1 = strWhere1 & " And ((tblAddress.strPCZip) like """ & "*" &
cboPCZip & "*"")"
End If

Problem:
If cboPCZip is NOT a Number, i.e., 1234-4235 records are found and returned. However if cboPCZip isNumeric, i.e., 12344235 no records are returned, even though there are Address records where the PCZip field contains the zip code 12344235.

For Example:
This does NOT work: Where ((tblAddress.strPCZip) like "12344235*")
This does work: Where ((tblAddress.strPCZip) like "1234-4235*")

What am I doing wrong?

Thanks for your help!

I suspec tthe LIKE operator only works for strings/text? When you insert a
dash (-) Access probably assumes it is a string.

Maybe insert some single quotes around your value?

Where ((tblAddress.strPCZip) like "'12344235*'")
--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response

[Barry Edmund Wright]

Thanks you your response Alan.

Even though the data in the field was OK your response made look deeper,
specifically, the cboPCZip.rowsource code. There I found an error which was
returning an incorrect value which of course it couldn't find because the
value was not in the database field strPCZip that the combo box was based
on.

"Alan Webb" <kn*****@hotmail.com> wrote in message
news:g2*****************@news.uswest.net...

Barry,
Probably not a code problem. You've probably got junk data in your postal
codes. Some of your users have typed the same postal code in a variant form and it is not matching your WHERE clause. Modifying the postal codes to
conform to a standard would be one solution but changing these could have
consequences for anything that has been written assuming the data remains as it is. Better idea is to add a column to your table with the corrected
postal codes and have your code join on the new postal code column. Also
guard the new postal code column with vicious dogs so users don't create a
new issue by adding junk to that column as well.

"Barry Edmund Wright" <ba*****************@NOSPAMrogers.com> wrote in
message news:hS************@twister01.bloor.is.net.cable.r ogers.com...

I would really appreciate your assistance.

I am using Access 2000 to create a form that Lists Names and Addresses

based

on a number of selection criteria one of which is a combo box cboPCZip.

All

the selection criteria are used to build a Where clause (strWhere1).

If optBeginsContains = 1 Then 'Return records Beginning with the value in cboPCZip
strWhere1 = strWhere1 & " And ((tblAddress.strPCZip) like """ &

cboPCZip

& "*"")"
Else 'Return records Containing the value in cboPCZip
strWhere1 = strWhere1 & " And ((tblAddress.strPCZip) like """ & "*" &
cboPCZip & "*"")"
End If

Problem:
If cboPCZip is NOT a Number, i.e., 1234-4235 records are found and

returned.

However if cboPCZip isNumeric, i.e., 12344235 no records are returned,

even

though there are Address records where the PCZip field contains the zip

code

12344235.

For Example:
This does NOT work: Where ((tblAddress.strPCZip) like "12344235*")
This does work: Where ((tblAddress.strPCZip) like "1234-4235*")

What am I doing wrong?

Thanks for your help!

[Barry Edmund Wright]

Thank you for your response Bradley.
I actually found the error to be in the SQL string used as the cboPCZip.recordsource.

Bradley, I looked at the web sites you have developed and was facinated by the fact that they are based on, I presume, an Access
database; I beleive the Access Web also uses this technique. Could you give me some information on how you learned to do this, i.e.,
books read, courses taken, articles read, etc.

I would really appreciate hearing back from you.
--
Barry Edmund Wright
Microsoft Access Developer

301-100 Spadina Rd.
Toronto, Ontario M5R 2T7
Phone: [phone]
E-mail: ba*****************@NOSPAMrogers.com

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message news:40******@nexus.comcen.com.au...
"Barry Edmund Wright" <ba*****************@NOSPAMrogers.com> wrote in
message news:hS************@twister01.bloor.is.net.cable.r ogers.com...

I would really appreciate your assistance.

I am using Access 2000 to create a form that Lists Names and Addresses based on a number of selection criteria one of which is a combo box cboPCZip. All the selection criteria are used to build a Where clause (strWhere1).

If optBeginsContains = 1 Then 'Return records Beginning with the value in
cboPCZip
strWhere1 = strWhere1 & " And ((tblAddress.strPCZip) like """ & cboPCZip & "*"")"
Else 'Return records Containing the value in cboPCZip
strWhere1 = strWhere1 & " And ((tblAddress.strPCZip) like """ & "*" &
cboPCZip & "*"")"
End If

Problem:
If cboPCZip is NOT a Number, i.e., 1234-4235 records are found and returned. However if cboPCZip isNumeric, i.e., 12344235 no records are returned, even though there are Address records where the PCZip field contains the zip code 12344235.

For Example:
This does NOT work: Where ((tblAddress.strPCZip) like "12344235*")
This does work: Where ((tblAddress.strPCZip) like "1234-4235*")

What am I doing wrong?

Thanks for your help!

I suspec tthe LIKE operator only works for strings/text? When you insert a
dash (-) Access probably assumes it is a string.

Maybe insert some single quotes around your value?

Where ((tblAddress.strPCZip) like "'12344235*'")
--
Bradley
Software Developer www.hrsystems.com.au
A Christian Response www.pastornet.net.au/response