FILL BLANK FIELDS WITH "0" 3

[DIVINEDAR0956]

I have a table with numeric fields that I brought over from Paradox. In this table some of the fields are empty and needs to have a ZERO put in each field. There are thousands of blank fields that need this ZERO. Is there a code or a way I fill this blank fields with zero easily then besides going to each field and inserting a zero. I have the default set at zero but that doesn't fill the already existing fields with zero.

Please Help. Thank you. Darlene Sippio
dsippio@comtechsystems.com

 

[neillovell]

Can you change the validation rule to say

>= 0

I'm not sure, but that MIGHT convert all blank fields to 0.

 

[DIVINEDAR0956]

Thank you for the jesture but that didn't work either. I need a code that will find a blank field and input a "0" in each blank field. >= 0 may work for future purposes.

Can you still help me. Darlene Sippio
dsippio@comtechsystems.com

 

[SgtJarrow]

Do an update query....in the criteria field put:

IsNull

and in the UpdateTo line, put:

0

That will insert a zero into any field that is blank (null) Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[DIVINEDAR0956]

Thank you but that didn't work either. The blank fields already exist. I'm just trying to input a zero in a blank field.

I tried the following:

IIf(IsNull([HRSENTER]![MONTH_1]),0,[HRSENTER]![MONTH_1])

But this should have worked but it doesn't.

Is there a way to tell it to go to each empty cell or field and input a zero in each empty cell or field.

Thank you again for your help. Darlene Sippio
dsippio@comtechsystems.com

 

[MichaelRed]

First, you need to define what is meant by empty.

It MAY be Null, then again it might be "", on the other hand it could be some 'high ascii' character code, and these are just a few of the more common 'empties' encountered when dabbling in the black art (paradox - even the name is cause for concern!).

Of course, simply defining 'empty' may be more difficult than meets the obvious eye, as data migration should not assume consistency in the source(s) either. Depending on the age of the legacy it is quite possible (to me even PROBABLE) that different "emptiness" exist within the same field of these foregin substances, so one might need to do the inverse and simply set all fields which are not within a specific NUMERIC criteria to your desired value.

Of course, MY approach (being a programmer) would be to generate a function to check the various criteria, and use the function to 'calculate' the replacement value.

Then, finally, one should not usually ASS-U-ME that null is zero. Null, in strict database terms does NOT mean Zero, but it is a specific VALUE which mean the content is NOT KNOWN. The novice often mistakes the fact of unknown-ness for zero, but this is usually a mistake.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MichaelRed]

First, you need to define what is meant by empty.

It MAY be Null, then again it might be "", on the other hand it could be some 'high ascii' character code, and these are just a few of the more common 'empties' encountered when dabbling in the black art (paradox - even the name is cause for concern!).

Of course, simply defining 'empty' may be more difficult than meets the obvious eye, as data migration should not assume consistency in the source(s) either. Depending on the age of the legacy it is quite possible (to me even PROBABLE) that different "emptiness" exist within the same field of these foregin substances, so one might need to do the inverse and simply set all fields which are not within a specific NUMERIC criteria to your desired value.

Of course, MY approach (being a programmer) would be to generate a function to check the various criteria, and use the function to 'calculate' the replacement value.

Then, finally, one should not usually ASS-U-ME that null is zero. Null, in strict database terms does NOT mean Zero, but it is a specific VALUE which mean the content is NOT KNOWN. The novice often mistakes the fact of unknown-ness for zero, but this is usually a mistake.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[DIVINEDAR0956]

MichaelRed

Thank you for that enlighting speech. Sorry to say that I am a beginner and following your speech leaves me speechless. However please help.

I have Months 1 through 10 in a table of about 10,000 records. In those months are some "blank" fields that needs to have "0" input into them. I tried the following and put in the criteria of a query calling the function but no success. Please help me be successful, you don't know what kind of reward might be awaiting you.

Public Function ConvertNullToZero()
Dim stMonth47 As String
stTblName = "StoreHours1"
If IsNull(stTblName) Then
stTblName = 0
End If
End Function

I know I'm missing something. I want this to check each field and if it is blank, empty, null or whatever to input a "0".

Thank you and God Bless. Darlene Sippio
dsippio@comtechsystems.com

 

[GHolden]

Sorry for butting in...

If your field is numeric, and clearly isn't null, and valid input is between two values (say 1 to 9), you could try something like an update query with

Update to 0

Criteria - Not In (1,2,3,4,5,6,9,8,9)

