replace and trim erratic commas and spaces in field

[John1Chr]

Hi all,

I have a field that looks like:

OLD Structures
9782
, 69003
, , , , , , , , , , , , , , , , , , , 69812, 69880, 69831, 69832, , , , , , , , , , , , 69835
6730, 6731
91049, 91050
6738, 6630,
9800, , , , 62881
85807, 85808, 85809, 85810
5388,

and I want
OLD STRUCTURES
9782
69003
69812,69880,69831
69832,69835
6730,6731
91049,91050
6738,6630
9800,62881
85807,85808,85809,85810
5388

I'm trying to get rid of the unecessary commas & spaces in the beginning of the field and the end of the field.

I got close with Trim(replace(replace([OLD Structures]," ",""),",,,","")) but haven't nailed it down.
It's probably simpler than I'm making it out to be.

 

[John1Chr]

This problem came about when I ran the FAQ for contencate family and the old structure didn't have any relation to the new structure because not all new structures are replacing old structures. In the blank old structure field there are 8 blank spaces.

Thought maybe it would be quicker to use the replace function in the query.

 

[lespaul]

what you need to do is modify the FAQ code you got to only add a comma when there's a value in the field. So when you are looping through the result set and adding the field and comma, first check to make sure the field has something in it, if it does add it and the comma, if it doesn't go to the next record.

HTH

Leslie

Have you met Hardy Heron?

 

[John1Chr]

That's the frustrating part...that it has eight blank spaces in those fields instead of being null. So, I tried to bypass by running a query on the contencating query.

 

[lespaul]

what's the concatenating code you're running now?

Leslie

 

[dhookom]

If you are using my Concatenate() function, you can add a where clause to the SQL statement sent to the function. If you can't figure how to do this, provide the SQL of your query as well as information about your source data.

Duane
Hook'D on Access
MS Access MVP

 

[John1Chr]

Duane,

I am using your code. Thanks for providing it. My SQL is below. Basically, I'm concatenating both the old structure and new structure in one query. The old structure is null in some instances. That is what I would like to ignore, if possible.

My sql code:

SELECT PPMS_PPMS_PM_STRUCTURE.PROJ_SEQ, PPMS_PPMS_PM_PROJECT.PROJ_DESC, PUBLIC_PPMS_PM_SP_NUMBERS.SP_NBR AS [LOW SP], Concatenate("SELECT STRUC_OLD_NBR FROM PUBLIC_PPMS_PM_STRUCTURE WHERE PROJ_SEQ =" & [PUBLIC_PPMS_PM_SP_NUMBERS]![PROJ_SEQ]) AS [OLD Structures], Concatenate("SELECT STRUC_NBR FROM PUBLIC_PPMS_PM_STRUCTURE WHERE PROJ_SEQ =" & [PUBLIC_PPMS_PM_SP_NUMBERS]![PROJ_SEQ]) AS [New Structures]
FROM (([tbl_HF 2800 fr cr br 09] LEFT JOIN PPMS_PPMS_PM_STRUCTURE ON [tbl_HF 2800 fr cr br 09].[Bridge Nbr Non Zero] = PPMS_PPMS_PM_STRUCTURE.STRUC_OLD_NBR) LEFT JOIN PUBLIC_PPMS_PM_SP_NUMBERS ON PPMS_PPMS_PM_STRUCTURE.PROJ_SEQ = PUBLIC_PPMS_PM_SP_NUMBERS.PROJ_SEQ) LEFT JOIN PPMS_PPMS_PM_PROJECT ON PPMS_PPMS_PM_STRUCTURE.PROJ_SEQ = PPMS_PPMS_PM_PROJECT.PROJ_SEQ
WHERE (((PUBLIC_PPMS_PM_SP_NUMBERS.SP_TYP_CDE)="L"))
GROUP BY PPMS_PPMS_PM_STRUCTURE.PROJ_SEQ, PPMS_PPMS_PM_PROJECT.PROJ_DESC, PUBLIC_PPMS_PM_SP_NUMBERS.SP_NBR, Concatenate("SELECT STRUC_OLD_NBR FROM PUBLIC_PPMS_PM_STRUCTURE WHERE PROJ_SEQ =" & [PUBLIC_PPMS_PM_SP_NUMBERS]![PROJ_SEQ]), Concatenate("SELECT STRUC_NBR FROM PUBLIC_PPMS_PM_STRUCTURE WHERE PROJ_SEQ =" & [PUBLIC_PPMS_PM_SP_NUMBERS]![PROJ_SEQ])
HAVING (((PPMS_PPMS_PM_STRUCTURE.PROJ_SEQ) Is Not Null));


