Thanks - re: What date was the 1st Tuesday of last month . . .

[Latimer, Richard]

Thank you to all who took the time to respond. 

I have adapted the extremele elegant code supplied by Paul Howard and it
works exactly the way I want !

I must admit it took a while to get my head around how it worked and I ended
up adding a lot of comments so I can figure it out in the future! For what
it's worth here's how I implemented it (note that on the AS400 the date is
stored as an 8 digit number yyymmdd and can be treated as a number or a
date) :

ACCESS control_file                                               
                                                                  
; The report is expected to return movement data for              
; "the 1st Tuesday of last month". This code derives the          
; corresponding date to use in the eventual choose statement.     
; STEP 1 is to find the date of the "1st day of last month".      
; The last day of the month is on the control file so start       
; from there.                                                     
; STEP 2 is to find out which day of the week the that was.       
; Dividing the day count by 7 and reviewing the remainder (MOD)   
; gives the weekday where 0 = Sunday , 1 = Monday etc             
; STEP 3 is to calculate how many days to add to the "1st" to     
; get to the next Tuesday. The INDEX function returns the position
; in the string of each weekday, this number is then added to the 
; 1st of the month. Note that Tuesday (day 2) is not needed in    
; the lookup string . . .                                         
 

DEFINE d_days_1st num*6 &                                        
    = DAYS(FLOOR(dte_current_monthend / 100)*100) + 1            
                                                                 
DEFINE d_dte_tue  DATE &                                         
    = DATE(d_days_1st + INDEX('106543',CCON(MOD(d_days_1st,7)))) 



many thanks
Richard 

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.