Ken, I got it to work with the following in the last line of the SQL statement:
ORDER BY Val(Nz([SerialNbr],0)), SerialNbr;
I appreciate your help!
Jay
"Jay Maitri" wrote:
> I'll give it a go.
>
> Thank you Ken!
> Jay
>
>
> "Ken Snell (MVP)" wrote:
>
> > Something like this:
> >
> > SELECT SerialNumber, OfficerRank
> > FROM TableName
> > ORDER BY Val(Nz([SerialNumber],0) ASC,
> > SerialNumber ASC;
> >
> > --
> >
> > Ken Snell
> > <MS ACCESS MVP>
> >
> >
> >
> >
> > "Jay Maitri" <(E-Mail Removed)> wrote in message
> > news:4C0144DC-203A-4E22-A250-(E-Mail Removed)...
> > > Hi,
> > >
> > > I am an academic historical researcher and have recently built a database
> > > for research I am currently doing on Australian World War 1 soldiers who
> > > fought on the Gallipoli Peninsula (Turkey).
> > >
> > > One of the data fields in my database is for tracking the assigned serial
> > > numbers of these men. I choose to use a text data type because serial
> > > numbers because Officers were not assigned serial numbers (I leave the
> > > serial
> > > number field blank for their records) and Enlisted men were either
> > > assigned a
> > > number or a numeric-alpha number for their serial number.
> > >
> > > Enlisted men's numbers range from 1 to 5 digits in length. If they were
> > > assigned a numeric-alpha serial number, their number is followed by just 1
> > > letter.
> > >
> > > I would like to sort my data by listing Officers first (serial numbers are
> > > blank) and then Enlisted men in ascending order:
> > >
> > > (blank) Officer
> > > (blank) Officer
> > > 1 Enlisted
> > > 2 Enlisted
> > > 2A Enlisted
> > > 6 Enlisted
> > > 6A Enlisted
> > > 6B Enlisted
> > > and so forth
> > >
> > > I've searched various discussion groups via Google and tried a few things
> > > which either have not worked or not worked well.
> > >
> > > I'm a complete novice-hence ignorant with using query expression and with
> > > writing code for modules-with building databases and with Access (I'm
> > > using
> > > 2003).
> > >
> > > I surely will appreciate any help anyone can provide!
> > >
> > > Thank you,
> > > Jay
> > >
> >
> >
> >
|
Similar Threads
