query sorting question

[Guest]

hello

My qryFlights has fields pkFlightID (autonumber) and FlightName. FlightName
is sorted Ascending. The problem is that I get entries like:

Flight 1
Flight 10
Flight 11
Flight 2
etc...

I'd really like Flights 10 and 11 to follow 9,for example, not 1.

The form used to enter these values showns only the FlightName field in a
continuous form. I'd like the query to sort 'properly' without having to use
another artificial field for sort order. Thanks in advance for any help.

 

[strive4peace]

Hi Cinnie,

Do all the fields start with a one-word text phrase followed by a space
followed by the number without letters?

if so, how about this:

Order by
Left(FlightName, instr(FlightName, " "))
, cLng(right(FlightName, len(FlightName) - instr(FlightName, " ")-1))


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi Crystal

I had to drop the cLng because I kept getting a data mismatch error. Not
sure why.

When I use ORDER BY Left(FlightName,InStr(FlightName," ")),
(Right(FlightName,Len(FlightName)-instr(FlightName," ")-1)); I get...

Flight 9
Flight 8
Flight 7
....
Flight 1
Flight 10
Flight 11
Flight 12
....
Closer, but not quite! Any further tweaks?

 

[strive4peace]

Hi Cinnie,

is the first word going to be Flight (probably a silly question), or did
you just use that for an example?

"had to drop the cLng"

Do your numbers sometimes contain letters?

try making a query and make a calculated field with each part of the
equation so you can see what is happening ... you may see the problem
through manual inspection

field --> Pt1: Left(FlightName,InStr(FlightName," "))
field --> Pt2: (Right(FlightName,Len(FlightName)-instr(FlightName," ")-1))


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*