[AccessD] ...gurus only

...tks gustav ...I'll give this a shot ...suddenly my cup runneth over 
...ain't no place like AccessD :)
...now if only I could learn to swallow my pride and just ask before 
spending days running in circles.

William

----- Original Message ----- 
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Friday, November 02, 2007 4:16 PM
Subject: Re: [AccessD] ...gurus only


> Hi William
>
> 1) No problem.
>
> 2) Sorry, now I understand.
>  Use this for Series:
>
>      IIf([First]+[Last]=0,
>        0,
>        1+[Last]-[First]) AS
>    Series
>
> 3) If you expect "" for empty fields, values must be strings. That's why I 
> use Val() to convert to Integers.
>
> /gustav
>
>
>>>> wdhindman at dejpolsystems.com 02-11-2007 21:05:02 >>>
> ...thanks gustav but:
> ...I'm busted again ...there was an error in the data3 max ...sigh
> ...given:
>
> fields        w        x        y        z        max        series
> data1        1        1                            1             2
> data2        1                  5                  5             3
> data3        1        4                  3        4             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 number of fields between the first one
> containing a
> number and the last containing a number, not the total of the field
> contents.
> 3) where w/x/y/z are long integers for some stupid reason since it will
> allways be a single digit
>
> ...I ask for guru help and everyone starts exposing why I need it :(
> ...there is a desperate shortage of caffiene in my bloodstream ...that's 
> my
> story and I'm sticking to it :)
>
> William
>
> ----- Original Message ----- 
> From: "Gustav Brock" <Gustav at cactus.dk>
> To: <accessd at databaseadvisors.com>
> Sent: Friday, November 02, 2007 3:40 PM
> Subject: Re: [AccessD] ...gurus only
>
>
>> Hi William
>>
>> Here is one method using Choose():
>>
>>  SELECT
>>    ID,
>>    w,
>>    x,
>>    y,
>>    z,
>>      IIf(Val("" & [w])>Val("" & [x]),
>>        Val("" & [w]),
>>        Val("" & [x])) AS Max12,
>>      IIf(Val("" & [y])>Val("" & [z]),
>>        Val("" & [y]),
>>        Val("" & [z])) AS Max34,
>>      IIf(Val("" & [w])>0,
>>        1,
>>        IIf(Val("" & [x])>0,
>>          2,
>>          IIf(Val("" & [y])>0,
>>            3,
>>            IIf(Val("" & [z])>0,
>>              4,
>>              0)))) AS
>>    First,
>>      IIf(Val("" & [z])>0,
>>        4,
>>        IIf(Val("" & [y])>0,
>>          3,
>>          IIf(Val("" & [x])>0,
>>            2,
>>            IIf(Val("" & [w])>0,
>>              1,
>>              0)))) AS
>>    Last,
>>      IIf([Max12]>[Max34],
>>        [Max12],
>>        [Max34]) AS
>>    Max,
>>      IIf([First]=[Last],
>>        Val(Choose([First],[w],[x],[y],[z])),
>>
>> Val(Choose([First],[w],[x],[y],[z]))+Val(Choose([Last],[w],[x],[y],[z])))
>> AS
>>    Series
>>  FROM
>>    tblGuru;
>>
>>
>> This will return, which differs from your example but is according to 
>> your
>> specification:
>>
>> Max Series
>> 1 2
>> 5 6
>> 4 4
>> 3 5
>> 1 2
>> 1 1
>>
>> /gustav
>>
>>>>> wdhindman at dejpolsystems.com 02-11-2007 19:45:35 >>>
>> ...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