Thanks, Lynn...and as I said, I am NOT an expert, nor trying to be...
If the Iif statement is wrong, I accept that...but it seems to be working
OK, could be just "dumb luck"... or a mystery!
Either way, not sure what to do with it, but for now, I will try your
solution.
Also, I know I am somewhat violating the rules by including a range of
pages, but several of the users were complaining that if they reference a
paragraph which includes a range of line numbers, and the block is a numeric
field, they cannot make reference to that range and it wasn't meeting their
needs so I changed it and went with this method...
Working OK now (I sorted the columns by page # first, then Para then Line
and it seems to be working now...???)
Also, I am attaching the info I got from the Access Help page concerning
"sorting numbers in non-numeric fields" which is what I am doing since I have
them in a text box.
From Microsoft Access Help Page: (Pay particular attention to the last para
which starts with "If the previous two techniques do not work for you..."
----------------------------
Sort numbers stored in a nonnumeric field
Numbers stored in a text or memo field will be sorted alphabetically, that
is, based on the first character, instead of on the numeric value. For
example, the value 11 will appear before 2, and the value 2000 will appear
before 3.
To avoid having numbers treated as text values during sorting, do one of the
following:
If the field contains only numeric values, consider changing the data type
of the field to Number or Currency.
If the field contains only numeric values, but you do not want to change the
data type of the field, or you want to ignore the nonnumeric characters when
sorting, use an expression that calls the Val function in the Advanced
Filter/Sort dialog box. If the field contains null values, use the IIf
function to treat null values as 0 or some other number.
On the Records menu, point to Filter and then click Advanced Filter/Sort.
Type the expression Expr1: IIf([Fieldname] Is Null, 0, Val([Fieldname])) in
the Field row in the first column. The IIf function checks to see if the
field value is null, and if so, treats the value as a 0. If the value is not
null, it calls the Val function to obtain the numeric equivalent.
Learn more about using expressions and functions when sorting records
An expression is like a formula that returns a value for each record. Access
uses the return values to determine the sort order of the records. While the
expression that you use will vary with your requirements, all expressions are
a combination of functions, operators, field names, and constants. To learn
how to write expressions and what operators and functions are available in
Access, see the Access Help topics About expressions and Functions (by
category).
If you need help with writing a complex expression or if an expression is
not working as expected, you can get help from communities on the Web by
posting a question to Access experts and other users. Learn about communities
with Ask the Access experts. If you want to learn more about posting
questions to communities, see the article Start getting answers from other
Office users.
In the Sort cell, select Ascending or Descending.
An ascending sort displays the record with the smallest value on top and the
record with the largest value at the bottom. A descending sort does the
opposite.
Click Apply Filter on the toolbar to sort the records.
If the previous two techniques do not work for you, make sure the values are
of the same length before sorting the records. You can do this by padding
values that contain fewer digits with leading zeroes. For example, the values
11, 2, 2000, and 3 will need to be changed to 0011, 0002, 2000, and 0003
before you apply the sort. For more information about how to pad values with
leading zeroes, see the Microsoft Knowledge Base article How to pad character
strings on left and right side. This Knowledge Base article assumes you are
familiar with writing code using Visual Basic for Applications (VBA).
-----------------------------------
They then provide a ling to this page for VBA assistance: (LPad is VBA, I
believe...???)
http://support.microsoft.com/default.aspx?scid=kb;en-us;96458
Thanks again, Lynn...I appreciate you taking the time...
CCR
Lynn Trapp said:
OK, I got the Iif statement formats off the Access help pages...if they
are
wrong, not sure how to fix them, but...they seem to be working.
Well, first of all, without seeing your whole query and knowing your table
structure, I'm not quite sure how to fix it either. I can't imagine what
Access help page would have suggested that you use LPad() inside an IIF
statement. If you have a link to that page I would like to see it.
Page # Para # Line #
16 1.3.8.5 677-684
2 1.2.2 460
3 1.2.2 469
8 1.3.5 571
8 1.3.6 591-594
10 1.3.7 619
16 1.3.8.5 672
16 1.3.8.5 678
23 3.1.2 1007-1009
15 1.3.8.5 669
16 1.3.8.5 671
16 1.3.8.5 674
16 1.3.8.5 688 - 690
23 3.1.2 993
You can get your table to sort on the Page number fairly easily by doing the
following in your order by clause.
Order By CInt([Page #])
That might be more than enough to get things in a usable order, depending on
how many paragraph and line number values you have.
One thing to consider is that your Line # field violates database
normalization rules. A single field should store only a single value -- i.e.
one page number
--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security:
www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List:
www.ltcomputerdesigns.com/JCReferences.html
