473,322 Members | 1,690 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Building a Where Clause Using PC/Zip as Criteria Item

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!
Nov 12 '05 #1
4 1956
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!

Nov 12 '05 #2
"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
Nov 12 '05 #3
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!


Nov 12 '05 #4
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: 1-416-921-7082
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

Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: A.V.C. | last post by:
Hello, I found members of this group very helpful for my last queries. Have one problem with CASE. I can use the column name alias in Order By Clause but unable to use it in WHERE CLAUSE. PLS...
2
by: Ellen Manning | last post by:
Using A2K. In my table I have the field "Grant" which can have a value or be null. I have a query that counts the number of records and has a Where clause on the Grant field. The query won't...
1
by: BerkshireGuy | last post by:
Hello everyone, I am reading a query's SQL string and adding criteria to that and then resaving it. I know, might not be the best method, but it seems to work and there are some pros to doing...
4
by: Brian Shannon | last post by:
I have 3 combo boxes and two date text boxes on a .aspx page. The user can fill in any of the 5 controls or none to filter a datagrid. I was hoping someone could explain how to efficiently build...
9
by: DFS | last post by:
You might also find other uses ========================================================= Public Function getSelections(selType As String, selSeparator As Boolean, selList As ListBox, selCol As...
5
by: WebMatrix | last post by:
Hello, I am developing ASP.NET 1.1 application against SQL Server 2000 db, where users have ability to construct their own select criteria (for a where clause). I already developed a user...
2
by: Jim.Mueksch | last post by:
I am having a problem with using calculated values in a WHERE clause. My query is below. DB2 gives me this error message: Error: SQL0206N "APPRAISAL_LESS_PRICE" is not valid in the context where...
1
by: 2D Rick | last post by:
I want to open a report using OpenReport and passing it a Where Clause with multiple criteria. I know the Where Clause below is way off but it conveys what I need. Dates are of string type. ...
1
by: mtnbikur | last post by:
Lots of reading out there says you should only place “join criteria” in the join clause. That makes perfect sense. What about other search criteria? I’ve got other criteria, ie. cst_delete_flag =...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.