Microsoft Access - Removing spaces from table text imprted from .csv file.

Asked By Charles Bostic on 11-Oct-12 04:50 PM

I have an issue matching tables in a query on certain fields because the field contains text with spaces at the end of the text. I tried the “Trim” and “Replace” keywords but all fail to remove the additional space(s). The file was imported from a .csv spreadsheet file that might be causing my problem but I’m unsure how solve this matter. Any help would be appreciated.

Robbe Morris replied to Charles Bostic on 11-Oct-12 04:58 PM

What is the datatype of the column in your Access database?

wally eye replied to Charles Bostic on 11-Oct-12 07:24 PM

The spaces might actually not be spaces but special characters. Try copying one character, then go to the VBA IDE (alt-F11), immediate window (ctrl-G), type:

=ASC("

paste in the character, close the double-quotes and parenthesis and press enter. If it isn't 32, then it isn't really a space.

You can check the values with something like this:

public sub PrintChars()

    call PrintAllChars("Now is the time for all good men to come to the aid of their country.")

end sub

public sub PrintAllChars(byval varInput as variant)

   dim intChar      as integer

  for intchar = len(varinput) to 1 step -1
    debug.print intchar, asc(mid(varinput,intchar,1)), mid(varinput, intchar,1)
    next intchar

end sub

just paste some of the text from your table in place of the Now is the time line

Charles Bostic replied to Robbe Morris on 12-Oct-12 09:07 AM

The data type is "Text"