EMPTY CELLS!!! Validation Rule!!!

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

0
Utf
9/19/2007 5:48:02 PM
access 16762 articles. 3 followers. Follow

9 Replies
1002 Views

Similar Articles

[PageSpeed] 4

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.

"Shernan" <Shernan@discussions.microsoft.com> wrote in message 
news:47AD22FD-B2FC-475C-A617-3DF0DE177F4F@microsoft.com...
> 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
> 


0
BruceM
9/19/2007 5:59:55 PM
In news:47AD22FD-B2FC-475C-A617-3DF0DE177F4F@microsoft.com,
Shernan <Shernan@discussions.microsoft.com> wrote:
> 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

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".

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

(please reply to the newsgroup)


0
Dirk
9/19/2007 6:00:57 PM
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.
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Shernan" <Shernan@discussions.microsoft.com> wrote in message 
news:47AD22FD-B2FC-475C-A617-3DF0DE177F4F@microsoft.com...
> 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
> 


0
Arvin
9/19/2007 6:09:46 PM
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

"Dirk Goldgar" wrote:

> In news:47AD22FD-B2FC-475C-A617-3DF0DE177F4F@microsoft.com,
> Shernan <Shernan@discussions.microsoft.com> wrote:
> > 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
> 
> 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".
> 
> -- 
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
> 
> (please reply to the newsgroup)
> 
> 
> 
0
Utf
9/19/2007 6:20:03 PM
In news:86B08F5D-A60E-4495-86D6-35C0626D1E4F@microsoft.com,
Shernan <Shernan@discussions.microsoft.com> wrote:
> 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.

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)


0
Dirk
9/19/2007 6:32:26 PM
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

"Arvin Meyer [MVP]" wrote:

> 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.
> -- 
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
> 
> "Shernan" <Shernan@discussions.microsoft.com> wrote in message 
> news:47AD22FD-B2FC-475C-A617-3DF0DE177F4F@microsoft.com...
> > 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
> > 
> 
> 
> 
0
Utf
9/19/2007 6:38:04 PM
Thank you so much, that works!!!!!!!

"Dirk Goldgar" wrote:

> In news:86B08F5D-A60E-4495-86D6-35C0626D1E4F@microsoft.com,
> Shernan <Shernan@discussions.microsoft.com> wrote:
> > 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.
> 
> 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)
> 
> 
> 
0
Utf
9/19/2007 6:50:06 PM
In news:6CE51D6F-183F-4D79-ABF9-41E171369170@microsoft.com,
Shernan <Shernan@discussions.microsoft.com> wrote:
> Now I'm changing Default Property Value to "0" and nothing happens

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.

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

(please reply to the newsgroup)


0
Dirk
9/19/2007 6:55:57 PM
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.

-- 
Steve Schapel, Microsoft Access MVP

Shernan wrote:
> 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??
0
Steve
9/19/2007 6:59:41 PM
Reply:

Similar Artilces:

formatting cells for radar graph
Is there a way to format cells so that when you type in a whole number, it is converted to a decimal? (EX: If I type in 150, I want the cell to format this whole number as .150) I have a radar graph that I have set up to graph product thickness, when the thickness is less than a whole number. Thanks Hi Debbie, Yes: use Tools|Options and open Edit tab; locate Fixed Decimals and set it to 3 However, I think this is "global" and will apply to all workbooks you subsequently open; so do remember to set it off. Alternative: enter the three digit numbers; type 1000 in an empty ce...

Too many cell formats #3
I am wondering if there is any way for me to find out how many cell formats I am using. I know the linit is 4000 and I hve a user that uses very close to that. Is there a report or a third party app that would let me check my sheets fo this information? Thanks and Happy Holidays. I'm having difficulty understanding why there would be so many cell formats? What purpose does this serve? "Adrian" wrote: > I am wondering if there is any way for me to find out how many cell formats > I am using. I know the linit is 4000 and I hve a user that uses very close > to th...

