Query expression

[clarkw]

I am not a savvy Access user but think I have a basic operation to perform. I want to insert a value where a field is currently null. So an IF-Then statement would read like:

If [GearID] = Is Null then "RSTR8"

How do I build a query to make this an easy operation for filling in a value for several thousand records? Do I use an Update query?

 

[Kreszch68]

I promised to explain how you can apply your VBA functions, or native VBA functions, when you write data to a worksheet, using <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on"><st1lace w:st="on">ADO</st1lace></st1:City> to retrieve data from a database.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
First I have to admit that at the NZ function is not as efficient as I assumed. However, the method I describe makes it easy to use your own functions to manipulate data. <o></o>
And the method is highly efficient. It’s tested with over 10 million records and still returns the values within a few seconds. Of course you can’t write 10 million records to an Excel sheet, but it’s good to know this method will not frustrate the performance.<o></o>
<o></o>
Suppose we have a table like:<o></o>
<o></o>
PRODId<o></o>
PRODName<o></o>
PRODDescription<o></o>
PRODPrice<o></o>
PRODCode<o></o>
<o></o>
The SQL statement can be any valid SQL against <st1:City w:st="on"><st1lace w:st="on">ADO</st1lace></st1:City>, for now we use:<o></o>

Public Function SQL_Products() As String
SQL_Products = "SELECT Product.PRODId, Product.PRODName, Product.PRODDescription, Product.PRODPrice, Product.PRODCode " _
             & "FROM Product"
End Function

Say we have to do the following before the data is written to the sheet Product:<o></o>
<o></o>
- if PRODDescription is a Null value, write “Description missing “ & PRODCode.value<o></o>
- add 10% to the PRODPrice.value

Two simple functions to demonstrate

Public Function fnAdd10pct(ByVal dPrice As Double) As Double
    fnAdd10pct = (dPrice / 100) * 110
End Function
 
Public Function fnMyNz(ByVal vVal As Variant) As Variant
If IsNull(vVal) Then
    fnMyNz = "Description missing"
Else
    fnMyNz = vVal
End If
End Function

Public Sub WriteDataToSheet()
Dim oCn As New ADODB.Connection
Dim oRs As New ADODB.Recordset
Dim ws As Worksheet
Dim xlApp As Excel.Application
Dim arrRecords() As Variant 'Create an array to load the record set
Dim lRecNo As Long
    ' Open connection to the database
    oCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sDBFile & ";"
 
    With oRs
            .Open SQL_Products, oCn, adOpenStatic, adLockReadOnly
            arrRecords() = .GetRows()
            .Close
            Set oRs = Nothing
    End With
 
oCn.Close
Set oCn = Nothing
' Note that the connection is already closed before the process starts
            'do manipulations
            For lRecNo = 0 To UBound(arrRecords, 2)
            '            MyNz function on field PRODescription, this is field(2) in ADO and add PRODCode (field(4)
                         arrRecords(2, lRecNo) = [B]fnMyNz[/B](arrRecords(2, lRecNo)) & arrRecords(4, lRecNo)
            '            add 10 pct to field PRODPrice, this is field(3) in ADO
                         arrRecords(3, lRecNo) = [B]fnAdd10pct[/B](arrRecords(3, lRecNo))
            Next lRecNo
'write to sheet
Set ws = ActiveWorkbook.Sheets("Products")
Set xlApp = ThisWorkbook.Application
    With ws
            'Resize range, remember that the array is zero based, so add 1 to max
            'Transpose array
            .Cells(1, 1).Resize(UBound(arrRecords, 2) + 1, UBound(arrRecords, 1) + 1).Value = _
             xlApp.WorksheetFunction.Transpose(arrRecords)
    End With
Set ws = Nothing
Set xlApp = Nothing
Erase arrRecords()
End Sub

 

[xenou]

That's a good demonstration. Thanks for the example. For a "mere" substitute for NZ() across an ADO connection I've also found this works (with Access):

IIF(IsNull([FieldName]),"",[FieldName])


Though take me with a grain of salt - it's been some time since I've tried it.