Pad text with trailing space

[SusanV]

Hi all,

Probably simple but driving me batty!

I need to be able to set a text field to pad left and pad right with a
single space, but when I go to the table after, the field has lost the pad
right space. How can I get Access to keep this trailing space?

TIA,

SusanV

 

[Van T. Dinh]

JET normally deletes trailing spaces on data input before storing the value
in the Table. On imports, JET sometimes retains the trailing spaces
depending on the source database engine (e.g. import from Attache accounting
software which uses C-Tree database engine).

If you need trailing spaces, use a Calculated Field in the Query to pad to
the right.

 

[SusanV]

Hi Van, and thanks once again for your help!

A bit more background may be helpful, this is what's going on where I need
the leading and trailing spaces:

Users open main form and in the background via VBA the narr field has
formatting marks removed via Replace function, then it's converted to lower,
then loop through tblAcronyms using Replace to get certain acronyms back to
upper case. If users see something in lowercase that should be upper or
title, they click a button which opens another form (tblAcronyms) where they
enter the lowercase word (fldLower) then the replacement (fldUpper), and
when they close the entry form the main form reloads, reformatting the narr
field.

The reason I need to pad left and pad right is because if they put in "us"
and "US" then the word "because" becomes 'becaUSe" or the word "thus"
becomes "thUS." Make more sense now?

I'm not sure how to, as you suggested, "use a Calculated Field in the Query
to pad to the right." in this scenario?

SusanV

 

[SusanV]

Sorry, should have included this in my last post!
This is the code where it loops through the table:
''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim rstTblAcr As ADODB.Recordset
Set rstTblAcr = New ADODB.Recordset
rstTblAcr.Open "SELECT * FROM tblAcronyms", CurrentProject.Connection
rstTblAcr.MoveFirst
Do While Not rstTblAcr.EOF
narr1 = Replace(narr1, rstTblAcr.Fields("fldLower"),
rstTblAcr.Fields("fldUpper"))
rstTblAcr.MoveNext
Loop
rstTblAcr.Close
Set rstTblAcr = Nothing

'update temp record with formatted narr
DoCmd.RunSQL "UPDATE tblMCodeTEMP SET NARR = " & Chr(39) & narr1 &
Chr(39)
''''''''''''''''''''''''''''''''''''''''''''''''''''''

SusanV

 

[Van T. Dinh]

I am not sure I understood your code fully but try the statement:

narr1 = Replace(narr1, " " & rstTblAcr.Fields("fldLower") & " ", _
" " & rstTblAcr.Fields("fldUpper") & " ")

 

[SusanV]

Thanks Van, I'll give that a shot

;-)

I am not sure I understood your code fully but try the statement:

narr1 = Replace(narr1, " " & rstTblAcr.Fields("fldLower") & " ", _
" " & rstTblAcr.Fields("fldUpper") & " ")