Unanswered: Access Report, Sort Header by particular words - How??
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:
San Luis Obispo
I would like them sort in order.. say...
San Luis Obispo
(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..
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
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];
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:
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.
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??
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(2) ="San Luis Obispo"
varCity = arr
'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
MsgBox Err.Number & ": " & Err.Description
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...