Date Format

[Dan @BCBS]

This is an extension of Subject "Two PCs Diff Results"

I'm trying to add this code to the below code just before the FROM:
"\#mm\/dd\/yyyy\#") AS DueDate, tblTrackingData.TR_CLOSEDATE

SELECT tblTrackingData.TR_GRIEVANCECOORDINATOR,
IIf([tr_24expedited]=True,DateAdd("h",24,[TR_DATE_TIMERCVD_HOI]),IIf([tr_expedited]=True,DateAdd("h",72,[TR_DATE_TIMERCVD_HOI]),IIf([tr_product]
In ("HO") And [tr_CaseType] In
("C","G"),[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_product] In ("HO") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In ("HO") And
[tr_InquiryType]="AD",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In
("MM","MD","MP","AY") And [tr_InquiryType]="GR" Or
[tr_CaseType]="G",[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_inquirytype]="OD",[tr_date_timercvd_hoi]+30,IIf([tr_inquirytype]="RD",[tr_date_timercvd_hoi]+7,IIf([tr_product]
In ("MM","MD","MP","AY") And
[tr_CaseType]="C",[TR_DATE_TIMERCVD_HOI]+60,IIf([tr_product] In
("MM","MD","MP","AY") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+30))))))))))+IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,14,0) AS DueDate, tblTrackingData.TR_CLOSEDATE
FROM tblTrackingData
WHERE (((tblTrackingData.TR_CLOSEDATE) Is Null));

Thanks

 

[Ofer Cohen]

But this IIf return 14 or 0 As DueDate, how can you format this as date?

IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,14,0) AS DueDate

 

[John W. Vinson]

But this IIf return 14 or 0 As DueDate, how can you format this as date?

IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,14,0) AS DueDate

simple... 0 will be #12/30/1899# and 14 will be #1/12/1900#... <g>

John W. Vinson [MVP]

 

[Dan @BCBS]

I'm trying to code: If casetype in ... and product in ... and extension =
true Then add 14 days, else 0...

This has been caclulating the date correctly but obviously it's being
formatted as a date. How would you create these 3 conditions to add 14
days????


IIf([tr_casetype] In ("C","P","G") And [tr_product] In ("MM","MD","MP","AY")
And [tr_extension]=True,14,0)))


Thanks

But this IIf return 14 or 0 As DueDate, how can you format this as date?

IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,14,0) AS DueDate

simple... 0 will be #12/30/1899# and 14 will be #1/12/1900#... <g>

John W. Vinson [MVP]

 

[Dan @BCBS]

I tried this but the order remains correct on only my PC and different on
every other PC.

........+30,IIf([tr_casetype] In ("C","P","G") And [tr_product] In
("MM","MD","MP","AY") And [tr_extension]=True,[TR_DATE_TIMERCVD_HOI]+14


Any idea what is going on???
Here is the entire code.

DueDate: IIf([tr_inquirytype] In
("IN","NC"),Null,IIf([TR_DATE_TIMERCVD_HOI]<#07/01/2002#,"N/A",IIf([tr_24expedited]=True,DateAdd("h",24,[TR_DATE_TIMERCVD_HOI]),IIf([tr_expedited]=True,DateAdd("h",72,[TR_DATE_TIMERCVD_HOI]),IIf([tr_product]
In ("HO") And [tr_CaseType] In
("C","G"),[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_product] In ("HO") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In ("HO") And
[tr_InquiryType]="AD",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In
("MM","MD","MP","AY") And [tr_InquiryType]="GR" Or
[tr_CaseType]="G",[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_inquirytype]="OD",[tr_date_timercvd_hoi]+30,IIf([tr_inquirytype]="RD",[tr_date_timercvd_hoi]+7,IIf([tr_product]
In ("MM","MD","MP","AY") And
[tr_CaseType]="C",[TR_DATE_TIMERCVD_HOI]+60,IIf([tr_product] In
("MM","MD","MP","AY") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,[TR_DATE_TIMERCVD_HOI]+14)))))))))))))

I'm trying to code: If casetype in ... and product in ... and extension =
true Then add 14 days, else 0...

This has been caclulating the date correctly but obviously it's being
formatted as a date. How would you create these 3 conditions to add 14
days????


IIf([tr_casetype] In ("C","P","G") And [tr_product] In ("MM","MD","MP","AY")
And [tr_extension]=True,14,0)))


Thanks

But this IIf return 14 or 0 As DueDate, how can you format this as date?

IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,14,0) AS DueDate

simple... 0 will be #12/30/1899# and 14 will be #1/12/1900#... <g>

John W. Vinson [MVP]

 

[Ofer Cohen]

To which field you want to add the days? for the example let say [FieldName]

Try
DateAdd("d",IIf([tr_casetype] In ("C","P","G") And [tr_product] In
("MM","MD","MP","AY") And [tr_extension]=True,14,0),[FieldName]) AS DueDate

Or
[FieldName] + IIf([tr_casetype] In ("C","P","G") And [tr_product] In
("MM","MD","MP","AY") And [tr_extension]=True,14,0) AS DueDate
--
Good Luck
BS"D

I tried this but the order remains correct on only my PC and different on
every other PC.

.......+30,IIf([tr_casetype] In ("C","P","G") And [tr_product] In
("MM","MD","MP","AY") And [tr_extension]=True,[TR_DATE_TIMERCVD_HOI]+14


Any idea what is going on???
Here is the entire code.

DueDate: IIf([tr_inquirytype] In
("IN","NC"),Null,IIf([TR_DATE_TIMERCVD_HOI]<#07/01/2002#,"N/A",IIf([tr_24expedited]=True,DateAdd("h",24,[TR_DATE_TIMERCVD_HOI]),IIf([tr_expedited]=True,DateAdd("h",72,[TR_DATE_TIMERCVD_HOI]),IIf([tr_product]
In ("HO") And [tr_CaseType] In
("C","G"),[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_product] In ("HO") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In ("HO") And
[tr_InquiryType]="AD",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In
("MM","MD","MP","AY") And [tr_InquiryType]="GR" Or
[tr_CaseType]="G",[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_inquirytype]="OD",[tr_date_timercvd_hoi]+30,IIf([tr_inquirytype]="RD",[tr_date_timercvd_hoi]+7,IIf([tr_product]
In ("MM","MD","MP","AY") And
[tr_CaseType]="C",[TR_DATE_TIMERCVD_HOI]+60,IIf([tr_product] In
("MM","MD","MP","AY") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,[TR_DATE_TIMERCVD_HOI]+14)))))))))))))

I'm trying to code: If casetype in ... and product in ... and extension =
true Then add 14 days, else 0...

This has been caclulating the date correctly but obviously it's being
formatted as a date. How would you create these 3 conditions to add 14
days????


IIf([tr_casetype] In ("C","P","G") And [tr_product] In ("MM","MD","MP","AY")
And [tr_extension]=True,14,0)))


Thanks

On Wed, 30 May 2007 15:51:01 -0700, Ofer Cohen

But this IIf return 14 or 0 As DueDate, how can you format this as date?

IIf([tr_casetype] In
("C","P","G") And [tr_product] In ("MM","MD","MP","AY") And
[tr_extension]=True,14,0) AS DueDate

simple... 0 will be #12/30/1899# and 14 will be #1/12/1900#... <g>

John W. Vinson [MVP]