Link to home
Start Free TrialLog in
Avatar of Monroe406
Monroe406

asked on

Delete these

I have a zipcode database in Access 2000, where the format of the table is:


CITY
STATE
ZIP CODE
AREA CODE
COUNTY
TIME ZONE
LAT
LONG
MISC

and from time to time I need to delete dozens of zip codes from the table, as zip codes are removed from the official USPS zip code list.  I will get an ASCII list of dead ZIP Codes, and I'd like to find a way to automate the deletion of these zip codes.  What I am looking for is to create a Memo component on a form, and paste the contents of the dead zipcodes into the Memo component, and then by clicking a button component, have Access go line by line through this list of zip codes, and look for a match in the master table, and delete any instances where the zip code was found.  IOW, here's a dummy list of dead zip codes:

38763
54744
56246
56513
56712
58014
58320
58432
62552
65573
68035
68054
71444
82631
89494
90198

When I paste these into the Memo, and click "Delete" button, I want all records containing these zip codes to be removed from the master table.
ASKER CERTIFIED SOLUTION
Avatar of paasky
paasky
Flag of Finland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of amp072397
amp072397

Monroe406:

You have many open questions. I will be posting this comment in all of them:

https://www.experts-exchange.com/jsp/qShow.jsp?ta=win2k&qid=20254049
https://www.experts-exchange.com/jsp/qShow.jsp?ta=win2k&qid=20172013
https://www.experts-exchange.com/jsp/qShow.jsp?ta=win2k&qid=20139842
https://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=20265114
https://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=20239542
https://www.experts-exchange.com/jsp/qShow.jsp?ta=iis&qid=20154037

To assist you in your cleanup, I'm providing the following guidelines:

1.  Stay active in your questions and provide feedback whenever possible. Likewise, when feedback has not been provided by the experts, commenting again makes them receive an email notification, and they may provide you with further information. Experts have no other method of searching for questions in which they have commented, except manually.

2.  Award points by hitting the Accept Comment As Answer button located above and to the left of that expert's comment.

3.  When grading, be sure to read:
https://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3
to ensure that you understand the grading system here at EE. If you grade less than an A, you must explain why.

4.  Questions that were not helpful to you should be PAQ'd (stored in the database for their valuable content?even if not valuable to you) or deleted. To PAQ or delete a question, you must first post your intent in that question to make the experts aware. Then, if no experts object after three full days, you can post a zero-point question at community support to request deletion or PAQ. Please include the link(s) to the question(s).
CS:  https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
At that point, a moderator can refund your points and PAQ or delete the question for you. The delete button does not work.

5.  If you fail to respond to this cleanup request, I must report you to the Community Support Administrator for further action.

Our intent is to get the questions cleaned up, and not to embarrass or shame anyone. If you have any questions or need further assistance at all, feel free to ask me in this question or post a zero-point question at CS. We are very happy to help you in this task!


thanks!
amp
community support moderator
Avatar of Monroe406

ASKER

To amp...

Your comment for this question is not appropriate.  This question is not even 1 hour old, and you are reprimanding me!
To paasky:

I am having problems with this line of code:

CurrentDb.Execute "delete from tblMaster where ZipCode=" & Chr(34) & strZip & Chr(34)

My table is called "Master Table", not "tblMaster".   Replacing "tblMaster" with "Table Master" generates an error.

CurrentDb.Execute "delete from Master Table where ZipCode=" & Chr(34) & strZip & Chr(34)
Monroe406:

Unfortunately, many people ignore requests to provide feedback unless the request is placed in a new question. Moderators have no way of knowing if the email on a member's account is accurate or not. Hence, this is much more effective than placing it only in the older questions.

I do not see one word of reprimand in my post and I have used it on thousands of open questions. Please see the definition of reprimand at:
http://education.yahoo.com/reference/dictionary/entries/46/r0164600.html

I have only told you that you have open questions, what you can do about them, and what will happen if you don't.

By far, your best recourse is to simply stay active in your open questions. Once I get to a point where all of the questions have been cleaned up, I hope to moderate this forum by ensuring that questions have had a comment in the past 7 days, even if it is just mine.

If you have any problems with the way that I carry out any of my tasks, which I perform voluntarily--I do not get compensated by EE or anyone else for the time I spend, feel free to contact the Community Support Administrator at ComTech@experts-exchange.com

And--when you need the help of a moderator, just ask! I hold NO grudges. I have had the same done to me when I left the site for several months. I cleaned up my questions, kept active in new ones, and that was that!

At your request, I'll be happy to delete my comments here, and the comments where you have replied to me. All you need do is ask.

thanks!
amp
community support moderator
Avatar of aikimark
use [Master Table]
>> use [Master Table]

I already tried that prior to posting...

The result was "Run time error 3061: Too few parameters. Expected 1".

CurrentDb.Execute "delete from [Master Table] where ZipCode=" & Chr(34) & strZip & Chr(34)
Delete * From [Master Table] Where ...
CurrentDb.Execute "delete * from [Master Table] where ZipCode=" & Chr(34) & strZip & Chr(34)   still generates the same error message: "Run time error 3061: Too few parameters. Expected 1".



Whoops!...  My mistake.  I erred with the name of the Zipcode field.  Sorry.  It works!  :-)