Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55

    Unanswered: Access Report, Sort Header by particular words - How??

    Hello,

    I am not very good at programming so here I am. Code may be necessary I think but I do not know... this should be easy <I think>.

    I have a report (reprtRentalGuide2New)
    I have Header (CATEGORY) that sorts properly.. ie I can make it ascend or decend.
    This Header(CATEGORY) is built from a Combobox that has specific selections in it... ie.... Names of towns... Richmond, Dallas, Los Angeles, Rochester, San Luis Obispo, Denver.... Etc... These towns are specific and total 20

    When I sort, the form wizard only allows alphabetical ascending or decending.

    What I need is to list the towns by MY fixed Selection(ok to be in VB or SQL code, because it will not change) for instance, rather than being listed as:

    Dallas
    Denver
    Los Angeles
    Richmond
    Rochester
    San Luis Obispo

    I would like them sort in order.. say...

    Rochester
    Dallas
    San Luis Obispo
    Richmond
    Denver
    Rochester....
    (or any order I select)

    How do I do that?...
    I have gotten to the "CATEGORY Header" and selected "Sorting and Grouping"
    but it only allows me to ascend or decend, I don't know how to enter my own selected order.
    Am I clear???

    Let me know if you have any questions.... remember I am not a programmer, but I can do some minor tasks..

    Thanks in advance.

  2. #2
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89
    Custom sorts are best created elsewhere. The best approach is to:
    create a new table specifying the sort; link that table to the table containing the data you want to sort; and create a query based on these two tables. NB Create a new copy of the database and try the following there first.

    Let's say your values are in field, strCity in table, tblAddress. Now:

    1. Create a table, tblCityGroup with fields:
    a. lngCityID - AutoNumber & PrimaryKey.
    b. strCity - Text (this will be eqivalent to [tblAddress].[strCity])
    c. lngOrder - Number

    2. Append unique values of [tblAddress].[strCity] to tblCityGroup by creating and running an append query with an SQL statement like this:

    INSERT INTO tblCityGroup ( strCity )
    SELECT DISTINCT tblAddress.strCity
    FROM tblAddress;

    3. Create a record order by entering values in [tblCityGroup].[lngOrder].

    4. Create a new number data type field in tblAddress called lngCityID. This will be used to sort the records in this table.

    4. Update [tblAddress].[lngCityID] to the equivalent [tblCityGroup].[lngCityID] value by creating & running an update query with SQL like this:

    UPDATE tblCityGroup INNER JOIN tblAddress ON tblCityGroup.strCity = tblAddress.strCity SET tblAddress.lngCityID = [tblCityGroup].[lngCityID];

    5. Now:
    a. Make [tblAddress].[lngCityID] a required field.
    b. Create an index where duplicates are allowed based on [tblAddress].[lngCityID]
    c. Create a relation between [tblAddress].[lngCityID] and [tblCityGroup].[lngCityID]. Make sure you select Maintain Referential Integrity.

    6. The final stage is to make Report.RecordSource a select query based on the two tables. The SQL will be like this:

    SELECT tblAddress.*
    FROM tblAddress INNER JOIN tblCityGroup ON tblAddress.lngCityID = tblCityGroup.lngCityID
    ORDER BY tblCityGroup.lngOrder;

    The question you've asked is simple but requires a reasonably complex answer. There is a further downside to this. You have to maintain a seperate table, tblCityGroup, and make sure your users only select lngCity_ID/strCity values from this table.

    If you have any problems with this. Post again in this thread.

  3. #3
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Thanks MyNewFlavour for your kind response.

    You answer pushes to the limit my ability in MSAccess.

    This will be extremely difficult for me so I will need allot of time to try <and much more coffee>... but first please allow me to ask a lame question.

    I would think that this could could be done in VB or SQL Code and have the cities listed in there.. in the code. That would force the lookup in the code rather than making another table and cross referenceing the city and number... am I off base?

    Since I have complete control over adding and deleting cities on the list, modifying the lookup that resides in the code would be fine, expecially if it simplifies the operation...

    I guess Im asking ... is there a simplier way more plaitable to my lack of skills??

    Thanks for your attention.

  4. #4
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89
    You can create a function, say CitySort, in a general module (not in the report class module), pass the value of strCity to it, and sort on the function result like this:

    SELECT tblAddress.* FROM tblAddress ORDERBY CitySort([strCity])

    This has 3 major disadvantages:
    1. Any query will be SLOW and get SLOWER as you add more records or more city values to compare with.
    2. You'll have to maintain the list of cities in code.
    3. You won't be able to guarantee to match all city values resulting in unpredictable sorts.

    Anyway, here's how you might write the function:

    Public Function CitySort(ByVal City As Variant) As Long
    On Error GoTo Err_CitySort
    Static varCity As Variant
    Dim intCity As Integer

    'Create city comparison array the first time the function is called.
    If Not IsArray(varCity) Then
    ReDim arr(5) as String
    arr(0) ="Rochester"
    arr(1) ="Dallas"
    arr(2) ="San Luis Obispo"
    arr(3) ="Richmond"
    arr(4) ="Denver"
    arr(5) ="Rochester"
    varCity = arr
    End If

    'Only check city against comparison array if not null.
    If Not IsNull(City) Then
    City =Trim(City) 'Remove leading and trailing spaces
    For intCity = 0 To Ubound(varCity)
    If City = varCity(varCity) Then
    'Return Function value.
    CitySort = intCity + 1
    Exit For
    End If
    End If

    Exit_CitySort:
    Exit Function

    Err_CitySort:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_CitySort
    End Function

    If a city is passed to CitySort that is not equal to an item in the comparison array the function will return 0. So, if you sort ascending on CitySort function values they'll appear at the top of the list.

    Creating a function will get you out of a hole but I'd strongly suggest you try the previous suggestion. When you've got it sussed you can change things for the better...
    Last edited by MyNewFlavour; 08-23-06 at 08:29.

  5. #5
    Join Date
    Feb 2004
    Posts
    90
    Easiest Way:

    In your cities table create another field (data type: number) called sort_order (for example). You could then assign each city a sort order number (i.e. 1-20).

    Then create a query that contains this sort_order number as well as your other fields.

    Change the data source of the report to this query and change the report to sort by the sort_order field.

  6. #6
    Join Date
    Feb 2004
    Posts
    90
    Actually I'd assign the cities sort order numbers in stages of say 100.

    ie. 100, 200, 300, 400 etc

    Then when new cities are added, you can easily assign them a sort order number that comes between two other cities, without having to change all the existing sort order numbers.

  7. #7
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Thank you for your thoughtful replies.

    I think I will try the 'Easiest Way' first and see if that will satisfy my need.

    I will let you know how it goes. I find it important in these forums to post results for others to benefit.

    Regards and thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •