Date calculation

[Dan @BCBS]

This code calculates the age of an entry - but only in days!!!!
Counting days it works fine, but I need it to also show hours.
Example: 2 hours should show .2 not 2...

Help

AgeErisa:
IIf([tr_inquirytype]="in",Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,Null,IIf([tr_closedate]
Is
Null,IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],Now()),Date()-[tr_date_timercvd_hoi]),IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],[tr_closedate]),[tr_closedate]-[TR_DATE_TIMERCVD_HOI]))))

 

[OfficeDev18 via AccessMonster.com]

Please show the exact format of a complete message: days, hours, etc., so we
can have an idea what you need and how to get it.

This code calculates the age of an entry - but only in days!!!!
Counting days it works fine, but I need it to also show hours.
Example: 2 hours should show .2 not 2...

Help

AgeErisa:
IIf([tr_inquirytype]="in",Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,Null,IIf([tr_closedate]
Is
Null,IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],Now()),Date()-[tr_date_timercvd_hoi]),IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],[tr_closedate]),[tr_closedate]-[TR_DATE_TIMERCVD_HOI]))))

 

[Dan @BCBS]

1. Query- qryCases3
DueDateERISA:
Format(IIf([tr_inquirytype]="in",Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,Null,IIf([tr_expedited]=True,DateAdd("h",72,[TR_DATE_TIMERCVD_HOI]),IIf([tr_product]
In ("HO","CM") And [tr_CaseType] In
("C","G"),[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_product] In ("HO","CM") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In ("HO","CM")
And [tr_InquiryType]="AD",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product]="MM" And
[tr_InquiryType]="GR" Or
[tr_CaseType]="G",[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_inquirytype]="OD",[tr_date_timercvd_hoi]+30,IIf([tr_product]="MM"
And [tr_CaseType]="C",[TR_DATE_TIMERCVD_HOI]+60,IIf([tr_product]="MM" And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+30))))))))))+IIf([tr_casetype]="P"
And [tr_product]="MM" And [tr_extension]=True,14,0),"yyyy/mm/dd")


2. Query #2
SELECT qryCases3.*
FROM qryCases3
ORDER BY qryCases3.DueDateERISA, qryCases3.AgeErisa;

3. Report
=IIf([TR_Expedited]=True,"H"," D")

Results:
Will show number of days 1 D
I need to add hours like .2 D (or 2 H) anyway I can identify hours...

Thanks

Please show the exact format of a complete message: days, hours, etc., so we
can have an idea what you need and how to get it.

This code calculates the age of an entry - but only in days!!!!
Counting days it works fine, but I need it to also show hours.
Example: 2 hours should show .2 not 2...

Help

AgeErisa:
IIf([tr_inquirytype]="in",Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,Null,IIf([tr_closedate]
Is
Null,IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],Now()),Date()-[tr_date_timercvd_hoi]),IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],[tr_closedate]),[tr_closedate]-[TR_DATE_TIMERCVD_HOI]))))

 

[Dan @BCBS]

A few days ago I added all the code.
My original message did show the complete format. The .2 D is the complete
format..

""""" I need to add hours like .2 D (or 2 H) anyway I can identify hours
""""""""

Again, the calculation works fine if it's 1 or more days old... But I need
to calculate the hours if it's under one full day...

Example again: 3 days old will read " 3 D "
Anything under 24 hours the number makes no sense: 2 hours may show " 0.5 D
" or " 7 H "....

Help???

1. Query- qryCases3
DueDateERISA:
Format(IIf([tr_inquirytype]="in",Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,Null,IIf([tr_expedited]=True,DateAdd("h",72,[TR_DATE_TIMERCVD_HOI]),IIf([tr_product]
In ("HO","CM") And [tr_CaseType] In
("C","G"),[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_product] In ("HO","CM") And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product] In ("HO","CM")
And [tr_InquiryType]="AD",[TR_DATE_TIMERCVD_HOI]+15,IIf([tr_product]="MM" And
[tr_InquiryType]="GR" Or
[tr_CaseType]="G",[TR_DATE_TIMERCVD_HOI]+30,IIf([tr_inquirytype]="OD",[tr_date_timercvd_hoi]+30,IIf([tr_product]="MM"
And [tr_CaseType]="C",[TR_DATE_TIMERCVD_HOI]+60,IIf([tr_product]="MM" And
[tr_CaseType]="P",[TR_DATE_TIMERCVD_HOI]+30))))))))))+IIf([tr_casetype]="P"
And [tr_product]="MM" And [tr_extension]=True,14,0),"yyyy/mm/dd")


2. Query #2
SELECT qryCases3.*
FROM qryCases3
ORDER BY qryCases3.DueDateERISA, qryCases3.AgeErisa;

3. Report
=IIf([TR_Expedited]=True,"H"," D")

Results:
Will show number of days 1 D
I need to add hours like .2 D (or 2 H) anyway I can identify hours...

Thanks

Please show the exact format of a complete message: days, hours, etc., so we
can have an idea what you need and how to get it.

This code calculates the age of an entry - but only in days!!!!
Counting days it works fine, but I need it to also show hours.
Example: 2 hours should show .2 not 2...

Help

AgeErisa:
IIf([tr_inquirytype]="in",Null,IIf([TR_DATE_TIMERCVD_HOI]<#7/1/2002#,Null,IIf([tr_closedate]
Is
Null,IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],Now()),Date()-[tr_date_timercvd_hoi]),IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],[tr_closedate]),[tr_closedate]-[TR_DATE_TIMERCVD_HOI]))))

 

[OfficeDev18 via AccessMonster.com]

Even after reading your code several times, I can't for the life of me tell
which variable stores the number that is your final answer. Be that as it may,
the way around that is:

FinalDisplay: Format(VariableName, IIf(VariableName < 1.00, "Long Time",
"yyyy/mm/dd"))

The reason is that a decimal number less than 1 denotes a partial day, and a
number 1 or above denotes a full day or more.

Sam

A few days ago I added all the code.
My original message did show the complete format. The .2 D is the complete
format..

""""" I need to add hours like .2 D (or 2 H) anyway I can identify hours
""""""""

Again, the calculation works fine if it's 1 or more days old... But I need
to calculate the hours if it's under one full day...

Example again: 3 days old will read " 3 D "
Anything under 24 hours the number makes no sense: 2 hours may show " 0.5 D
" or " 7 H "....

Help???

1. Query- qryCases3
DueDateERISA:

[quoted text clipped - 36 lines]

Is
Null,IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],Now()),Date()-[tr_date_timercvd_hoi]),IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],[tr_closedate]),[tr_closedate]-[TR_DATE_TIMERCVD_HOI]))))

 

[Dan @BCBS]

Wow, almost perfect, thanks..
But my original format had "H" or "D" example: .2 D (or 2 H)

I'm trying to add something like =IIf([TR_Expedited]=True,"H"," D")
But it's not working..

Any suggestions...
Thanks

Even after reading your code several times, I can't for the life of me tell
which variable stores the number that is your final answer. Be that as it may,
the way around that is:

FinalDisplay: Format(VariableName, IIf(VariableName < 1.00, "Long Time",
"yyyy/mm/dd"))

The reason is that a decimal number less than 1 denotes a partial day, and a
number 1 or above denotes a full day or more.

Sam

A few days ago I added all the code.
My original message did show the complete format. The .2 D is the complete
format..

""""" I need to add hours like .2 D (or 2 H) anyway I can identify hours
""""""""

Again, the calculation works fine if it's 1 or more days old... But I need
to calculate the hours if it's under one full day...

Example again: 3 days old will read " 3 D "
Anything under 24 hours the number makes no sense: 2 hours may show " 0.5 D
" or " 7 H "....

Help???

1. Query- qryCases3
DueDateERISA:

[quoted text clipped - 36 lines]

Is
Null,IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],Now()),Date()-[tr_date_timercvd_hoi]),IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],[tr_closedate]),[tr_closedate]-[TR_DATE_TIMERCVD_HOI]))))

 

[Dan @BCBS]

This does the calculation correctly -
Format(VariableName, IIf(VariableName < 1.00, "Long Time","yyyy/mm/dd"))

But could you please help me add the H or D..
My original final display was =IIf([TR_Expedited]=True,"H"," D")

Greatly appreciative:
Dan

Wow, almost perfect, thanks..
But my original format had "H" or "D" example: .2 D (or 2 H)

I'm trying to add something like =IIf([TR_Expedited]=True,"H"," D")
But it's not working..

Any suggestions...
Thanks

Even after reading your code several times, I can't for the life of me tell
which variable stores the number that is your final answer. Be that as it may,
the way around that is:

FinalDisplay: Format(VariableName, IIf(VariableName < 1.00, "Long Time",
"yyyy/mm/dd"))

The reason is that a decimal number less than 1 denotes a partial day, and a
number 1 or above denotes a full day or more.

Sam

A few days ago I added all the code.
My original message did show the complete format. The .2 D is the complete
format..

""""" I need to add hours like .2 D (or 2 H) anyway I can identify hours
""""""""

