Trim() concatenated fields of list row source

[jsteeves]

I need to concatenate several fields to provide some visual feedback to the
user.
Problem is each field is packed full of spaces at the end
ex. "CUSTOMERNAME - CITY , STATE
"

i want to trim the recordset returned by the lists row source query.
here is what i THOUGHT would work....
but it justs reads it like the field name is the string i want to trim

Private Sub cboOrdno_AfterUpdate()
Dim WhereLine As String
Dim WhereCust As String

WhereLine = "SELECT Line_" _
& " FROM ordimg WHERE ordno = " & Me.cboOrdno _
& " ORDER BY Line_"
WhereCust = "SELECT ordno, " _
& Trim("[stnam]") & " & ' - ' & " _
& Trim("[stad3]") & " & ', ' & " _
& Trim("[stste]") & " & ' ' & " _
& Trim("[stad5]") & "" _
& " FROM ordimg WHERE ordno = " & Me.cboOrdno _
& " ORDER BY stnam"
Debug.Print WhereLine
Debug.Print WhereCust
Me.cboLine.RowSource = WhereLine
Me.cboCust.RowSource = WhereCust
End Sub

 

[pietlinden]

Why are you storing all the spaces? Why not normalize/strip the spaces
when you insert/import the data?

 

[Steve Schapel]

Jsteeves,

You've got your ""s all higgledy-piggledy. I am pretty sure you mean
more like this...

WhereCust = "SELECT ordno, " & _
" Trim([stnam]) & ' - '" & _
" Trim([stad3]) & ', '" & _
" Trim([stste]) & ' '" & _
" Trim([stad5])" & _
" FROM ordimg" & _
" WHERE ordno = " & Me.cboOrdno & _
" ORDER BY stnam"

 

[fredg]

I need to concatenate several fields to provide some visual feedback to the
user.
Problem is each field is packed full of spaces at the end
ex. "CUSTOMERNAME - CITY , STATE
"

i want to trim the recordset returned by the lists row source query.
here is what i THOUGHT would work....
but it justs reads it like the field name is the string i want to trim

Private Sub cboOrdno_AfterUpdate()
Dim WhereLine As String
Dim WhereCust As String

WhereLine = "SELECT Line_" _
& " FROM ordimg WHERE ordno = " & Me.cboOrdno _
& " ORDER BY Line_"
WhereCust = "SELECT ordno, " _
& Trim("[stnam]") & " & ' - ' & " _
& Trim("[stad3]") & " & ', ' & " _
& Trim("[stste]") & " & ' ' & " _
& Trim("[stad5]") & "" _
& " FROM ordimg WHERE ordno = " & Me.cboOrdno _
& " ORDER BY stnam"
Debug.Print WhereLine
Debug.Print WhereCust
Me.cboLine.RowSource = WhereLine
Me.cboCust.RowSource = WhereCust
End Sub

& Trim("[stnam]") & " & ' - ' & " _

It's reading the field name as the string because you have surrounded
the field name with quotes.
Remove the quotes:
& Trim([stnam]) & " & ' - ' & " _
etc.

 

[jsteeves]

Please elaborate, I am just linking to a FoxPro free table - that in turn
gets dynamically updated from an AS400 RPG based DB.
What you wrote is eventually where I was headed, but do not currently know
how to go about it.
I would like to:
Import only new records
Scrub the data
Import on a timer event
And do this all without ever locking the FoxPro tables - not even on a
record level - they have to be free at all times.

 

[jsteeves]

Thanks that did the trick, what a mess I was making!

WhereCust = "SELECT ordno, " & _
" Trim([stnam]) & ' - ' & " & _
" Trim([stad3]) & ', ' & " & _
" Trim([stste]) & ' ' & " & _
" Trim([stad5])" & _
" FROM ordimg" & _
" WHERE ordno = " & Me.cboOrdno & _
" ORDER BY stnam"

 

[Steve Schapel]

Cool.

Er...
& ', ' & "
is equivalent to
& ', '"

Not sure why you changed it. If it was to make it easier for you to
understand, fine. If you thought one was right and one was wrong, well,
not so.

 

[jsteeves]

I couldn’t get the other way to work; " and & syntax is still moderately
foreign to me. when you concatenate with a delimiter in SQL does it not need
the second ampersand? Honestly I don’t know, I just couldn’t get it to work
----
WhereCust = "SELECT ordno, " & _
" Trim([stnam]) & ' - ' & " & _
" Trim([stad3]) & ', ' & " & _
" Trim([stste]) & ' ' & " & _
" Trim([stad5])" & _
" FROM ordimg" & _
" WHERE ordno = " & Me.cboOrdno & _
" ORDER BY stnam"
SELECT ordno, Trim([stnam]) & ' - ' & Trim([stad3]) & ', ' &
Trim([stste]) & ' ' & Trim([stad5])
FROM ordimg
WHERE ordno = 111111
ORDER BY stnam
----
----
WhereCust = "SELECT ordno, " & _
" Trim([stnam]) & ' - '" & _
" Trim([stad3]) & ', '" & _
" Trim([stste]) & ' '" & _
" Trim([stad5])" & _
" FROM ordimg" & _
" WHERE ordno = " & Me.cboOrdno & _
" ORDER BY stnam"
SELECT ordno, Trim([stnam]) & ' - ' Trim([stad3]) & ', ' Trim([stste]) & '
' Trim([stad5])
FROM ordimg
WHERE ordno = 111111
ORDER BY stnam
-----

Cool.

Er...
& ', ' & "
is equivalent to
& ', '"

Not sure why you changed it. If it was to make it easier for you to
understand, fine. If you thought one was right and one was wrong, well,
not so.

--
Steve Schapel, Microsoft Access MVP

Thanks that did the trick, what a mess I was making!

WhereCust = "SELECT ordno, " & _
" Trim([stnam]) & ' - ' & " & _
" Trim([stad3]) & ', ' & " & _
" Trim([stste]) & ' ' & " & _
" Trim([stad5])" & _
" FROM ordimg" & _
" WHERE ordno = " & Me.cboOrdno & _
" ORDER BY stnam"