Sort or Group

[JDRoss]

In a table where there are three fields for address: Street No, Address0, Address1, I build a "full address" with the following string in a query for the form:

FullAddress: IIf(Not IsNull([address1]),Trim([street no]) & " " & Trim([ADDRESS0]) & ", " & Trim([address1]),Trim([street no]) & " " & Trim([Address0]))

On the form there are some sort options for the user, one of them being Street No. That works fine, but I also have filters on the same form based on Districts in a Regional Area. I build the sort with the following code and set the form Orderby property.

If Me.Sort1 <> &quot;&quot; Or Me.Check1 <> &quot;&quot; Then
        strsql = Buildsqlstring(Me.Sort1, Me.Check1)
    'Build strSQL String to sort the data
        Me.OrderBy = strsql
        Me.OrderByOn = True
     End If

Function Buildsqlstring(Sort1 As String, Optional Check1 As Boolean) As String
        
        Dim strsql As String
        
    'Build strSQL String to sort the data
        
        
        If Sort1 <> &quot;&quot; Then
            If Sort1 = &quot;street no&quot; Then
                strsql = strsql & &quot;[&quot; & Sort1 & &quot;]&quot;
                ' strsql = strsql & &quot;,&quot; & &quot;[&quot; & &quot;address0&quot; & &quot;]&quot; ' Add the field address0
                ' strsql = strsql & &quot;,&quot; & &quot;[&quot; & &quot;Address1&quot; & &quot;]&quot; ' Add the field address1
            Else: strsql = strsql & &quot;[&quot; & Sort1 & &quot;]&quot;
            End If
            If Not IsMissing(Check1) Then If Check1 = True Then strsql = strsql & &quot; DESC&quot;
            strsql = strsql & &quot;, &quot;
        End If
    If strsql <> &quot;&quot; Then
        'Strip Last Comma & Space
        strsql = Left(strsql, (Len(strsql) - 2))
        
        Buildsqlstring = strsql
        
    End If
End Function

So if the user has selected a filter based on either district or area, I would like them to see the results in Datasheet form view grouped.

My problem is that when I sort on Street No, it is not grouping or sorting as I expect for example:

1 Seaside
10 Seaside
10 Fairgreen
11 Seaside
17 Seaside
17 Fairgreen

etc.

Whereas I would like to have the following
1 Seaside
10 Seaside
11 Seaside
17 Seaside
10 Fairgreen
17 Fairgreen


I wonder what I am doing wrong or if I have explained myself clearly?

I appreciate your expert advice and help as usual

Regards

John

 

[IFELSETHEN]

Hi John,
Looks like your street number is not a number but a char. in front of the address and that is why you have the sort like you do. If you want the sort to come out like you have indicated then you need to have the street number as a seperate field.

John

 

[JDRoss]

Thanks IFELSETHEN

You are right, I checked the table and I remembered that because the Street No field can also include letters like

22a, 22b, etc.

I had left the field as text.

However, I am now presented with the problem of sorting this as text or string using the Val function. As I have never used this function before could you give me some ideas on how to implement it? Will it mean building a query at run time to sort the data?

Regards


John