+ Post New Thread
Page 2 of 2 FirstFirst 12
Results 16 to 18 of 18
Office Software Thread, Microsoft Access Fill All Blanks in Table in Technical; Originally Posted by powdarrmonkey So do it a column at a time. Even doing 100 column table by hand is ...
  1. #16

    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
    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]

  2. #17

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    254
    Thank Post
    23
    Thanked 37 Times in 28 Posts
    Rep Power
    20
    Quote Originally Posted by apeo View Post
    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:

    Code:
    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

  3. #18
    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
    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:

    Code:
    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.

SHARE:
+ Post New Thread
Page 2 of 2 FirstFirst 12

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
  •