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?

 

[alansidman]

You need to do an Update Query. Here is a tutorial on how to do an update query
Click here


Alan

 

[xenou]

More or less like:

UPDATE [MyTable] SET [GearID] = "RSTR8" WHERE [GearID] Is Null

You can write the sql directly in SQL View (not a bad way to learn SQL). The table name needs to be the actual name of the table, though - I used "MyTable" in absence of the actual name. Make a copy of the database or table first...!

 

[Kreszch68]

The best way to do this is using the 'nz' function.
Simply use nz('fieldname','value if null') in an update statement.

 

[Kreszch68]

The statement should look like:

UPDATE [your tablename] SET [GearID] = nz([GearID],"RSTR8");

 

[james_lankford]

the best way is actually xenou's way

the nz function only works in the access query builder and in a few DAO functions.

So if you're in another application (let's say an excel module) and you're using vba to code an ado query that connects to access, querys the database and brings back data to fill the spreadsheet, then the code will fail.
You'll get an "undefined function" error. NZ does not work with ADO or in other applications.

So if you're using vba to write an ado query your code will fail, whether you're in excel, or in a dynamic web page or even in access itself

and with this
UPDATE [your tablename] SET [GearID] = nz([GearID],"RSTR8");
you're updating every single row in the table whether its null or not.
If the value is null, then you update it to RSTR8.
If the value isn't null you update it anyway and over write the value that's already there with the exact same same value.
Very wasteful.

with xenou's way you only update the values that actually are null.
You don't even touch the rows that are null.

 

[xenou]

I admit I hadn't even thought of NZ() but since I do sometimes run my queries from Excel with ADO, I indeed avoid NZ() in my stored queries for the reasons given above.

ξ

 

[Kreszch68]

Well, this is not quite how it really works.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Nz(Value,[ValueIfNull]) is designed for VBA. <o></o>
When used in de query builder in Access you need to set the ValueIfNull value, in VBA this is optional. <o></o>
<o> </o>
The NZ function can be used within VBA code, but as it comes with the MS Access Object Library you need to set a reference to this library.<o></o>
<o> </o>
At first, it will evaluate if a value is null, if it is it returns a vbNullstring. Or it replaces the null value for the desired value. If the value is NOT null it does nothing. <o></o>
<o> </o>
When you use <?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 or write data Excel, you can still use NZ, depending on how you push or pull the data.<o></o>
If you want a demonstration, I’d be happy to publish some code to show how to do this.<o></o>
<o> </o>
The big advantage is that you don’t need a separate update statement to eliminate null values in a dataset.<o></o>

 

[xenou]

When you use ADO to retrieve or write data Excel, you can still use NZ, depending on how you push or pull the data.
If you want a demonstration, I’d be happy to publish some code to show how to do this.

A demonstration would be of interest to me. My experience is that Access queries with NZ() fail when run from Excel with ADO.

ξ

 

[james_lankford]

...If the value is NOT null it does nothing. <o></o>
<o> </o>...<o></o>

I don't think it does nothing if the value is not null.
I think it returns the tested variable.

If you look in the access help for the nz function, it says these two are equivalent

varResult = IIf(IsNull(varFreight), "No Freight Charge", varFreight)

varResult = Nz(varFreight, "No Freight Charge")

so when you do this
UPDATE [your tablename] SET [GearID] = nz([GearID],"RSTR8");
you're not doing nothing if GearID is not null, you're returning the non-null value
and then you're using that value in the update statement
so you're updating the value to itself

and I too would like to see come code, because when I run this in an excel module, the 2nd select statement fails with this message
"Undefined function 'nz' in expression."

'***************************************
Sub test_it()
    
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\jamesl\Documents\db1.mdb;Mode=Share Deny None"
    conn.Open
    
On Error Resume Next
    Set rs = conn.Execute("select column1 from table1", , adCmdText)
    If Err.Number <> 0 Then
        MsgBox Err.Description
        Err.Clear
    End If
    rs.Close
    
    Set rs = conn.Execute("select nz(column1, 'value if null') from table1")
    If Err.Number <> 0 Then
        MsgBox Err.Description
        Err.Clear
    End If
    rs.Close
    
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
    
End Sub
'***************************************