EMPTY CELLS!!! Validation Rule!!!

S

Shernan

How do I set up a cell to be 0.0 (zero) if there is no data available??? I
know is on the Validation Rule, but I don't know the formula.
I can't add the values of other cells becasue there are a few empty ones.
PLEASEEE HELP!!
Thanks
 
B

BruceM

It sounds as if setting the Default Value to 0 will accomplish what you
need. For existing records you will need to do something such as run an
update query to replace null with 0.
You may be able to use the control's Exit event to add the value, depending
on just what you need to do. I don't think you can use table-level
validation to add a value to the field.
 
D

Dirk Goldgar

In
Shernan said:
How do I set up a cell to be 0.0 (zero) if there is no data
available??? I know is on the Validation Rule, but I don't know the
formula.
I can't add the values of other cells becasue there are a few empty
ones. PLEASEEE HELP!!
Thanks
Click to expand...

No validation rule can actually change data -- all it can do is prevent
the data from being saved if it doesn't meet the rule. For a number
field, I think the best you can do to get what (I think) you want is to
set the field's Default Value property to 0 and set its Required
property to True. That will prevent records from being saved with Null
values in that field, and put an initial a value of 0 in any new records
that may be created (until you change the field's value to something
else).

If you have existing records that have Null values in this field, you
can set their values to 0 using an update query. I'd recommend doing
that before you set the field's Required property to True.

Incidentally, the term "cell" is an Excel concept you should probably
not use, for good communication with Access specialists. In Access we
deal with tables, columns, rows, records, fields, and controls, but not
"cells".
 
A

Arvin Meyer [MVP]

Databases don't have cells, spreadsheets have cells. Columns in databases
are called fields and records are called rows. If a field has a default
value of 0, it will automatically enter that value in that row for that
field if nothing else is entered. Now it is possible to delete that 0, and
you would need validation to ensure that it didn't happen, or you could use
the BeforeUpdate event of the form to check whether there was a value there
before moving on.
 
S

Shernan

Thank you for your response!!
I think I know how to chage the default values to 0, but I don't know how to
change the "Requiere Property" to True!?
I check on the Acces Help and it's aying to change the "Allow Zero Lenght"
but I don't know where is that either.

Thank you!!
Hernan
 
D

Dirk Goldgar

In
Shernan said:
Thank you for your response!!
I think I know how to chage the default values to 0, but I don't know
how to change the "Requiere Property" to True!?
I check on the Acces Help and it's aying to change the "Allow Zero
Lenght" but I don't know where is that either.
Click to expand...

This would be set in design view of the table, assuming that you want
the rule to apply to all records in the table, and not just those that
are entered on a particular form. Open the table in design view, click
on the field in the list of fields in the top pane of the design window,
and the field's properties will be listed in the property sheet in the
bottom pane of the window. On the General tab of that property sheet,
you'll see the property named "Required". You want to set its value (on
the line to the right of the name) to "Yes".

Allow Zero Length is another field property, but it only applies to Text
or Memo fields and won't appear for Number fields. I'm assuming your
field is a number, not text, since you want it to default to 0.
 
S

Shernan

Ok I'm sorry about the "cell" thing, I got it. it's my first time with Acces
as you can see.
Now I'm changing Default Property Value to "0" and nothing happens, I think
the reason is becasue I'm not setting the Required property to True ...of
course because I don't know how to do that. Do you know how to se the require
property to True??
Thank you.
Hernan
 
S

Shernan

Thank you so much, that works!!!!!!!

Dirk Goldgar said:
In

This would be set in design view of the table, assuming that you want
the rule to apply to all records in the table, and not just those that
are entered on a particular form. Open the table in design view, click
on the field in the list of fields in the top pane of the design window,
and the field's properties will be listed in the property sheet in the
bottom pane of the window. On the General tab of that property sheet,
you'll see the property named "Required". You want to set its value (on
the line to the right of the name) to "Yes".

Allow Zero Length is another field property, but it only applies to Text
or Memo fields and won't appear for Number fields. I'm assuming your
field is a number, not text, since you want it to default to 0.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

In
Shernan said:
Now I'm changing Default Property Value to "0" and nothing happens
Click to expand...

The Default Value only applies to new records. It will have no effect
on any existing records. You'll have to go back and change them. You
can use an update query to do that.
 
S

Steve Schapel

Shernan,

See Dirk's reply for how to change the Required property.

Regarding "nothing happens", you should note that the Default Value
property only affects new records when they are created. Setting that
property will not affect the data (or lack thereof) in the existing
records. You will need to update those, probably using an Update Query
as was suggested to you before. And you will need to do that before you
try setting the Required property.
 
Top