Results 1 to 3 of 3

Thread: access 2010

  1. #1
    Join Date
    Aug 2005
    Posts
    16

    Unanswered: access 2010

    some guidance please on these lines

    UPDATE tableName SET (columns to NULL) WHERE fieldnames LIKE '*Ledger#'

    The number of columns that need to be set to null exceed the number permitted
    so if there were a way to iterate/loop through the columns to find all
    those starting with the name Ledger

    Appreciate any help


    Regards
    Peter

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    limit the expression by some means


    use say the first letter of a string/text column, use a numeric range

    eg
    UPDATE tableName SET (columns to NULL)
    WHERE fieldnames LIKE '*Ledger#'
    and mynumericcolumn <15000


    then do ta series of queries untill you have fixed the problem, and make certain you have fixed the initial design problem that causes the problem in the first place (well fix the problem, then fix the data to be honest
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    You could run an app to clear each col. 1 by 1

    Code:
    Public Sub SetAllCols2Null(ByVal pvTbl)
    Dim rst 'As Recordset
    Dim fld  'As Field
    Dim sSql As String
    On Error Resume Next
    'HrGlass
    Set rst = CurrentDb.OpenRecordset("select * from " & pvTbl)
        For Each fld In rst.Fields
           sSql = "UPDATE " & pvTbl & " SET " & fld.Name & " = Null"
           DoCmd.RunSQL sSql
        Next
    rst.Close
    Set rst = Nothing
    'HrGlass False
    DoCmd.OpenTable pvTbl
    End Sub

Posting Permissions

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