Find Null Values in Table 1

[Bonediggler1]

Hello--

I have a db that imports a customer file, formats, and exports a new file for upload into a different system. I need to validate whether or not there are any null values in any of the fields after importing, as even one null value throws everything off.

Any ideas how to go about this?


Thank you!!

 

[dhookom]

Are all of the fields text or are there some numeric and/or date fields?

Duane
Hook'D on Access
MS Access MVP

 

[Bonediggler1]

All of the above...

 

[dhookom]

I expect you could write code that would loop through each field and generate a SQL statement that would count the number of null values in the field.

The question is, what do you want to do with the information? Store it in a table or display in a msgbox or what?

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

db that imports a customer file

What kind of file is it?
Could you show some small example of how it looks like (with some NULLs)?
If you do find a NULL, what do you want to do with it? Change to a default value? If so, what's the value?

Have fun.

---- Andy

 

[Bonediggler1]

It is a .csv file. It basically has Invoice and Line records like

Invoice, first name, last name, claim #, ssn...
Line, wholesale amount, retail amount...


If I find a null I need to stop the program and alert the sender that there is a null value in the file. But that isn't really the issue - I just need a way to find null values...

 

[dhookom]

Bonediggler1,
Again, "The question is, what do you want to do with the information? Store it in a table or display in a msgbox or what?"

How do you expect to be notified? Is it ok for a simple message box, "You have a Null", or do you need something more?

Duane
Hook'D on Access
MS Access MVP

 

[Bonediggler1]

Ok for now let's display it in a message box. "You have a null" is sufficient...

 

[dhookom]

You can use code like:

Public Function DoesTableHaveNull(strTable As String) As Boolean
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fd As DAO.Field
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strTable)
    DoesTableHaveNull = False
    Do Until rs.EOF
        For Each fd In rs.Fields
            If IsNull(fd) Then
                DoesTableHaveNull = True
                MsgBox "there is a null"
                Exit Do
            End If
        Next
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

So you have a csv file (comma delimited text file) that you use to transfer data into your database.

You do not have NULL values in your csv file, just empty fields, right?

Do you want to check if you have any empty fields in your csv file? Or do you want to know about any NULLs in your database after you use your csv file?


Have fun.

---- Andy

 

[Bonediggler1]

All-

Thanks for the help. We finally got it working. I had to use a modified version of dhookom's code to accomodate some other requirements, but it's all set now.

Thanks again!

 

[Andrzejek]

I have a db that imports a customer file [...]It is a .csv file

If you get this file from your customer and use it to populate your database (with some NULLs) - wouldn't be easier to validate your data in csv file BEFORE you put the data into your DB?

Something like:

Dim strTextLine As String
Dim intL As Integer
Dim i As Integer
Dim s

Open "C:\Temp\SomeFile.csv" For Input As #1
Do While Not EOF(1)
    Line Input #1, strTextLine
    intL = intL + 1
    s = Split(strTextLine, ",")
    For i = LBound(s) To UBound(s)
        If Len(Trim(s(i))) = 0 Then
            Call MsgBox("Info in position " & i & _
                " on line " & intL & " is missing", _
                vbInformation, "Position 0 based.")
        End If
    Next i
Loop
Close #1

Have fun.

---- Andy