ie. check what it isn't rather than what it is

There are two ways to write error-free programs; only the third one works.

 

[MichaelRed]

GHolden

Thanks ... for butting in ...

I can only realistically repeat the earlier 'speech'. 10 Months is another enigma, as is the concept of "Month 0", so I just get further away from the concept.

At best, I remain convinced that someone needs to understand the overall process better than I do.

Simply Assigning a value is not, in my opinion, a useful option. After all, having the Month be set to Zero in a range of 1 to 10 is not significantly different from just leaving it Null or empty. And, the issue remains as to WHY and HOW the 'missing' values got there to begin with. Repetitively replacing these 'missng' values with (Your CHOICE) is liken to bandaging the wound - it may cover the bruise, but it hardly prevents the next hit from occuring. But AGAIN & STILL, there being no 'background' to the generation of whatever the value may be, there is little or no point is arbitrairly changing it (them?) to some other value(s) with an equally obscure history?


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[DIVINEDAR0956]

MichaelRed

I beginning to wonder if you understand where I'm coming from here. The fields are headed with Month_1 through Month_10. In these fields there are no value but they are set to be a numeric field. The field is BLANK.

All I want to do is have a code or an easier way of inserting a zero in each and every BLANK field.

The table was brought in out of paradox and more columns where added to the table. The columns that were added, even if I put "0" as the default value when adding the fields, does not have a value in them. Please help.

Thank you. Are you a lecturer? You are very good and somewhat understandable. Darlene Sippio
dsippio@comtechsystems.com

 

[MichaelRed]

ummmmmmmmmmm ... mmmmmmmmmmmm ... mmmmmmm


obviously not so good, else I would be able to get the message. either to or from, but at least a message.

for me, it is not so much the doing which is difficult, which SEEMS like it could be simplistically accomplished.

just to 'get over it' A way to do it MAY be to follow GHoldens' suggestion:

Not In (1,2,3,4,5,6,9,8,9)

in the criteria ROW of each FIELD which you want to "adjust", with the Update To ROW as

0

In this approach, you must do each field SEPERATLY, as the criteria apply to all the fields collectively (as either AND or OR, depending on the row(s) for the criteria).


I also "re-wrote" you function:

Public Function basNull2Zero(strTblName As String)

    'Michael Red    1/8/2003    Tek-Tips thread700-440522
    'for   Darlene Sippiodsippio@ comtechsystems.com
    'a.k.a DIVINEDAR0956
    '? basNull2Zero("tblSalary")

    Dim dbs As DAO.Database     'Define a db object
    Dim rst As DAO.Recordset    'Define a recordset objets
    Dim Idx As Integer

    Set dbs = CurrentDb         'Instantiate the Database as the currnt db
    'Instantiate the recordset using the db and the given table name as dynamic
    Set rst = dbs.OpenRecordset(strTblName, dbOpenDynaset)

    While Not rst.EOF           'Loop through the WHOLE table one rec at a time
    Idx = 0
        While Idx <= rst.Fields.Count - 1      'Check each field for the name game
            If (Left(rst.Fields(Idx).Name, 5) = &quot;Month&quot;) Then   'Only do Montly ones
                'Here because SOME field is a &quot;Month&quot;

                'Check content is number
                If (Not IsNumeric(rst.Fields(Idx))) Then
                    'Here because it doesn't have a number in it!
                    rst.Edit                    'So edit it
                        rst.Fields(Idx) = 0
                    rst.Update
                End If
            End If
            Idx = Idx + 1
        Wend
        rst.MoveNext
    Wend

End Function

O.K. So NOW, I HAVE shown "how". Perhaps you would be willing to discuss the WHY part?

