Help needed on query sort - IIf?

[BlackBox95]

Hello all,

I have an .mdb that keeps track of construction Equipment and vehicle inventory. I am trying to come up with a query that will sort the items by age (newest to oldest)and/or by serial number (newest to oldest).

My problem occurs when i recieve an inventory listing that does not have a year but does have a serial number. If there is no year listed, i want to sort by serial number so that the listing still falls in the correct place within the correct year. I cant just sort by decreasing serial number because this is a text field and would produce erroneous results because of the alphabetic characters in the Serial nUmber field.

this is part of my ORDER BY statement:

IIf(Equipment.Year Is Not Null,Equipment.Year,Equipment.[Serial Number]) DESC, Equipment.[Serial Number] DESC;

but this sorts the items w/ no year at the top
can anyone point me in the right direction?

JC

 

[dhookom]

Are you expecting to sort by some type of year value if there is no year? Maybe you should provide some sample values and tell us how you would expect to see them sorted in your query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

in the correct place within the correct year
What is the correct year in this case ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BlackBox95]

Ok, too keep things simple ill make up a simple scenario:

Table1 has 5 fields:
ID(autonumber),Year(number),SerialNumber(text), Description (text)

Table1 has 9 records:

ID Yr SN Descr
8 2001 00001 testdescription1
1 2002 00002 testdescription2
6 2003 00003 testdescription3
9 2004 00004 testdescription4
3 00005 testdescription5
5 2006 00006 testdescription6
2 2007 00007 testdescription7
4 00008 testdescription8
7 2009 00009 testdescription9

i want to make a query that will sort the descriptions by year and then by serial number, if ther is a year value. But only by serialnumber if ther is no year value.

A simple sorted query sorts the records with years and serialnumbers fine, but puts any item witout a value in the year field (ID#5,8) at the bottom, then sorts by serial nu8mber.

I want to use IIf logic to sort the records without year values into their correct place within those that have been sorted by year, based on the serial number. i need a query that could reconstruct that table in the order presented

and the ID numbers in this case were just arbitrary so can just sort them or the SN.

thanks again
JC

 

[PHV]

Sorry, but I don't understand why simply sorting by SN doesn't meet your requirement ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BlackBox95]

Simply sorting by SN would work in my example above, but I guess I oversimplified my example for clarity. The actual serial numbers that i am working with are vehicle VIN#s and other similar strings. They all have 10 to 17 digits and contain both letters and numbers so they must be a text field. Sorting on them alone will produce incorrect data, because there are many SNs that may begin with A or 1 that should be at the end of the list because of their age. The 10th digit in a vehicle VIN# indicates the year of manufacture, all the digits before that describe the make/model/config, etc. All the digits after that are the build sequence number (autonumber ID for cars)

basically if the year field has a value, i want to sort the records by year and then by serial number. But if there is no year value i want to use the serialnumber's year digit and sequence digits to sort the item into the correct spot.

thanks,
JC

 

[PHV]

Like this ?
ORDER BY Mid(SN,10), SN

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886