Convert all null data in table/recordset into non-nulls

[LLYTLE]

Currently, I am trying to write code to programmatically convert null values into non-nulls for various data types. In the present method, I have to name each field individually and recode using a sql statement.

Ideally, I would like to programmatically cycle through the recordset and convert all null dates to a standard value, all the null numeric values to another standard, and all the null strings to yet another standard.

How do I accomplish the null conversion via a vba method?

‘*PRESENT METHOD **********************************************
‘
 strSQL_qun_ABC_RecodeNULLs = "SELECT " _
            & "IIf(IsNull([lngFIELD1]),0,[lngFIELD1]) AS lngFIELD1r," _
            & "IIf(IsNull([lngFIELD2]),0,[lngFIELD2]) AS lngFIELD2r," _
            & "IIf(IsNull([strFIELD3]),' ',[strFIELD3]) AS strFIELD3r," _
            & "IIf(IsNull([strFIELD4]),' ',[strFIELD4]) AS strFIELD4r," _
            & "IIf(IsNull([strFIELD5]),' ',[strFIELD5]) AS strFIELD5r," _
            & "IIf(IsNull([shortFIELD6]),0,[shortFIELD6]) AS shortFIELD6r," _
            & "IIf(IsNull([strFIELD7]),' ',[strFIELD7]) AS strFIELD7r," _
            & "IIf(IsNull([strFIELD8]),' ',[strFIELD8]) AS strFIELD8r," _
            & "IIf(IsNull([strFIELD9]),' ',[strFIELD9]) AS strFIELD9r," _
            & "IIf(IsNull([dteFIELD10]),#12/12/12#,[dteFIELD10]) AS dteFIELD10r," _
            & "IIf(IsNull([dteFIELD11]),#12/12/12#,[dteFIELD11]) AS dteFIELD11r," _
            & "FROM qun_ABC;"

‘*IDEAL METHOD'*********************************
'Types "10" = String / "4" = Long / "7" = Double / "8" = Date/Time

Set db = CurrentDb

Set rst = db.OpenRecordset("tbl_qun_ABC", dbOpenTable)


If rst.RecordCount = 0 Then
GoTo MyExit
Else
rst.MoveFirst
End If

Do Until rst.EOF

If fld.Type = "10" And fld = Null Then fld = "  "
If fld.Type = "4" And fld = Null Then fld = 0
If fld.Type = "7" And fld = Null Then fld = 0
If fld.Type = "8" And fld = Null Then fld = #12/12/12#

End If
rst.MoveNext
Loop


'**********************************

 

[gol4]

I think you have something like this in mind

Private Sub Command0_Click()
Dim db As Database, rst As Recordset
Dim rstfld As Field
Set db = CurrentDb

Set rst = db.OpenRecordset("keybit", dbOpenTable)
If rst.RecordCount = 0 Then
rst.Close
Exit Sub
Else
rst.MoveFirst
End If

Do Until rst.EOF
For Each rstfld In rst.Fields
If rstfld.Value Is Null Then
Select Case rst.Type
Case "10": rstfld.Value = " "
Case "4": rstfld.Value() = 0
Case "7": rstfld.Value = 0
Case "8": rstfld.Value = #12/12/12#
End Select
End If
Next
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub
Code not tested but should give you the idea and get you there with some debugging

 

[gol4]

afterposting I noticed this typo error "Sorry"
Select Case rst.Type
should read
Select Case rstfld.Type

another think you might want to consider. If you have default values set in your table design view already then you could forget worrying about the type and just cycle thru the fields using

Do Until rst.EOF
For Each rstfld In rst.Fields
If rstfld.Value Is Null Then
rstfld.value = rsfld.defaultvalue
End If
Next
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

but I suppose if it was set you would not have this problem in the first place

 

[LLYTLE]

Your solution definitely places me on the right track. I have only a little experience with referencing objects. As the code stands, I have gotten an error message for the statement: "If rstfld.Value Is Null Then"

Error message: Error '424' Object required.

What piece of code am I missing?

 

[gol4]

Sorry threw that code up in a hurry as I was Leaving work for the day.

I tested this and it seems to work.

Private Sub Command0_Click()
Dim db As Database, rst As Recordset
Dim rstfld As Field
Set db = CurrentDb

Set rst = db.OpenRecordset("YourTBL", dbOpenTable)
If rst.RecordCount = 0 Then
rst.Close
Exit Sub
Else
rst.MoveFirst
End If

Do Until rst.EOF
For Each rstfld In rst.Fields
Debug.Print rstfld.Value
If IsNull(rstfld.Value) Then
rst.Edit
Select Case rstfld.Type
Case "10": rstfld.Value = " "
Case "4": rstfld.Value() = 0
Case "7": rstfld.Value = 0
Case "8": rstfld.Value = #12/12/2012#
End Select
rst.Update
End If
Next
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

End Sub

 

[MichaelRed]

You MAY want to be careful. NULL is a "value" in db terms. It clearly states "Do Not Know" the value of this field for this record. There are MANY implications of this statement. ONE example, If you do an aggregate query (Avg), the null fields are NOT included. If you set then all to 0, they are included - and the results WILL BE VERY different. Another, If you Group by Date all of the null values will appear together. If you "Substitute" a specific date they will appear within THAT date - and not be disguishable from other records with the same date.

Altogether, the scheme appears to be inappropiate.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[LLYTLE]

Well... some background:

The overall purpose of converting nulls in this dataset is because I need to pass all of the data into a function that does not accept null values. Although I have the function set up to accept variant values, the function returns an error if any null exists in the dataset.

Overall my goal is to compare two structurally similar data sets. I need to group/match/compare a dataset with another using more than 10 data pieces. The process I am trying to build attempts to compare records contained in one table with records contained in another table, ie. I am trying to programatically determine if there is a direct match of 10,000 records in fields 1-50 contained in Table X with the 10,000 sister records in fields 1-50 in Table Y.

The basic process of comparison is as follows:
1. Append source identifier field to Table X and Y. ie. strFieldSource = TableX for records contained in Table X, and strFieldSource = TableY for records contained in TableY.

2. Use a union query to combine recordsets: UNION Table X.* Table Y.*

3. Convert all nulls to non-nulls. To pass all variables an aggregate function.

4. Use a function to aggregate data based information on all Fields (Fields 1-50).

5. Identify fields where Source DOES not = Table X AND Table Y.

This, I feel, may not be the most efficient way of comparing data contained in two datasets.

I am searching for a more efficient way to aggregate/compare the data, so any feedback would be greatly appreciated.

Hopefully, this description of the overall objective provides clarification.

 

[MichaelRed]

I do not think there is ANY "good" soloution to compare 10K rows of anything, It is just going to brutal. Fortunately, Ms. Access has at least something to get you started. It is called the "UnMatched" query - AND it has it's OWN wizzard.

The wizzard is PROBABLY NOT up to speed for your requirements - BUT it is a good little teaching tool. Try it on just a few fields, inspect the query it generates, and CAREFULLY extend it to the limit of your (mutual) endurance and needs. MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over