There really is NO pratical difference between the [empty ("&quot | Null | ANY COMMON VALUE] in the db. NULL, Empty or any common value may be found, or sorted or relaced throughought the (well formed) recordset. Null actually has the advantage of NOT being included in aggregate functions. Your "insistance" on replacing nothing with something is just not generally the way to maintain a database. Think about what you are doing. A rule I like to impose on operations is that unless there is a specific objective which will be acomplished and alter the data in a predictable (and NON-uniform) manner, the operation should NOT be carried out. You ARE violating this in at least one way, and possibly more.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[hwkranger]

MichaelRed is a guru here, but I'll offer my simple input.. simple b/c i had this problem, and in a pinch I didn't have time to find the root of the problem, but rather I did replace all the "empty" fields with values.

I got this same lecture.. sometimes you want the problem fixed 4 now, and later you can decide where the it's coming from. (not the most efficient, but who cares??)

my temp suggestion is as follows;

make a button that does the following:


Dim db As Database
Dim myrecordset As Recordset
Set db = CurrentDb()
Set myrecordset = db.OpenRecordset("your table name&quot
If myrecordset.BOF = True Then Exit Sub

With myrecordset
If .RecordCount Then
.MoveFirst
Do Until myrecordset.EOF
If !yourfield = "" Or IsNull(!yourfield) Then
.Edit
!yourfield = 0
.Update
End If
loop
end if
end with

it's not a solution, but rather a temporary stitch... I'm sure there's more efficient ways of doing this, but it works for now.

being a noob sucks.. i know first hand. I've only been out of college for a year, and I've only been doing access for a few years. Everyone has a better method..
Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[hwkranger]

welp, looks like michaelred posted right b4 i did.. as I said, everyone has a better solution. everytime see him or that NY dude post something, i learn something new..... Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[DIVINEDAR0956]

MichaelRed

You're the greatest.

The reason why is that it seems in access when you calculating all the fields into on, if that field is empty, null, or doesn't have a value in it, it doesn't give the right calculation in the end. For instance, the following:

MONTH_1 $100
MONTH_2 $100
MONTH_3 (empty no value)
MONTH_4 $50
MONTH_5 $500
MONTH_6 (empty no value)

From what I have been getting is a $0 Total. But if I put a zero in that field it seems to total just fine.

If there is something else that can help this situation please let me know.

Again Thank you and I give you a star. Darlene Sippio
dsippio@comtechsystems.com

 

[MichaelRed]

for the calc (rowset aggregate) I have a number of functions.

Public Function basRowSum(ParamArray varMyVals() As Variant) As Variant

    'Michael Red 4/9/2002
    'To return the AVERAGE or a series of values

    'Sample Usage:
    '? basAvgVal(1,12,3,5,78)
    ' 99

    '? basAvgVal(9, 1, 5, 3, 13.663)
    ' 31.663

    Print basRowSum(9, 1, 5, 3, Null, 13.663)
    ' 31.663

    Dim Idx As Integer
    Dim Jdx As Integer
    Dim MyAccum As Variant

    If (UBound(varMyVals) < 0) Then
        Exit Function
    End If

    For Idx = 0 To UBound(varMyVals())
        If (varMyVals(Idx) <> &quot;&quot; And Not IsNull(varMyVals(Idx))) Then
            MyAccum = MyAccum + varMyVals(Idx)
            Jdx = Jdx + 1
        End If
    Next Idx

    basRowSum = MyAccum

End Function

Which is a bit easier to use - AND would have kept from distorting the records in the db, to include invalid values. Not to mention some ammount of our collective bandwidth.

So, the 'moral of the story' is ... please fully explain what the problem is to begin w/








MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[DIVINEDAR0956]

MichaelRed

I'm sorry. I'm not so proud that I can admit that. Anyway I'm posting another one tomorrow, right now I'm going home. I am one of those people who like challenging other people at their expertise. You'll hear from me again.

I will give you your credits. You know what you're doing.

Nice talking to you and thank you again. Darlene Sippio
dsippio@comtechsystems.com

 

[jksmi]

Hi Darlene,

Have you tried to use either the NZ function in your calculation or in an update query?

e.g. Expr: nz([month_0],0)/nz([month_1],0)
- means you dont have to change any records
or

UPDATE tblNZTest SET tblNZTest.Month_0 = nz([Month_0],0), tblNZTest.Month_1 = nz([Month_1],0), tblNZTest.Month_2 = nz([Month_2],0), tblNZTest.Month_3 = nz([Month_3],0);
- not very good if your running from, say, a form...

Also, SAS stores unknown number values as a period, '.', maybe paradox does the same? Maybe converting to text and back again might help (shot in the dark as I've not tried it)... What number format does the table use? If its imported as DECIMAL and your using currency maybe you could change it to CURRENCY... If you can give as much info about what you are actually trying to achieve and currently have I'm sure I can come up with some more dodgy ideas that you could try.

See ya,

Jamie

 

[StateGov]

Write an Update Query to update the field to "0" when null.

 

[pdldavis]

Hi, for null fields you can attach something like this to the On Click event of a button on a form. I use something similar to date stamp blank fields.

DoCmd.RunSQL "UPDATE yourTable SET yourtable.yourfield = 0 WHERE ((yourtable.yourfield is null ));"

Hope that helps