Format Cells Custom Forumula?
How do I interpret (#,##0_);[Red](#,##0) or other similar formulas so I can figure out what is wrong or create a new one for a specific situation? I looked throughout help but couldn't find anything. I know this one rounds the value of the formula and spaces it so if it is a negagive it doesn't mess up the spacing on the page, but I don't know how to read the actual formula to determine what does what. Thanks, Mike See "About custom number formats" in XL Help. In article <F2FEC468-63AE-4F9C-BE97-ED44A6D5CDED@microsoft.com>, Mike King <Mike King@discussi...

my formated column is displaying one cell incorrectly
I have a spreadsheet with 4 columns; the 4th columns is a formula going across the 3 columns a+b-c = d with d preferably equally zero. When I format column d almost all of the columns that equal zero display as a dash (preference). However periodically one or two of the rows will display as 0.00 and I can't seem to get the formatting to change to the dash. I have tried to reformat all columns all other cells and to no avail. Has anyone else run into this issue and if so how have u solved it. The only way I can solve it is to actually put a zero in the cell. If the numb...

Macro/keyboard shortcut to increment a cell value
I would like to set up a keyboard shortcut (e.g., Ctrl+I) that would increment the value of the selected cell by 1. Can someone get me started by pointing me in the right direction? I have some experience creating simple macros in Word, if that is what's needed. Thanks -- PC: HP Omnibook 6000 OS: Win 2K SP-4 (5.00.2195) LAN: P2P with an HP Vectra workstation Email: Usenet-20031220 at spamex.com (11/03/04) One way is to make a macro and assign the shortcut key to it: Option Explicit Sub AddOneToActiveCell() With ActiveCell If IsNumeric(.Value) Then .Value = .Val...

Referencing a Range of Cells
Hi all, I have named a range of cells on one worksheet, say I called (A1:B10) as "My_Range" on "WorkSheet_1". And then I got another worksheet which want to copy the same value on another worksheet "WorkSheet_2" using the name as a reference. How could I do this? Also, could I specify a particular value inside the named range? For example how could I display the B3 cell in "My_Range"? Thanks for your help. Derek C hi. not sure why you would want to do it this way but try this Sub macSetRange() Dim rng As Range 'range to copy Dim rng1 As...

Code to read a cell from every workbook in a folder
Any Ideas about what code would read the formula in cell c3 of ever workbook in a folder and give an array of the workbook names and th formula??? -- Message posted from http://www.ExcelForum.com Something like this? Option Explicit Sub testme01() Dim myFiles() As String Dim fCtr As Long Dim myFile As String Dim myPath As String Dim tempWkbk As Workbook Dim wks As Worksheet Dim oRow As Long Dim logWks As Worksheet Dim myAddr As String myAddr = "C3" 'change to point at the folder to check myPath = "...

format cell
How to set a cell so that when I key in [number] follow by [/] follow by [number] and display as number - number I.e key in 3/4, appear as 3 - 4 Not by formatting alone. You could format the cell as text (not general, not a date) and use the adjacent cell to return the string the way you want to see it: =substitute(a1,"/"," - ") After you do all your data entry, you could hide the original column or even convert the formula column to values and delete the original column. crapit wrote: > > How to set a cell so that when I key in [number] fol...

Can you change only one cell size in a workbook?
No. You can change the width of a column, or the height of a row. And next time, please remember to ask the question in the body of the message, not just in the subject line. And if this is related to charting, it is not clear how. Was this intended for one of the other Excel groups? -- David Biddulph "Becky" <Becky@discussions.microsoft.com> wrote in message news:4A4504F7-4FA9-44C4-A79C-5E67A9E8C169@microsoft.com... > ...

Clearing Cells
Hi I'm using an Excel sheet for pricing items customers purchase. In A2 I could enter any of 4 prices e.g. £30 , £15, £10, £5 In B2, C2, D2, E2, I have entered the following formula =IF(A2=Rate_30,A2,0) It follows that B2 shows £30 and C2,D2,E2 show 0.00 that works ok the problem is with 200 rows its a lot of zero's is it possible to add to the formula something that clears all the zero's so that the columns are easier to read. This sheet is used on a daily basis so the customer could call in tomorrow and buy something at £15 the £30 would c...

a macro to copy only 2 lines w/i a cell separated by 4 line breaks
Can anyone help me determine how to copy only 2 specific lines from a cell that contains text in 4 lines that is separated using the line break function? Which two lines? You may be better off parsing into 4 cells then picking the two you want Gord Dibben MS Excel MVP On Wed, 14 Apr 2010 15:31:01 -0700, poltpar <poltpar@discussions.microsoft.com> wrote: >Can anyone help me determine how to copy only 2 specific lines from a cell >that contains text in 4 lines that is separated using the line break function? ...

incoming email rules
is there a way to setup a rule that will place any incoming emails into a folder if the senders email address is not in one of my address books? Please respond to bh@vail.net. Thanks ...

Can i create a rule for Sent items?
Like you can have a rule, that when an item arrives, it goes into a particular folder, can you also have such a rule for SENT items. Like, whenever i send an item to a certain Email address, that it would go into a different folder from the SENT ITEMS folder? If so, can you tell me how to do it, as i do not see an obvious way within the Rules Wizard. Thanks, tom No one can tell since you decided your Outlook version is not in play - = it is. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal acc...

set excel cell value
i'm no expert at vba and have been having a hard time trying to send a value from an open access form to a specific cell in excel. I've been writing a module in Ac2000 that opens excel, names the sheet, and sends some Access table data over. Thanks to Bob Larsons code I was able to do most of this. After a week of trial and error i'm now also able to format the sheet the way it needs to be, plus some conditional formatting that seems to be working out great- now i'm very badly stuck on trying to send a value from an open access form to a particular cell in the shee...

How do I run a rule by using a toolbar icon?
I have Outlook 2007 and have a rule set up to delte spam by detecting certain words in the spam. Here is what someone gave me but it does not work: Sub RunRuleDeleteSpam() Dim oNS As Outlook.NameSpace Dim oStore As Outlook.Store Dim colRules As Outlook.Rules Dim oRule as Outlook.Rule Set oNS = Application.GetNameSpace("MAPI") Set oStore = oNS.DefaultStore Set colRules = oStore.Rules Set oRule = colRules.Item("Delete Spam") oRule.Execute End Sub If I run it it highlights the set oRule line and stops, I guess. I know n...

OL2003 - Rules
I have three POP3 accounts setup in Outlook 2003 each from different providers. So, I created two rules to move the email based on specified account to other folders to separate the email based on account. So, my default account goes into the Inbox and the other accounts go into their own specific folders. When I setup the rules I choose "through the specified account" in the "condition" section then specify the account. Then choose "move it to the specified folder" in the "what to do with the message" section then specify the folder to move it to. ...

is it possible to change print ink color based on cell value?
Is it possible to change print ink color based on value in a cell for a spreadsheet application? Example: If a cell's value is over 250, can I have it print the number in red ink to draw my attention to it?? You can use Conditional Formatting (Format>Conditional Formatting) to test the value and set the font colour accordingly. -- HTH RP (remove nothere from the email address if mailing direct) "Dave The Mechanic" <Dave The Mechanic@discussions.microsoft.com> wrote in message news:0B63AB99-5824-4AFB-A206-D4CDF1BB9D48@microsoft.com... > Is it possible to chang...

custom a cell
How do make that when I enter 1234 its should come out 12.3 -- shlom ----------------------------------------------------------------------- shlomo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1530 View this thread: http://www.excelforum.com/showthread.php?threadid=34680 Format > Cell > Number > change decimal and symbol -- Cissy ------------------------------------------------------------------------ Cissy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20107 View this thread: http://www.excelforum.com/showthread....

Cond format based on if field value is valid
Hello all, I have a combo box whos control source is a filed on a form,its Row Source type is a query and its row source is a record from the query. Basically the field on the form is a category ID number. The query compares that category number to the list of category numbers and the end result is that the category descritpion text is displayed on the form. I need that descritpion combo box to change colors if the category code is not on the list of available category codes. How can I accomplish this? Thanks for your advice.... ...

Matching cells by content then cell fill with color
Thanks to JEM, I am using this routine to color three consecutive cell a specific color, in this case red: Public Sub ThreeCellsRed() ActiveCell.Resize(1,3).Interior.ColorIndex = 3 End Sub What I need now is a way for the routine to continue to find all th similar cells, let's say for sake of disc they are people's names, s when I execute the above on my name, mrh, I want it to continue in th worksheet and find all exact matches and color those same cells red. Another thought, say my name (MRH) is in "A1" and it is also in "D1". But in "D1" I use "...

Moving cursor to another cell w/out selecting entire area
PLEASE write your question in the body of the post and NOT just the subject Have a look here: http://dts-l.org/goodpost.htm -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk "Darin" <Darin@discussions.microsoft.com> wrote in message news:525460EF-810C-4599-A841-2940CB8E0471@microsoft.com... > PLEASE write your reply in the body of the post and NOT in the subject line. Have a look here: http://dts-l.org/goodpost.htm -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk "Ragdyer&qu...

Workflow Rule creation
I am trying to create a workflow rule that will prompt a user to contact a lead 7 days after en email is sent to the lead. I got a workflow rule to create a task to follow-up but the task does not contain any reference to the specific emai lthat is to be followed up on. Is there a way to attach the original email to the workflow created task so that the user would know what it is they are following up on? ...

Count Consecutive Cells #2
I have a simple need: I have one column of data containing either an O or a U. Is there a way I can ask EXCEL to tell me how many times the O appeared in 2 consecutive cells, 3 consecutive cells... etc. Say I am using collumn A for this A 1 O 2 O 3 O 4 U 5 U 6 O 7 U 8 U 9 U 10 U Try the following... B1, copied down: =SUM(--(COUNTIF(OFFSET($A$1:$A$10,ROW($A$1:$A$10)-MIN(ROW($A$1:$A$10)),0, ROWS($B$1:B1)+1),"O")=ROWS($B$1:B1)+1)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Note that the result displayed in B1 will tell you how many times 'O' appe...

Ranking cells largest to smallest
I've got a large amount of data that I've sorted with a pivot table. I need to order the data within each column of the pivot table (outside of the pivot table). Example: Frequency A 6 B 7 C 6 D 5 E 4 Obviously, the largest number in the frequency column is 7 and obviously, the next largest number is 6. What I'm trying to do is to get Excel to list the results by row heading (i.e., B is the largest, A is the next largest, C is the next largest, etc.) even when there is a duplicate number. I've been using the =large() function to no avail (it gives me a value of 6 fo...

How do I copy data (word) into respective cells when the data bei.
I have word data in 2 or more collums (not cells). When I copy and paste that data into a cell (word or excel) all that data goes into one cell. How do I get the data into the respective cells without going back and cut from one cell to another? Hi one method that i've used before to do this may work for you, in Word, select the columns then click on the INSERT TABLE icon on the toolbar - if you're lucky the columns will convert to two tables side by side then select the left hand side table (it might also select the right it doesn't matter) then click on the split cells ico...