Query Sort order!

[Bob Vance]

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

 

[John W. Vinson]

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

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.

 

[Bob Vance]

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

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

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.

 

[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

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.

 

[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

 

[John W. Vinson]

Thanks John

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.

 

[Bob Vance]

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

Thanks John

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.