Why is my query sorting like this

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

and what can I do about it.

The SQL is shown below the #s:

148
154
158
22
28
294
3
300
315
324
337
338
384
487

SELECT DISTINCT
IIf(QryControlLog!AmtOfLoss<>"N/A",[QryControlLog!FundNu],"") AS
FundsIncluded, QryControlLog.Researcher, QryControlLog.SecName,
QryControlLog.CUSIP, QryControlLog.CLActBegPer, QryControlLog.CLActEndPer
FROM QryControlLog
ORDER BY IIf(QryControlLog!AmtOfLoss<>"N/A",QryControlLog!FundNu,"");
 
Robert,

The FundsIncluded field is being interpreted as a Text data type. It is
not immediately clear why that is, but I assume you are expecting it to
be numerical. You have a peculiar reference to "" in the calculation -
probably it should be...
IIf(QryControlLog!AmtOfLoss<>"N/A",QryControlLog!FundNu,0)
or...
IIf(QryControlLog!AmtOfLoss<>"N/A",QryControlLog!FundNu,Null)
.... but I don't think this would cause the problem.
It is derived from the FundNu field - is this a calculated field in the
QryControlLog query? If so, what is the calculation? Can you check the
data shown in the datasheet if you open the QryControlLog query and see
if FundNu appears as number or text?
 
It could be these fields are originally formatted as text fields. The
sort will look for the first value in the field as opposed to the
entire number. So any numbers that start with the number one would
precede any values that begni with 2 and so forth.

Hope this helps!
 
The reason is that you have forced Access to convert the values to strings
when you used
ORDER BY IIf(QryControlLog!AmtOfLoss<>"N/A",QryControlLog!FundNu,"");

You should have used
ORDER BY IIf(QryControlLog!AmtOfLoss<>"N/A",QryControlLog!FundNu,NULL);
or
ORDER BY IIf(QryControlLog!AmtOfLoss<>"N/A",QryControlLog!FundNu,0);

Access can then keep the FundNu as a number (assuming that it was). If that
still fails to sort properly for you then try
ORDER BY Val( IIf(QryControlLog!AmtOfLoss<>"N/A",QryControlLog!FundNu,""))

Note that in this case I do want the empty string, since Val(Null) will
error.
 
Back
Top