query sorting question

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

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.
 
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
*
 
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?
 
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
*
 
Back
Top