trim space in front and behind

[chubbychub]

I have the below code that was given to me, how can i trim the leading space when users put in front of name when running macro to add a new record? (name is on "C1" cell in excel)
I have tried to use 'trim' in various parts, but no luck please help.

Private Sub CommandButton3_Click()
Dim check(2) As Variant
Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    
    cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cnn.ConnectionString = "Data Source=Z:\desktop\Database.accdb"
    cnn.Open

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "datatable", cnn, adOpenForwardOnly, adLockOptimistic, adCmdTable
''''''''''''''''''''''''''''''''''''''''''''''''''''' Check if customer already exist then do not add
       check(0) = ActiveSheet.Range("Customer")
       If IsNull(check(0)) Then
            Exit Sub
       Else
         check(1) = 0
              Do
            
            If rst![Name] = check(0) Then
                            check(1) = 1
            End If
                rst.MoveNext
            Loop Until rst.EOF
            
     End If
'''''''''''''''''''''''''''''''''''''''''''''''''''' If record do not exist then add
    If check(1) = 0 Then
    rst.AddNew   
        rst![Name] = Sheets("Quote").Range("C1")
        rst![Address] = Sheets("Quote").Range("C2")
        rst![phone] = Sheets("Quote").Range("C3")
   rst.Update
    
    End If
    rst.Close
    cnn.Close
End Sub

[moke123]

If trim didn't do it, are you sure it's a space? A non-printable character?
You don't show what you've tried so its hard to guess.

You can check what's in the string and look it up in an ascii table with something like this:

Sub PrintChars(strIN As String)

    Dim i As Integer
 
    For i = 1 To Len(strIN)
        Debug.Print Mid(strIN, i, 1), Asc(Mid(strIN, i, 1))
    Next
 
End Sub

Sub test()
PrintChars "chubby chub" 
End Sub

Output:
c              99 
h              104 
u              117 
b              98 
b              98 
y              121 
               32 
c              99 
h              104 
u              117 
b              98

[chubbychub]

i tried the red lines

Private Sub CommandButton3_Click()
Dim check(2) As Variant
Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    
    cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cnn.ConnectionString = "Data Source=Z:\desktop\Database.accdb"
    cnn.Open

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "datatable", cnn, adOpenForwardOnly, adLockOptimistic, adCmdTable
''''''''''''''''''''''''''''''''''''''''''''''''''''' Check if customer already exist then do not add
       check(0) = ActiveSheet.Range("Customer")
       If IsNull(check(0)) Then
            Exit Sub
       Else
         check(1) = 0
              Do
            
            If rst![Name] = check(0) Then  
                            check(1) = 1
            End If
                rst.MoveNext
            Loop Until rst.EOF
            
     End If
'''''''''''''''''''''''''''''''''''''''''''''''''''' If record do not exist then add
    If check(1) = 0 Then
    rst.AddNew   
        rst![Name] = Sheets("Quote").Range("C1")  ''i tried here adding this rst![Name] = Sheets("Quote"). Trim(Range("C1")) and also this rst![Name] = Sheets("Quote").Range(trim("C1"))
        rst![Address] = Sheets("Quote").Range("C2")
        rst![phone] = Sheets("Quote").Range("C3")
   rst.Update
    
    End If
    rst.Close
    cnn.Close
End Sub

[moke123]

how about

rst![Name] = trim(Sheets("Quote").Range("C1"))

BTW, Name is a reserved word and a poor choice for a field name.

[Welshgasman]

Trim that range into a variable and adding that to the field.
You cannot just make stuff up. That is not a valid range statement. The trim would have to be around the whole range statement.

[chubbychub]

thanks for the replies, i added that code into the, but when i go to access database, i still see space and this spaces (at the beginning and the end) which didnt trim

Private Sub CommandButton3_Click()
Dim check(2) As Variant
Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    
    cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
    cnn.ConnectionString = "Data Source=Z:\desktop\Database.accdb"
    cnn.Open

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "datatable", cnn, adOpenForwardOnly, adLockOptimistic, adCmdTable
''''''''''''''''''''''''''''''''''''''''''''''''''''' Check if customer already exist then do not add
       check(0) = ActiveSheet.Range("Customer")
       If IsNull(check(0)) Then
            Exit Sub
       Else
         check(1) = 0
              Do
            
            If rst![Name] = check(0) Then  
                            check(1) = 1
            End If
                rst.MoveNext
            Loop Until rst.EOF
            
     End If
'''''''''''''''''''''''''''''''''''''''''''''''''''' If record do not exist then add
    If check(1) = 0 Then
    rst.AddNew   
        rst![Name] = trim(Sheets("Quote").Range("C1"))  ''didnt work
        rst![phone] = Sheets("Quote").Range("C3")
   rst.Update
    
    End If
    rst.Close
    cnn.Close
End Sub

[Welshgasman]

Firstly the Trim statement is not being Proper cased?

Start learning to debug your code.

Sub TestTrim()
Dim strCell As String


strCell = "B4"
Debug.Print Len(Sheets("SF66OEK").Range(strCell))
Debug.Print Sheets("SF66OEK").Range(strCell)
Debug.Print Len(Trim(Sheets("SF66OEK").Range(strCell)))


End Sub

That produced

 24 
Mrs Bennett,
Llys y Coed
 24 
 31 
       Mrs Bennett,
Llys y Coed
 24

I ran that code once, then inserted spaces at the start of the cell as you can see the length increased from 24 to 31.

If that does not give you a clue, debug.print the asc() of every character in that cell.

[chubbychub]

i tried this but didnt work neither. I know its a a couple of spaces in front of it as i put it there.

when i run
debug.Print Len(Trim(Sheets("Quote").Range("C1")))
output:
17

which is correct (not counting the spaces in front), however when i open 'Database.accdb' (where this macro stores the information)
i still see spaces in the cell column where its saving. not sure if its the right place to trim, or why its saving with spaces when adding a new record.

rst![Name] = Len(Trim(Sheets("Quote").Range("C1")))

[Welshgasman]

Upload the DB and Workbook in a zip file.

I very much doubt you want the length of the cell in the field Name.

[moke123]

IIRC, you may have a Non-breaking space which is different from a regular space. Trim doesn't work on the non-breaking spaces.
Google Non-breaking space and see what comes up.

you could try replacing it with an empty string. Chr(240) is the ascii code for a non breaking space.

Replace(Sheets("Quote").Range("C1"), Chr(240), "")

[Welshgasman]

IIRC, you may have a Non-breaking space which is different from a regular space. Trim doesn't work on the non-breaking spaces.
Google Non-breaking space and see what comes up.

you could try replacing it with an empty string. Chr(240) is the ascii code for a non breaking space.

Replace(Sheets("Quote").Range("C1"), Chr(240), "")

O/P said they deliberately put spaces in front as I did in my test.
Perhaps worth debug.print every character ASC() number?

[chubbychub]

i tried

rst![Name] = Replace(Sheets("Quote").Range("C1"), Chr(240), "")

but no luck.
I shall point out that the macro is ran from excel and saving the name into a column on a cell in access. (not sure if this matters)
i know its a regular space becuse i put it there for testing.

[Welshgasman]

So upload as requested.

[chubbychub]

here is the file, i just deleted sensitive information
, there are some macros, i didnt know how to remove them

[moke123]

You have the VBE locked down.