RE: Concatenated Field - Sort Order
- From: Vittles <Vittles@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 22 Dec 2008 15:11:03 -0800
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. :)
"Vittles" wrote:
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 "/".
"Duane Hookom" wrote:
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
"Vittles" wrote:
{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?
- References:
- Concatenated Field - Sort Order
- From: Vittles
- RE: Concatenated Field - Sort Order
- From: Duane Hookom
- RE: Concatenated Field - Sort Order
- From: Vittles
- Concatenated Field - Sort Order
- Prev by Date: Query that references a field ID(P_K) to filter data from other ta
- Next by Date: Re: query will not work between years
- Previous by thread: RE: Concatenated Field - Sort Order
- Next by thread: Calculating totals/subtotals
- Index(es):
Relevant Pages
|
Loading
