Results 1 to 14 of 14
  1. #1
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107

    Post trim empty spaces in table

    I am trying to trim all the spaces in a table but I am getting an error.Wonder what I am doing wrong here.

    Code:
    Private Sub Command121_Click()
    Dim db As DAO.Database
    Dim strSQL As String
    Set db = CurrentDb
    strSQL = " update tbl_Tracking " & _
    "set tbl_Tracking = LTrim(RTrim([tbl_Tracking]))"
    CurrentDb.Execute strSQL
    
    Debug.Print strSQL
    End Sub


  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you don't have a table name or field name, not sure which

    also you can just use trim

    set fldName= Trim([fldName])

    if you are talking about removing spaces from field and table names, that is not the way to do it

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    depends. You don't say what the result is, and the title of your post doesn't really match what the code suggests. You are trying to remove all spaces in a string, as in
    all spaces in a string
    to
    allspacesinastring ?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Try replace([MyField]," ","") if the field is a string

  5. #5
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    what I am trying to do is to remove all the leading and lagging spaces ( " AB C " to
    "AB C"). in every single cell in the table named tbl_Tracking
    I could use update query (example for one column)
    [UPDATE tbl_Tracking SET tbl_Tracking.P_Iso_Dwg = LTrim(RTrim([P_Iso_Dwg]));] but there is so many queries in this database and I don't want to create a new one.
    Instead of query I was hopping I could write VBA code which would do the same thing.
    The error I am getting is
    "Run-time error '3061':
    To few parameters. Expected 1.
    Thanks for your help

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can write vba to do this. Here is some air code


    Code:
    dim tdf as tabledef
    dim fld as field
    
    for each tdf in currentdb.tabledefs
        for each fld in tdf.fields
            if fld.type=actext then 'you'll need to check this
                currentdb.execute("UPDATE " & tdf.name & " SET " & fld.name & " = Trim(" & fld.name & ")")
            end if
        next fld
    next tdf
    it is air code so you may need to correct the syntax here and there

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    the vb side of Access cannot always resolve references to form controls when you try to execute queries - depends on how you're doing it.
    Often, the easiest code fix is to assign the form control value to a variable, then use the variable in the vba sql. However, you can also generate that error message by not properly concatenating the form control reference in the sql on the vb side of things. Such as (assuming form field is a number)
    "...WHERE [someField] = Me.someControl..." instead of
    "...WHERE [someField] = " & Me.someControl & "...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    Ajax. I am not that advanced in vba, the way I understand this code is that it will trim all the text values in every table (which is what I hope for)
    I have changed acText to dbText and was able to pass this line code but was stuck on the next line with syntax error. I have looked at few examples on the web but was not able to find the error

    [CODEPrivate Sub Command121_Click()Dim tdf As TableDef
    Dim fld As Field


    For Each tdf In CurrentDb.TableDefs
    For Each fld In tdf.Fields
    If fld.Type = dbText Then 'you'll need to check this
    CurrentDb.Execute ("UPDATE " & tdf.Name & " SET " & fld.Name & " = RTrim(LTrim(" & fld.Name & "))")
    End If
    Next fld
    Next tdf
    End Sub][/CODE]

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That code will include system tables, which should not be played with in this case. On my phone so can't elaborate just now.

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    That code will include system tables
    good point

    Code:
    dim tdf as tabledef
    dim fld as field
    dim sqlstr as string
    
    for each tdf in currentdb.tabledefs
        if not (tdf.name like "msys*") then
             for each fld in tdf.fields
                  if fld.type=dbtext then 'you'll need to check this
                      sqlstr="UPDATE " & tdf.name & " SET " & fld.name & " = Trim(" & fld.name & ")"
                      debug.print sqlstr
                      
                      currentdb.execute(sqlstr)
                  end if
            next fld
        end if
    next tdf
    but was stuck on the next line with syntax error
    I've added a few lines so you can check the syntax of the code - the debug print will put the generated string in the immediate window. Also before running the code, ensure you have 'option explicit' at the top of the module and compile the code. It may be that your table or field names have spaces or none alpha numeric characters which is causing the problem in which case you need to surround them with square brackets

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Curious about one thing. In all my testing, I've never been able to store a trailing space in an Access database text field - including memo/long text. Not by direct table data entry; not by form. Is this something that can only come from other db types such as sql server? In Access, is it possible if a certain field option is set?

  12. #12
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    Micron, thanks for looking into it, the table created I have problem with was a query in another database on company server, almost every single colum has lagging spaces some up to 12 spaces.

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I certainly have seen spaces as suffix or prefix, but like HS it has been imported from somewhere else - try importing a cell from excel which has been populated with some text and trailing spaces. Access will remove trailing spaces when data is entered manually, but not if at the beginning (presumably to allow a user to indent a line with spaces). Not sure if there is a setting to allow them to keep trailing spaces - by why would you want to?

  14. #14
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    Thanks guys for your help, would never guess that the field names would be the problem, it was not only the spacing there but reserved words (From, To,Type) and characters (&, -,/) which coused syntax error. Now the code works without any problems.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 04-06-2018, 01:46 PM
  2. Removing Unwanted Spaces From Table Fields, Access 2007
    By rd.prasanna in forum Programming
    Replies: 3
    Last Post: 11-11-2013, 05:23 PM
  3. Replies: 1
    Last Post: 04-16-2013, 07:57 PM
  4. Look Up table with string that contain spaces
    By Leonidsg in forum Database Design
    Replies: 1
    Last Post: 04-03-2013, 06:53 PM
  5. Replies: 3
    Last Post: 05-19-2010, 10:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums