Solved Union Query - sort order ? (1 Viewer)

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 03:22
Joined
Nov 8, 2005
Messages
3,296
Hello people

I am sure that I am just missing the plot on this one

ok - 2 queries
excatly same field names (keep it simple)

I get all the data I want - great - but cannot get it to sort (Numerically )
the first field is numeric and is numbered 1-999 (with gaps ) 1-199 is x and 200-300 is y and 301-400 is z products /information -and there willb e gaps - might only be 5 entries in the 301 to 400 - kind of deal)

anyway union qry 1 comes first then all of the 1's 1 ,10 11,111, where as its should be assending 1,2,3,4,5, etc (sortorderno is numbers)

SELECT[sortorderno],LefthandTitle,Spacer,substance,QuoteSlip,Quoteno,Groupfilter

FROM SlipQuery1

union


SELECT[sortorderno],LefthandTitle,Spacer,substance,QuoteSlip,Quoteno,Groupfilter

FROM SlipQuery2

what have I missed??


order by sortorderno


regards
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 03:22
Joined
Nov 8, 2005
Messages
3,296
testunion
sortorderno
0
10
110
120
130
140
150
150.1
151
151.1
152
160
170
180
190
2
200
210
220
230
240
250
260
270
280
380
390
9
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:22
Joined
Sep 21, 2011
Messages
14,050
sortorderno is text?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:22
Joined
May 21, 2018
Messages
8,463
what if you
SELECT cdbl([sortorderno])
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:22
Joined
May 21, 2018
Messages
8,463
I have no idea what is going on in those queries. My guess they did a format or something else to turn it to text. That will turn it back to double.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:22
Joined
May 21, 2018
Messages
8,463
CBoolBooleanAny valid string or numeric expression.
CByteByte0 to 255.
CCurCurrency-922,337,203,685,477.5808 to 922,337,203,685,477.5807.
CDateDateAny valid date expression.
CDblDouble-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values.
CDecDecimal79,228,162,514,264,337,593,543,950,335 for zero-scaled numbers, that is, numbers with no decimal places. For numbers with 28 decimal places, the range is 7.9228162514264337593543950335. The smallest possible non-zero number is 0.0000000000000000000000000001.
CIntInteger-32,768 to 32,767; fractions are rounded.
CLngLong-2,147,483,648 to 2,147,483,647; fractions are rounded.
CLngLngLongLong-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807; fractions are rounded. (Valid on 64-bit platforms only.)
CLngPtrLongPtr-2,147,483,648 to 2,147,483,647 on 32-bit systems, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 on 64-bit systems; fractions are rounded for 32-bit and 64-bit systems.
CSngSingle-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values.
CStrStringReturns for CStr depend on the expression argument.
CVarVariantSame range as Double for numerics. Same range as String for non-numerics.
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 03:22
Joined
Nov 8, 2005
Messages
3,296
Whoooo hold your horses .. - when I just do the second one - I think it works ..
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 03:22
Joined
Nov 8, 2005
Messages
3,296
OK stupid me -
Query 1 - fine and dandy
Query 2 - I "coded" it to put the "sortorderno" I needed - so I tihnk that element might of been text...

sometimes taking it slowly to see where the issue is - is the easiest way ..

thanks
 

Minty

AWF VIP
Local time
Today, 10:22
Joined
Jul 26, 2013
Messages
10,355
A union will always "default" to the output datatype that will handle any of the unioned fields data.
So in your case, that was a string due to Query 2
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:22
Joined
May 21, 2018
Messages
8,463
As I expected. If one is text the column becomes text.
 

Users who are viewing this thread

Top Bottom