Microsoft Access Fill All Blanks in Table

[RabbieBurns]

IVe got a MS Access database, and a table with a lot of blanks. Im trying to fill all the blanks in all columns / rows with just NA but cant seem to figure out how to write the sql query. Any hints please?

[powdarrmonkey]

An update query. Set the columns you want to fill to the value you want them to have, and a condition to only do it on empty if you want that.

[RabbieBurns]

SELECT * FROM Asset < is that needed?
UPDATE Asset SET Manufacturer='NA' WHERE Manufactur IS NULL, OR SET Model='NA' WHERE Model IS NULL;

 

is that right?

[CESIL]

Try this

 

UPDATE test SET test.field2 = IIf(IsNull([field2]),"NA",[field2]), test.field3 = IIf(IsNull([field3]),"NA",[field3]);

 

Backup before you try this of course

[MattMitchell]

Sub FillInBlanks(sTable as String, sNullVal as String)
Dim td as TableDef, fl as Field, sq as String

' Assumes that the table "sTable" exists, and will cause an error otherwise

Set td=CurrentDb.TableDefs(sTable)
sq = "UPDATE [" & sTable & "] SET "

For Each fl In td.Fields
  ' If you test for field type, you could limit this to text fields only...  
  sq = sq & vbCrLf & "[" & fl.Name & "] = Nz([" & fl.Name & "], """ & sNullVal & """), "
  Next fl

sq = Left(sq, Len(sq)-2) & ";"
' Let you see what's come out of it...
Debug.Print sq

CurrentDb.Execute sq

End Sub

 

That'll work, as long as all your fields are text ones...

[RabbieBurns]

Sub FillInBlanks(sTable as String, sNullVal as String)
Dim td as TableDef, fl as Field, sq as String

' Assumes that the table "sTable" exists, and will cause an error otherwise

Set td=CurrentDb.TableDefs(sTable)
sq = "UPDATE [" & sTable & "] SET "

For Each fl In td.Fields
  ' If you test for field type, you could limit this to text fields only...  
  sq = sq & vbCrLf & "[" & fl.Name & "] = Nz([" & fl.Name & "], """ & sNullVal & """), "
  Next fl

sq = Left(sq, Len(sq)-2) & ";"
' Let you see what's come out of it...
Debug.Print sq

CurrentDb.Execute sq

End Sub

 

That'll work, as long as all your fields are text ones...

 

How would I run that?

[apeo]

How would I run that?

 

I wouldnt run this code, it doesnt look right. I may be wrong but i think this code will try to fill in a specified bit of text to every field regardless of what it has in it.

 

I would just create and update query and run that (as advised).

[CESIL]

I have used queries like the one I posted above before and the method works.

 

It is also very easy to enter into a query grid.

 

http://www.arkitsolutions.co.uk/access.jpg

[apeo]

Actually looking at the code more closely, my statement isnt right. It will only update fields that are blank but i think i will run into problems with a table that has a primekey that uses an autonumber. Edited March 17, 2010 by apeo

[powdarrmonkey]

WTF? It's so simple:Start a new update query in visual view, drop in the columns you want to consider, set the criteria on each one to "Is Null" and the value you want it to be in the Update To, then run it.

[CESIL]

WTF? It's so simple:Start a new update query in visual view, drop in the columns you want to consider, set the criteria on each one to "Is Null" and the value you want it to be in the Update To, then run it.

 

Actually this won't work as the resulting query updates either all fields whether blank or not or only updates where all the columns in a row are blank depending on how the criteria are entered.

[MattMitchell]

Actually looking at the code more closely, my statement isnt right. It will only update fields that are blank but i think i will run into problems with a table that has a primekey that uses an autonumber.

 

SET Field1 = Nz(Field1, ValueIfNull)

 

will set Field1 to ValueIfNull if it's blank.

 

The original brief was "set all the blank values to NA" which is what this will do - setting a numeric key to "NA" wouldn't work anyway, and last time I checked, you may find it difficult to have null values in a primary key in the first place.

 

Yes, a bit of error-checking on top would be nice, but hey, it's a step in the right direction. Plus, it actually works...

 

Also,

Field2=IIf(IsNull(Field2), "NA", Field2)

 

is functionally equivalent to

Field2=Nz(Field2, "NA")

 

but never mind.

[powdarrmonkey]

Actually this won't work as the resulting query updates either all fields whether blank or not or only updates where all the columns in a row are blank depending on how the criteria are entered.

 

So do it a column at a time. Even doing 100 column table by hand is faster than the two days you've collectively spent trying to come up with the Worlds Most Complex SQL Expression™.

[RabbieBurns]

So do it a column at a time. Even doing 100 column table by hand is faster than the two days you've collectively spent trying to come up with the Worlds Most Complex SQL Expression™.

 

@CESIL: Cheers for pointing me in the right direction with your first post..

 

@all: I guess i should have posted later that day that I did it that exact way with one simple line of SQL in SQL view:

 

UPDATE Asset SET Asset.InvoiceNumber = "NA" WHERE Asset.InvoiceNumber Is Null;

 

and then just modified accordingly for the other columns

[apeo]

SET Field1 = Nz(Field1, ValueIfNull)

 

will set Field1 to ValueIfNull if it's blank.

 

The original brief was "set all the blank values to NA" which is what this will do - setting a numeric key to "NA" wouldn't work anyway, and last time I checked, you may find it difficult to have null values in a primary key in the first place.

 

Yes, a bit of error-checking on top would be nice, but hey, it's a step in the right direction. Plus, it actually works...

 

Also,

Field2=IIf(IsNull(Field2), "NA", Field2)

 

is functionally equivalent to

Field2=Nz(Field2, "NA")

 

but never mind.

 

Yeah i know what Nz does, i initially thought that any field with with a value would have been overwriten with a null value but i realised later that wouldnt happen.. ero my correction.

 

As to my statement about primekey with autonumber, as this field will always have a value in it, the code should never try to update the field regardless of it being a numeric field but because its an autonumber it causes problems (even though its not updating the field i.e. "primekeyid = " is what the code will show).

Edited March 18, 2010 by apeo

[CESIL]

So do it a column at a time. Even doing 100 column table by hand is faster than the two days you've collectively spent trying to come up with the Worlds Most Complex SQL Expression™.

 

Except that I didn't take two days to solve this and your initial response was incorrect!

 

[EDIT]Yes I know I will sound very grumpy but that's the mood I am in today[/EDIT]

[MattMitchell]

Yeah i know what Nz does, i initially thought that any field with with a value would have been overwriten with a null value but i realised later that wouldnt happen.. ero my correction.

 

As to my statement about primekey with autonumber, as this field will always have a value in it, the code should never try to update the field regardless of it being a numeric field but because its an autonumber it causes problems (even though its not updating the field i.e. "primekeyid = " is what the code will show).

 

Fair point on the autonumber bit - I tend to avoid using these as they're not very relational and do nasty things when you start moving them around...

 

To be honest, I'd have just run a series of

 

UPDATE table SET field='Default' WHERE field IS NULL;

 

queries, but here's the equivalent thing in VBA, also picking up the default field value from the table properties where it's available, and confirming each column query before running:

 

Sub FixEmptyFields()
Dim td As TableDef, fl As Field
Dim sq As String, bUseDefault As Boolean

bUseDefault = True ' Setting to false will ignore default value in field properties

For Each td In CurrentDb.TableDefs
   If td.Name = "PUT_TABLE_NAME_HERE" Then Exit For
   Next
For Each fl In td.Fields
   If fl.Type = dbText Then
       sq = "UPDATE [" & td.Name & "] SET [" & fl.Name & "]="
       If fl.DefaultValue <> "" And bUseDefault Then
           sq = sq & fl.DefaultValue
           Else
           sq = sq & "'" & "PUT_DEFAULT_VALUE_HERE" & "'"
           End If
       sq = sq & " WHERE [" & fl.Name & "] IS NULL;"
       If MsgBox(sq, vbYesNo) = vbYes Then CurrentDb.Execute (sq)
       End If
   Next
End Sub

[apeo]

Fair point on the autonumber bit - I tend to avoid using these as they're not very relational and do nasty things when you start moving them around...

 

To be honest, I'd have just run a series of

 

UPDATE table SET field='Default' WHERE field IS NULL;

 

queries, but here's the equivalent thing in VBA, also picking up the default field value from the table properties where it's available, and confirming each column query before running:

 

Sub FixEmptyFields()
Dim td As TableDef, fl As Field
Dim sq As String, bUseDefault As Boolean

bUseDefault = True ' Setting to false will ignore default value in field properties

For Each td In CurrentDb.TableDefs
   If td.Name = "PUT_TABLE_NAME_HERE" Then Exit For
   Next
For Each fl In td.Fields
   If fl.Type = dbText Then
       sq = "UPDATE [" & td.Name & "] SET [" & fl.Name & "]="
       If fl.DefaultValue <> "" And bUseDefault Then
           sq = sq & fl.DefaultValue
           Else
           sq = sq & "'" & "PUT_DEFAULT_VALUE_HERE" & "'"
           End If
       sq = sq & " WHERE [" & fl.Name & "] IS NULL;"
       If MsgBox(sq, vbYesNo) = vbYes Then CurrentDb.Execute (sq)
       End If
   Next
End Sub

 

True autonumbers can be annoying... guess what students here love using.

 

To be fair i would just use an update query too but your VBA solution is pretty useful.