Emma
Sorting text fields which contain numeric values is always tricky because
alphabetically 1,2,3,10,11,100 would be sorted in the following order:
1,10,100,11,2,3. To avoid this, numbers could be entered using a fixed
number of digits, ie 0001,0002,0003,0010,0011,0100. These numbers will then
sort in the correct order.
This does not solve the sort problem if you have historic data which has
been entered without using a fixed format. Therefore you have 2 options:
a. To reformat all values in the PrefRef field to a fixed format using an
update query
or
b. To force a format whenever the PrefRef is printed out/requires sorting
Solutions:
a. Goto Adhoc queries. Create a new query based on the Mon table. Change
the query type to Update. Add the PrefRef field to the query grid. In the
Update To box enter the following:
Format$(Val([PrefRef]),"0000") - this will force the number to 4 digits.
Add extra zeros to increase the number of digits. If this fails, it is most
likely that you have a non numeric character in one of the fields.
b. Make a copy of the report which you want sorted (see the procedure
detailed in my last email - listed below - Note that the exeGesIS SDM
technical support Downloads are now working). In the Sorting and Grouping
form add the following expression:
Val([PrefRef]) or Format$(Val([PrefRef]),"0000")
This will then sort the report.
Note: You cannot change the PrefRef field data type to numeric because text
and numeric fields have to be handled differently in code, therefore the
code is written for a text field. Changing the format will make the HBSMR
software fail. The PrefRef field is text because many SMRs use an
alphanumeric reference system. As a general rule, do not change the data
type of any HBSMR fields.
Regards
Tony
<<<<<<<<<<<<<<<<
Changing a report sort order
All reports have a predefined format and ordering. This is defined when the
reports are designed. When you apply a filter on the index and then sort on
screen, this does not change the report sort order, instead, you must
create a different report. This is not hard because you can copy an
existing report, then simply change the sorting. The new report can then be
saved under a new name, then added to the list of available reports on the
Index form. See below for step by step instructions.
Alternatively I have started posting additional reports on our technical
support web site. You will be able to download these reports, then follow
the instructions which are supplied in a text file zipped up with the
reports - unfortunately there is problem with the download - I've asked our
Web site manager to fix it asap.
In the meantime the manual method is as follows:
Login as the system administrator.
Select Adhoc Queries on the main menu.
Select the Reports Tab
Select MonListRpt
Copy it by pressing <Ctrl>C followed by <Ctrl>V - provide a new name when
prompted - eg - MonListSortedByPrefRefRpt
Select the new report and press the Design button
Press the Sorting and Grouping button on the tool bar
In the next available line select the field PrefRef
Close the Sorting and Grouping form
Save the report File >> Save
Close the report
Run the Autoexec macro
Goto the Monument Index
Double click on the Report Pulldown list
In the popup ReportLUT listing, goto the bottom record and enter the name
given to the report in the Name field. A short description in the Desc
field, then MonInventFrm in the FormName field, Rpt in the ObjType field.
Then close the form.
The report should now be on the Report list.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-----Original Message-----
From: Emma Jones [mailto:[log in to unmask]]
Sent: Wednesday, October 03, 2001 09:30
To: [log in to unmask]
Subject: Re: Exegesis - printing in sorted SMR number order
Dear Tony
Our problem is that sorting on the PrefRef (a text field) does not produce
a numerically ordered printout. I have tried to change the format of the
PrefRef field to numerical, as the field contains only numbers, but the
system does not let me do this.
I there another way of producing a report in PrefRef number order?
Emma Jones
Warwickshire SMR
|