Asger Blond
ab-mi at post3.tele.dk
Fri Nov 2 18:27:57 CDT 2007
--correction, the query should be like this (supposing text data type and empty string "" as empty field value): SELECT w, x, y, z, IIf(val(x)>val(w) Or val(y)>val(w) Or val(z)>val(w), IIf(val(y)>val(x) Or val(z)>val(x), IIf(val(z)>val(y),val(z),val(y)),val(x)),val(w)) AS Max, -(IsNumeric(w)+IsNumeric(x)+IsNumeric(y)+IsNumeric(z)) AS Series FROM tblTest Asger -----Oprindelig meddelelse----- Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Asger Blond Sendt: 2. november 2007 22:17 Til: 'Access Developers discussion and problem solving' Emne: Re: [AccessD] ...gurus only If empty fields contain "" rather than nulls, then the fields are supposed to be text - and you could use a query like this: SELECT w, x, y, z, IIf([x]>[w] Or [y]>[x] Or [z]>[y],IIf([y]>[x] Or ([z]>[y] And [z]>[x]),IIf([z]>[y],[z],[y]),[x]),[w]) AS [Max], -(IsNumeric([w])+IsNumeric([x])+IsNumeric([y])+IsNumeric([z])) AS [Series] FROM tblTest Asger -----Oprindelig meddelelse----- Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af William Hindman Sendt: 2. november 2007 19:46 Til: Access Developers discussion and problem solving Emne: [AccessD] ...gurus only ...ok, you too, eh :) ...given: fields w x y z max series data1 1 1 1 2 data2 1 5 5 3 data3 1 4 3 3 4 data4 2 3 3 2 data5 1 1 1 1 1 4 data6 1 1 1 1) where max is the largest number in w/x/y/z and 2) where series is the total fields between the first one containing a number and the last containing a number 3) where empty fields contain "" rather than nulls ...two days and I've yet to come up with an answer to fill the max and series fields from the data in w/x/y/z. ...help! William -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com