Excel VBA how to replace Blanks with something using ADO

[DougP]

the following code does nothing
also tried this ="" still Nada

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Dim SQLUpdateString As String
    
        'get workbook name to open ADO connection
    FileName = Application.ActiveWorkbook.Fullname

    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & FileName & ";" & _
        "Extended Properties=Excel 8.0;"
        .CursorLocation = adUseClient
        .Open
    End With
      
        ' Change all of the blanks in the Application ID column to UNK
        SQLUpdateString = "UPDATE [Application Loading Combined$] SET [Application ID] " & _
                    " = 'UNK' WHERE [highlight #FCAF3E]IsEmpty([Application ID])"[/highlight]
                    
        ' open connection to the Excel workbook and get data from String above
        rs.Open SQLUpdateString, cn
    
    Set rs = Nothing
    Set cn = Nothing

DougP

 

[Andrzejek]

What do you have in Application ID now? Is it NULL?

If so, [tt]
SQLUpdateString = "UPDATE [Application Loading Combined$] " & _
" SET [Application ID] = 'UNK' " & _
" WHERE [Application ID][blue] IS NULL[/blue]"
[/tt]

Have fun.

---- Andy

 

[PHV]

Or perhaps this:
[tt]WHERE Trim([Application ID] & '')=''[/tt]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886