S/B Simple Nested IIF

[ConfusedNAccess]

I'm doing something wrong here..
looking at 3 date/time fields:
1)RedCalcDate
2)BlueCalcDate

I'm querying for the [lastcalc] or the most recent date out of the calc datefields for that record...and all records last calc date in the current month.. which I got that fine, but not I'm looking to show what type of calc for each corresponding date or both...

*example data*
redcalcdate: 4/1/2007 09:23:15
bluecalcdate: 4/7/2007 08:35:25

Iif((datevalue([redcalcdate]=[lastcalc] and datevalue([bluecalcdate]<>lastcalcdate,datevalue([redcalcdate]),datevalue([bluecalcdate] as CalcType
the above doesn't look right to me.. i'll just say what I'm trying to do...


I want to see the most recent calc type.. so
if redcalc = [lastcalc] then show red calc
if bluecalc = [lastcalc] then show blue calc
if redcalc and blue calc = lastcalc show both


lastcalc which is to show the most recent is defined as:

LastCalc: IIf(DateValue([redcalcdate])>DateValue([bluecalcDate]),DateValue([redcalc]),DateValue([bluecalcdate]))

how can i simplify all of this??? should Max be used instead??

 

[lespaul]

if redcalc and blue calc = lastcalc show both

can you show a "picture" of what you mean by the above "show both"

Are redcalc and bluecalc fields in your table?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ConfusedNAccess]

No i'm sorry.. maybe I can paint a better one though..
lets say.. query name is customercalculations


Customer redcalcdate bluecalcdate lastcalc type
john doe 4/12/2007 4/25/2007 4/25/2007 bluecalc
jane doe 4/7/2007 4/1/2007 4/7/2007 redcalc
joe shmoe 4/5/2007 4/5/2007 4/5/2007 both

 

[Darrylles]

Hi confused,

Try this:

iif(datevalue(redcalcdate)=lastcalc AND datevalue(bluecalcdate)=lastcalc,lastcalc,iif(datevalue(redcalcdate)=lastcalc, datevalue(redcalcdate),iif(datevalue(bluecalcdate)=lastcalc,datevalue(bluecalcdate),NULL))

This equates to:

IF redcalc AND bluecalc = lastcalc THEN show lastcalc (because BOTH are equal to lastcalc).
ELSE IF redcalc = lastcalc THEN show redcalc
ELSE IF bluecalc = lastcalc THEN show bluecalc
IF NONE OF THE ABOVE show NULL (nothing).

Compare the 'iif' statement to the 'IF' statement. Does this make more sense?

Regards,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.