Duane's Code:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

 

[dhookom]

Try change the Concatenate() syntax to:

Concatenate("SELECT STRUC_NBR FROM PUBLIC_PPMS_PM_STRUCTURE WHERE Trim(STRUC_NBR & '') <> '' AND PROJ_SEQ =" & [PUBLIC_PPMS_PM_SP_NUMBERS]![PROJ_SEQ]) AS [New Structures]

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I'd use something like this in the SQL code:

Concatenate("SELECT STRUC_OLD_NBR FROM PUBLIC_PPMS_PM_STRUCTURE WHERE PROJ_SEQ=" & PUBLIC_PPMS_PM_SP_NUMBERS.PROJ_SEQ [!]& " AND Trim(STRUC_OLD_NBR & '')<>''"[/!]) AS [OLD Structures], Concatenate("SELECT STRUC_NBR FROM PUBLIC_PPMS_PM_STRUCTURE WHERE PROJ_SEQ=" & PUBLIC_PPMS_PM_SP_NUMBERS.PROJ_SEQ [!]& " AND Trim(STRUC_NBR & '')<>''"[/!]) AS [New Structures]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[John1Chr]

Duane,

Query runs fine but everything returns blank for the new structures. I probably goofed on something. Here's my code...I'll give PHV's suggestion a try.


SELECT PPMS_PPMS_PM_STRUCTURE.PROJ_SEQ, PPMS_PPMS_PM_PROJECT.PROJ_DESC, PUBLIC_PPMS_PM_SP_NUMBERS.SP_NBR AS [LOW SP], Concatenate("SELECT STRUC_OLD_NBR FROM PUBLIC_PPMS_PM_STRUCTURE WHERE PROJ_SEQ =" & [PUBLIC_PPMS_PM_SP_NUMBERS]![PROJ_SEQ]) AS [OLD Structures], Concatenate("SELECT STRUC_NBR FROM PUBLIC_PPMS_PM_STRUCTURE WHERE Trim(STRUC_NBR & '') <> '' AND PROJ_SEQ =" & [PUBLIC_PPMS_PM_SP_NUMBERS]![PROJ_SEQ]) AS [New Structures]
FROM (([tbl_HF 2800 fr cr br 09] LEFT JOIN PPMS_PPMS_PM_STRUCTURE ON [tbl_HF 2800 fr cr br 09].[Bridge Nbr Non Zero] = PPMS_PPMS_PM_STRUCTURE.STRUC_OLD_NBR) LEFT JOIN PUBLIC_PPMS_PM_SP_NUMBERS ON PPMS_PPMS_PM_STRUCTURE.PROJ_SEQ = PUBLIC_PPMS_PM_SP_NUMBERS.PROJ_SEQ) LEFT JOIN PPMS_PPMS_PM_PROJECT ON PPMS_PPMS_PM_STRUCTURE.PROJ_SEQ = PPMS_PPMS_PM_PROJECT.PROJ_SEQ
WHERE (((PUBLIC_PPMS_PM_SP_NUMBERS.SP_TYP_CDE)="L"))
GROUP BY PPMS_PPMS_PM_STRUCTURE.PROJ_SEQ, PPMS_PPMS_PM_PROJECT.PROJ_DESC, PUBLIC_PPMS_PM_SP_NUMBERS.SP_NBR, Concatenate("SELECT STRUC_OLD_NBR FROM PUBLIC_PPMS_PM_STRUCTURE WHERE PROJ_SEQ =" & [PUBLIC_PPMS_PM_SP_NUMBERS]![PROJ_SEQ]), Concatenate("SELECT STRUC_NBR FROM PUBLIC_PPMS_PM_STRUCTURE WHERE Trim(STRUC_NBR & '') <> '' AND PROJ_SEQ =" & [PUBLIC_PPMS_PM_SP_NUMBERS]![PROJ_SEQ])
HAVING (((PPMS_PPMS_PM_STRUCTURE.PROJ_SEQ) Is Not Null));

 

[John1Chr]

PHV,

You're code worked. One thing, in design view I changed from expression to group by and it worked like a charm.