Concatenated Field - Sort Order

[Vittles]

{Access2003}
I have a concatenated field in a main query set as follows:
Other Phone: Concatenate2("Select [PhType] & ': ' & [Phone#] FROM [Staff
Contact-OtherPhone SubQ] where [EmplNo]=" & [Employees].[EmplNo] & " ORDER BY
[PrefSO] ASC")

The SubQ [PrefSO] field (based directly off of a table) mentioned is set as
follows:
PrefSO: IIf([PhType] Like "Pref*",1,IIf([PhType]="BB",2,3))

When I pull up the sub query the sort order column is accurate, but when I
pull up the concatenated results from the main query the concatenated field
results are not being sorted by the PreSO field. I have a similar set up
(same concatenation module) in a different database and it works great (only
with a comma between the concatenated fields versus a / in this one) - what
am I missing?

 

[Duane Hookom]

Are you using Concatenate2 in both queries? Was Concatenate() modified to
Concatenate2()? I don't see where you are using "/" in this one.

You could also try to sort descending by:
PrefSO: Instr("~BB~Pref~",[PhType])

 

[Vittles]

The concatenation coding is yours (as follows):
Function Concatenate2(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

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
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate2 = strConcat
End Function

I add a number after the concatenate function name when I have modified the
joining string - for some purposes a comma is good, for others it isn't....
In the original it was a comma and in this version it is a "/".

Are you using Concatenate2 in both queries? Was Concatenate() modified to
Concatenate2()? I don't see where you are using "/" in this one.

You could also try to sort descending by:
PrefSO: Instr("~BB~Pref~",[PhType])
--
Duane Hookom
Microsoft Access MVP

{Access2003}
I have a concatenated field in a main query set as follows:
Other Phone: Concatenate2("Select [PhType] & ': ' & [Phone#] FROM [Staff
Contact-OtherPhone SubQ] where [EmplNo]=" & [Employees].[EmplNo] & " ORDER BY
[PrefSO] ASC")

The SubQ [PrefSO] field (based directly off of a table) mentioned is set as
follows:
PrefSO: IIf([PhType] Like "Pref*",1,IIf([PhType]="BB",2,3))

When I pull up the sub query the sort order column is accurate, but when I
pull up the concatenated results from the main query the concatenated field
results are not being sorted by the PreSO field. I have a similar set up
(same concatenation module) in a different database and it works great (only
with a comma between the concatenated fields versus a / in this one) - what
am I missing?

 

[Duane Hookom]

I don't think you needed a copy of the function since the optional argument
of the function allows you to enter any delimiter you want. It defaults to
comma but you should have been able to use:
Other Phone: Concatenate("Select [PhType] & ': ' & [Phone#] FROM [Staff
Contact-OtherPhone SubQ] where [EmplNo]=" & [Employees].[EmplNo] & " ORDER
BY [PrefSO] ASC"," / ")

I would run some debugging code to troubleshoot. Maybe place a breakpoint in
the code and then open the debug window (press Ctrl+G) and enter:
? Concatenate("Select [PhType] & ': ' & [Phone#] FROM [Staff
Contact-OtherPhone SubQ] where [EmplNo]=XX ORDER BY [PrefSO] ASC")

Replace the XX with an EmplNo that doesn't seem to work correctly in the
query.


--
Duane Hookom
Microsoft Access MVP

The concatenation coding is yours (as follows):
Function Concatenate2(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

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
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate2 = strConcat
End Function

I add a number after the concatenate function name when I have modified the
joining string - for some purposes a comma is good, for others it isn't....
In the original it was a comma and in this version it is a "/".

Are you using Concatenate2 in both queries? Was Concatenate() modified to
Concatenate2()? I don't see where you are using "/" in this one.

You could also try to sort descending by:
PrefSO: Instr("~BB~Pref~",[PhType])
--
Duane Hookom
Microsoft Access MVP

{Access2003}
I have a concatenated field in a main query set as follows:
Other Phone: Concatenate2("Select [PhType] & ': ' & [Phone#] FROM [Staff
Contact-OtherPhone SubQ] where [EmplNo]=" & [Employees].[EmplNo] & " ORDER BY
[PrefSO] ASC")

The SubQ [PrefSO] field (based directly off of a table) mentioned is set as
follows:
PrefSO: IIf([PhType] Like "Pref*",1,IIf([PhType]="BB",2,3))

When I pull up the sub query the sort order column is accurate, but when I
pull up the concatenated results from the main query the concatenated field
results are not being sorted by the PreSO field. I have a similar set up
(same concatenation module) in a different database and it works great (only
with a comma between the concatenated fields versus a / in this one) - what
am I missing?

 

[Vittles]

I just got done troubleshootingthe end query by putting the the [PrefSO] in
front of the phone type and found a quirk. I found that the sort order was
working in the subquery (when run by itself), but when put into the
concatenation string, this same data was not accurate. In other words if a
person had a phone type like 'pref' it was listing it as a 3 versus a 1. The
main query/concatenation doesn't seem to work well with 'Like' operators.

I ended up getting the accurate concatenated results by switching the
[PrefSO] to be the following: PrefSO:
IIf(Left([PhType],4)="Pref",1,IIf([PhType]="BB",2,3))

I don't know why this matters, but it does work.

The concatenation coding is yours (as follows):
Function Concatenate2(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

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
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate2 = strConcat
End Function

I add a number after the concatenate function name when I have modified the
joining string - for some purposes a comma is good, for others it isn't....
In the original it was a comma and in this version it is a "/".

Are you using Concatenate2 in both queries? Was Concatenate() modified to
Concatenate2()? I don't see where you are using "/" in this one.

You could also try to sort descending by:
PrefSO: Instr("~BB~Pref~",[PhType])
--
Duane Hookom
Microsoft Access MVP

{Access2003}
I have a concatenated field in a main query set as follows:
Other Phone: Concatenate2("Select [PhType] & ': ' & [Phone#] FROM [Staff
Contact-OtherPhone SubQ] where [EmplNo]=" & [Employees].[EmplNo] & " ORDER BY
[PrefSO] ASC")

The SubQ [PrefSO] field (based directly off of a table) mentioned is set as
follows:
PrefSO: IIf([PhType] Like "Pref*",1,IIf([PhType]="BB",2,3))

When I pull up the sub query the sort order column is accurate, but when I
pull up the concatenated results from the main query the concatenated field
results are not being sorted by the PreSO field. I have a similar set up
(same concatenation module) in a different database and it works great (only
with a comma between the concatenated fields versus a / in this one) - what
am I missing?