Query Sort order!

B

Bob Vance

I want to sort my column (Numbers) in a query ascending from 1 > to any
higher number, Blanks last
 
J

John W. Vinson

I want to sort my column (Numbers) in a query ascending from 1 > to any
higher number, Blanks last
Click to expand...

Put in a calculated field by putting one of the expressions below in a vacnt
Field cell in your Query: if the field is a Number/Long Integer use

NZ([fieldname], 2147483647)

If it's Double, use

NZ([fieldname], 1.0E308)

and sort by this field.
 
B

Bob Vance

Thanks John , it is a number/Long Integer but now im getting
1
10
2147483647
3
5
Thanks for your help Bob


John W. Vinson said:
I want to sort my column (Numbers) in a query ascending from 1 > to any
higher number, Blanks last
Click to expand...

Put in a calculated field by putting one of the expressions below in a
vacnt
Field cell in your Query: if the field is a Number/Long Integer use

NZ([fieldname], 2147483647)

If it's Double, use

NZ([fieldname], 1.0E308)

and sort by this field.
Click to expand...
 
J

John W. Vinson

Thanks John , it is a number/Long Integer but now im getting
1
10
2147483647
3
5
Thanks for your help Bob
Click to expand...

Sounds like you're sorting it as Text - did you use Format() to cast it as a
string? If so, don't!

Please post the SQL view of your query.
 
B

Bob Vance

Thanks John
SELECT NZ([SheetOrder],2147483647) AS Expr3, tblHorseInfo.SheetOrder,
tblHorseInfo.HorseID, tblHorseInfo.Status,
funGetHorse(0,tblHorseInfo.HorseID,True) AS Expr1,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Expr2, tblHorseInfo.Invocing,
tblHorseInfo.StatusDate
FROM tblHorseInfo
ORDER BY NZ([SheetOrder],2147483647), tblHorseInfo.SheetOrder,
tblHorseInfo.Status, funGetHorse(0,tblHorseInfo.HorseID,True);
Regards Bob
 
J

John W. Vinson

Thanks John
Click to expand...

Odd. Looks like NZ is somehow casting the result as Text rather than Number.
Try

SELECT tblHorseInfo.SheetOrder,
tblHorseInfo.HorseID, tblHorseInfo.Status,
funGetHorse(0,tblHorseInfo.HorseID,True) AS Expr1,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Expr2, tblHorseInfo.Invocing,
tblHorseInfo.StatusDate
FROM tblHorseInfo
ORDER BY Val(NZ([SheetOrder],2147483647)), tblHorseInfo.SheetOrder,
tblHorseInfo.Status, funGetHorse(0,tblHorseInfo.HorseID,True);

Note that I removed the sort field from the SELECT statement - the query grid
way to do this is to uncheck the "show" button, you don't need to see this
field. You can also replace 2147483647 (the largest possible long integer,
xFFFFFFFF) with a smaller number, just so it's larger than any SheetOrder will
ever be; if a SheetOrder of 1000 is "ridiculously huge" just use 1000.
 
B

Bob Vance

Worked Brilliant John, Thanks for your time and effort much
appreciated............Regards Bob :)

John W. Vinson said:
Thanks John
Click to expand...

Odd. Looks like NZ is somehow casting the result as Text rather than
Number.
Try

SELECT tblHorseInfo.SheetOrder,
tblHorseInfo.HorseID, tblHorseInfo.Status,
funGetHorse(0,tblHorseInfo.HorseID,True) AS Expr1,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Expr2, tblHorseInfo.Invocing,
tblHorseInfo.StatusDate
FROM tblHorseInfo
ORDER BY Val(NZ([SheetOrder],2147483647)), tblHorseInfo.SheetOrder,
tblHorseInfo.Status, funGetHorse(0,tblHorseInfo.HorseID,True);

Note that I removed the sort field from the SELECT statement - the query
grid
way to do this is to uncheck the "show" button, you don't need to see this
field. You can also replace 2147483647 (the largest possible long integer,
xFFFFFFFF) with a smaller number, just so it's larger than any SheetOrder
will
ever be; if a SheetOrder of 1000 is "ridiculously huge" just use 1000.
Click to expand...
 
Top