+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
Office Software Thread, Microsoft Access Fill All Blanks in Table in Technical; IVe got a MS Access database, and a table with a lot of blanks. Im trying to fill all the ...
  1. #1

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,521
    Thank Post
    1,333
    Thanked 469 Times in 306 Posts
    Blog Entries
    6
    Rep Power
    199

    Microsoft Access Fill All Blanks in Table

    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?

  2. #2

    powdarrmonkey's Avatar
    Join Date
    Feb 2008
    Location
    Alcester, Warwickshire
    Posts
    4,859
    Thank Post
    412
    Thanked 777 Times in 650 Posts
    Rep Power
    182
    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.

  3. Thanks to powdarrmonkey from:

    RabbieBurns (18th March 2010)

  4. #3

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,521
    Thank Post
    1,333
    Thanked 469 Times in 306 Posts
    Blog Entries
    6
    Rep Power
    199
    Code:
    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?

  5. #4

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    Try this

    Code:
    UPDATE test SET test.field2 = IIf(IsNull([field2]),"NA",[field2]), test.field3 = IIf(IsNull([field3]),"NA",[field3]);
    Backup before you try this of course

  6. Thanks to CESIL from:

    RabbieBurns (18th March 2010)

  7. #5

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    247
    Thank Post
    23
    Thanked 37 Times in 28 Posts
    Rep Power
    20
    Code:
    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...

  8. #6

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,521
    Thank Post
    1,333
    Thanked 469 Times in 306 Posts
    Blog Entries
    6
    Rep Power
    199
    Quote Originally Posted by MattMitchell View Post
    Code:
    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?

  9. #7
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    42
    Quote Originally Posted by RabbieBurns View Post
    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).

  10. Thanks to apeo from:

    RabbieBurns (18th March 2010)

  11. #8

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    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.


  12. Thanks to CESIL from:

    RabbieBurns (18th March 2010)

  13. #9
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    42
    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.
    Last edited by apeo; 17th March 2010 at 10:11 AM.

  14. #10

    powdarrmonkey's Avatar
    Join Date
    Feb 2008
    Location
    Alcester, Warwickshire
    Posts
    4,859
    Thank Post
    412
    Thanked 777 Times in 650 Posts
    Rep Power
    182
    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.

  15. Thanks to powdarrmonkey from:

    RabbieBurns (18th March 2010)

  16. #11

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    Quote Originally Posted by powdarrmonkey View Post
    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.

  17. #12

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    247
    Thank Post
    23
    Thanked 37 Times in 28 Posts
    Rep Power
    20
    Quote Originally Posted by apeo View Post
    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,
    Code:
    Field2=IIf(IsNull(Field2), "NA", Field2)
    is functionally equivalent to
    Code:
    Field2=Nz(Field2, "NA")
    but never mind.

  18. #13

    powdarrmonkey's Avatar
    Join Date
    Feb 2008
    Location
    Alcester, Warwickshire
    Posts
    4,859
    Thank Post
    412
    Thanked 777 Times in 650 Posts
    Rep Power
    182
    Quote Originally Posted by CESIL View Post
    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™.

  19. Thanks to powdarrmonkey from:

    RabbieBurns (18th March 2010)

  20. #14

    RabbieBurns's Avatar
    Join Date
    Apr 2008
    Location
    Sydney
    Posts
    5,521
    Thank Post
    1,333
    Thanked 469 Times in 306 Posts
    Blog Entries
    6
    Rep Power
    199
    Quote Originally Posted by powdarrmonkey View Post
    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:

    Code:
    UPDATE Asset SET Asset.InvoiceNumber = "NA" WHERE Asset.InvoiceNumber Is Null;
    and then just modified accordingly for the other columns

  21. #15
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    42
    Quote Originally Posted by MattMitchell View Post
    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,
    Code:
    Field2=IIf(IsNull(Field2), "NA", Field2)
    is functionally equivalent to
    Code:
    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).
    Last edited by apeo; 18th March 2010 at 08:49 AM.

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. HP DX2250 Bezel Blanks
    By stjtech in forum Hardware
    Replies: 1
    Last Post: 15th July 2009, 02:09 PM
  2. Replies: 0
    Last Post: 25th June 2009, 03:45 PM
  3. Fill in the blanks quiz
    By TriggerHappyUK in forum General Chat
    Replies: 66
    Last Post: 18th May 2009, 12:41 PM
  4. Microsoft Access Help! Date Field
    By Pyroman in forum How do you do....it?
    Replies: 1
    Last Post: 2nd May 2008, 03:19 PM
  5. Microsoft Access XP
    By rusty155 in forum Windows
    Replies: 3
    Last Post: 8th February 2007, 08:15 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •