Need Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I am new to Access.I am trying to acheive the following.
I have a table in a database which needs to be cleaned, by which, I mean I
have to check if the values entered in the fields is right or wrong.
So I created a form and coded the same with the appropriate values in them.
What should I do next to run the code and clean the table.
Pls help me out here.
Thanks
 
What do you mean "clean"?

What are you trying to do specifically? This sounds like the job for an
update query.
 
By clean I mean that there are some values in the tables which are entered
wrong or they go out of a specific range.Since the tables are big they cannot
be manually corrected.Thats why the code to do it automatically.
I hope it makes sense now.
Thanks.
 
No, you need to tell us exactly. If you want us to tell you how to build an
update query to fix numbers, you have to tell us what numbers to fix, what
the wrong value is, and what the right one is.

For example, tell me that every record where the "color" field contains the
word "pink" you want to change it to be "salmon".

You say youwant the code to do it automatically, but we can;t tell you how
to build complicated code (or an update query) unless you tell us what you
want that code to do.

You say you created aform and "coded" it with the value. What does that
mean? You built a form and put a field called "old value" and a field
called "new value"? Access does not work that way.
 
Ok...this is what it is...
i have a table named MINI which has the fields d1,d1a and so on...but i am
just giving you a few..

the field d1 can have values as either 0 or 1. if it is 0 then there should
not be any values in the corresponding d1a field.
if d1 = 1 then d1a can be either 0 or 1.
basically the 0's and 1's are "yes or no".

similar rules apply for other fields in the table.

what has happened is there d1a fields does not have values which follows the
above rule.therefore it has to be corrected.
which is why i created a form and coded it.
I hope this helps.
Thanks.
 
Nikki....

I am wondering if your methodology is incorrect. I'm no expert, but if there
is a chance that your users would put the wrong information into a text box,
why not apply some format rules to it and or the field in the table?

As Rick says, you need to be more specific, post the code, give examples of
exactly what the data is that is wrong, what it should look like, etc....

The more info the better the answers will be....

cheers
 
Well, first off, Access uses a -1 and 0 to represent true/false or yes/no
fields. You might want to look into changing your field type.

But, I would try to stop others from making these mistkes in the first
place. In the form that your users use when entering data, you need to
build some code to prevent them from making an entry into the d1a field if
the d1 field has a false or "0" value. There are several ways to go about
that. I would probably "lock" the d1a fiedl if d1 = -1.
You would have to write code in several events in the form to do this.

You would want to check in the form's ON CURRENT event since this fires as
one moves between records. If I am in record number one and I have a d1
that is true, then I want to be able to make an entry in d1a. If I move to
record two and the d1 field is false, then I want to lock the d1a field so
no entry can be made.

You would also need to apply similar logic anytime a field is changed.
Let's say I am entering a brand new record. I go to the d1 field and set it
to "false" I would wnat the form to immediately lock the d1a field. This
would be done by writing code in the d1 field's ON EXIT event.



BUT, you are trying to fix your existing records. To do that, write a
query.

First, make a backup copy of your table...

Then, create a new query including your table. Pull field d1 and field d1a.
in the criteria under field d1 put =0. If you run the query you would see
all the records where d1 is set to "0". We need to tell Access to clear out
the d1a field for all these selected records. Change your query to an
Unpdate query by using the dropdown icon in the menu bar and selecting
"update query". This will cause a new field to appear in your query design
area called "Update to". Since we have already told it which records to
select, we can now tell it to change the d1a field to "" or blank. In the
Update To: box under d1a put "".

When you run the query, it will change all the records.


Repeat the steps for each change you ened to make.
 
Ok...this is what it is...
i have a table named MINI which has the fields d1,d1a and so on...but i am
just giving you a few..

the field d1 can have values as either 0 or 1. if it is 0 then there should
not be any values in the corresponding d1a field.
if d1 = 1 then d1a can be either 0 or 1.
basically the 0's and 1's are "yes or no".

similar rules apply for other fields in the table.

what has happened is there d1a fields does not have values which follows the
above rule.therefore it has to be corrected.
which is why i created a form and coded it.
I hope this helps.
Thanks.
Click to expand...

Nikki, I see you're getting Rick's usual good advice - but let me
throw out a more radical idea for consideration.

If you have a field (d1a) whose value depends on another field (d1)
then your table is not in properly normalized form. A field's value
should depend on the primary key, and ONLY on the primary key.

And if you have fields D1, D2, D3, ..., D20 it's even worse: embedding
a one to many relationship within each record is NOT good design! If
you have values for D1 to D20, and you ever need to add D21, you're in
trouble: all your forms, all your queries, all your reports need to be
changed.

I'm guessing that this is some sort of questionnaire; if so, you might
want to look at Duane Hookum's At Your Survey sample database:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

It uses a normalized design with one answer *per record* in a table,
rather than using multiple fields.

John W. Vinson[MVP]
 
Hi John,
Yes,Rick's answeres were helpful,but i did see the problem which you have
posted.I was not able to update a field which is dependant on an other field.
as in it gave a data type error when i tried to update a query by writing if
d1 = 0 then d1a ="".(d1 and d1a are fields).
What do you suggest for the the same.
Thanks
 
Hi John,
As you rightly said it is a questionnair for a study.The link which you had
given did not work.Lemme know what I should do.
Thanks.
 
Back
Top