Again, the calculation works fine if it's 1 or more days old... But I need
to calculate the hours if it's under one full day...

Example again: 3 days old will read " 3 D "
Anything under 24 hours the number makes no sense: 2 hours may show " 0.5 D
" or " 7 H "....

Help???

1. Query- qryCases3
DueDateERISA:
[quoted text clipped - 36 lines]
Is
Null,IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],Now()),Date()-[tr_date_timercvd_hoi]),IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],[tr_closedate]),[tr_closedate]-[TR_DATE_TIMERCVD_HOI]))))

 

[OfficeDev18 via AccessMonster.com]

That's the simple part. Since all format() results are string data by
definition, simply tweak your variable assignment, as follows:

SomeStringVar=Format(VariableName, IIf(VariableName < 1.00, "Long Time",
"yyyy/mm/dd")) & IIf(VariableName < 1.00, "H","D")

Good luck, Dan

Sam

This does the calculation correctly -
Format(VariableName, IIf(VariableName < 1.00, "Long Time","yyyy/mm/dd"))

But could you please help me add the H or D..
My original final display was =IIf([TR_Expedited]=True,"H"," D")

Greatly appreciative:
Dan

Wow, almost perfect, thanks..
But my original format had "H" or "D" example: .2 D (or 2 H)

[quoted text clipped - 38 lines]

Is
Null,IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],Now()),Date()-[tr_date_timercvd_hoi]),IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],[tr_closedate]),[tr_closedate]-[TR_DATE_TIMERCVD_HOI]))))

 

[Dan @BCBS]

Sooo Close:
When I put in the following String Format - it will not save the "H","D" -
it keeps changing it to "h\,d)

WHY/Help...
Thanks



"SomeStringVar=Format (TR_Expedited, IIf((TR_Expedited < 1.00, Long Time,
yyyy/mm/dd))& IIf(TR_Expedited < 1.00, "H","D")

That's the simple part. Since all format() results are string data by
definition, simply tweak your variable assignment, as follows:

SomeStringVar=Format(VariableName, IIf(VariableName < 1.00, "Long Time",
"yyyy/mm/dd")) & IIf(VariableName < 1.00, "H","D")

Good luck, Dan

Sam

This does the calculation correctly -
Format(VariableName, IIf(VariableName < 1.00, "Long Time","yyyy/mm/dd"))

But could you please help me add the H or D..
My original final display was =IIf([TR_Expedited]=True,"H"," D")

Greatly appreciative:
Dan

Wow, almost perfect, thanks..
But my original format had "H" or "D" example: .2 D (or 2 H)

[quoted text clipped - 38 lines]

Is
Null,IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],Now()),Date()-[tr_date_timercvd_hoi]),IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],[tr_closedate]),[tr_closedate]-[TR_DATE_TIMERCVD_HOI]))))

 

[OfficeDev18 via AccessMonster.com]

Too many parentheses are binding the wrong values. Besides, you didn't
balance the opening and closing parentheses. It's obvious this isn't a copy-
and-paste of your actual code. Change

SomeStringVar=Format (TR_Expedited, IIf((TR_Expedited < 1.00, Long Time,
yyyy/mm/dd))& IIf(TR_Expedited < 1.00, "H","D")

to

SomeStringVar=Format (TR_Expedited, IIf(TR_Expedited < 1.00, "Long Time",
"yyyy/mm/dd"))& IIf(TR_Expedited < 1.00, "H","D")

The format must be in double quotes ("Long Time", for example, or "yyy...")

Sam

By the way, beware of using the continuation character ("_") when your
statement spans more than one line. Its incorrect use can wreak havoc!!

Sooo Close:
When I put in the following String Format - it will not save the "H","D" -
it keeps changing it to "h\,d)

WHY/Help...
Thanks

"SomeStringVar=Format (TR_Expedited, IIf((TR_Expedited < 1.00, Long Time,
yyyy/mm/dd))& IIf(TR_Expedited < 1.00, "H","D")

That's the simple part. Since all format() results are string data by
definition, simply tweak your variable assignment, as follows:

[quoted text clipped - 20 lines]

Is
Null,IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],Now()),Date()-[tr_date_timercvd_hoi]),IIf([tr_expedited]=True,DateDiff("h",[TR_DATE_TIMERCVD_HOI],[tr_closedate]),[tr_closedate]-[TR_DATE_TIMERCVD_HOI]))))