Nearest Date

G

Guest

Is it possible to identify the nearest date??
The below code from a Text Box in a report, calculates a due date, but it
just returns the first one.
I'm trying to add to it - to say - nearest date to today.!!

Is this possible, I've tried adding "Date()" a few was with no luck...

Thanks
 
G

Guest

Any suggestions on adding your suggestion to this piece of code that
calculates my due date. I know this code will not mean much to you, but as
you can see it goes thru a lot of changes, to calculate the due date:

=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,14,0)))



Ofer Cohen said:
Try something like

=Dmax("[DateField]","[TableName]","[DateField] < = Date()")

--
Good Luck
BS"D


Dan @BCBS said:
Is it possible to identify the nearest date??
The below code from a Text Box in a report, calculates a due date, but it
just returns the first one.
I'm trying to add to it - to say - nearest date to today.!!

Is this possible, I've tried adding "Date()" a few was with no luck...

Thanks
Click to expand...
Click to expand...
 
G

Guest

To join two values in a text box you need to use & , it's better with a space
in between the values.

So it will look like

=Formula & " " & AnotherFormula

--
Good Luck
BS"D


Dan @BCBS said:
Any suggestions on adding your suggestion to this piece of code that
calculates my due date. I know this code will not mean much to you, but as
you can see it goes thru a lot of changes, to calculate the due date:

=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,14,0)))



Ofer Cohen said:
Try something like

=Dmax("[DateField]","[TableName]","[DateField] < = Date()")

--
Good Luck
BS"D


Dan @BCBS said:
Is it possible to identify the nearest date??
The below code from a Text Box in a report, calculates a due date, but it
just returns the first one.
I'm trying to add to it - to say - nearest date to today.!!

Is this possible, I've tried adding "Date()" a few was with no luck...

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I'm stuck!!! I'm trying to do what you said but I cannot figure out how to
add the "DMAX" When I add this it exceeds the allowed characters...
HELP

=IIf([tr_........ And [tr_extension]=True,14,0)))
=Dmax("[ReceivedDT]","[T_Main]","[ReceivedDT] < = Date()")





Ofer Cohen said:
To join two values in a text box you need to use & , it's better with a space
in between the values.

So it will look like

=Formula & " " & AnotherFormula

--
Good Luck
BS"D


Dan @BCBS said:
Any suggestions on adding your suggestion to this piece of code that
calculates my due date. I know this code will not mean much to you, but as
you can see it goes thru a lot of changes, to calculate the due date:

=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,14,0)))



Ofer Cohen said:
Try something like

=Dmax("[DateField]","[TableName]","[DateField] < = Date()")

--
Good Luck
BS"D


:

Is it possible to identify the nearest date??
The below code from a Text Box in a report, calculates a due date, but it
just returns the first one.
I'm trying to add to it - to say - nearest date to today.!!

Is this possible, I've tried adding "Date()" a few was with no luck...

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

You can always create two text boxes, one for each formula

=IIf([tr_........ And [tr_extension]=True,14,0)))
=Dmax("[ReceivedDT]","[T_Main]","[ReceivedDT] < = Date()")

Make them visible False

And then create another text box to combine the two, in the control source
=[TextBox1Name] & " " & [TextBox2Name]

--
Good Luck
BS"D


Dan @BCBS said:
I'm stuck!!! I'm trying to do what you said but I cannot figure out how to
add the "DMAX" When I add this it exceeds the allowed characters...
HELP

=IIf([tr_........ And [tr_extension]=True,14,0)))
=Dmax("[ReceivedDT]","[T_Main]","[ReceivedDT] < = Date()")





Ofer Cohen said:
To join two values in a text box you need to use & , it's better with a space
in between the values.

So it will look like

=Formula & " " & AnotherFormula

--
Good Luck
BS"D


Dan @BCBS said:
Any suggestions on adding your suggestion to this piece of code that
calculates my due date. I know this code will not mean much to you, but as
you can see it goes thru a lot of changes, to calculate the due date:

=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,14,0)))



:

Try something like

=Dmax("[DateField]","[TableName]","[DateField] < = Date()")

--
Good Luck
BS"D


:

Is it possible to identify the nearest date??
The below code from a Text Box in a report, calculates a due date, but it
just returns the first one.
I'm trying to add to it - to say - nearest date to today.!!

Is this possible, I've tried adding "Date()" a few was with no luck...

Thanks
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Per your suggestion, obviously I already have the first Text Box.
Step 2, create the 2nd text box like:
=DMax("[ReceivedDT]","[T_Main]","[ReceivedDT] < = Date()")

When I do just that, the compile runs the results on the report are #Error.
It does not give any reason why?

Any Suggestions???







Ofer Cohen said:
You can always create two text boxes, one for each formula

=IIf([tr_........ And [tr_extension]=True,14,0)))
=Dmax("[ReceivedDT]","[T_Main]","[ReceivedDT] < = Date()")

Make them visible False

And then create another text box to combine the two, in the control source
=[TextBox1Name] & " " & [TextBox2Name]

--
Good Luck
BS"D


Dan @BCBS said:
I'm stuck!!! I'm trying to do what you said but I cannot figure out how to
add the "DMAX" When I add this it exceeds the allowed characters...
HELP

=IIf([tr_........ And [tr_extension]=True,14,0)))
=Dmax("[ReceivedDT]","[T_Main]","[ReceivedDT] < = Date()")





Ofer Cohen said:
To join two values in a text box you need to use & , it's better with a space
in between the values.

So it will look like

=Formula & " " & AnotherFormula

--
Good Luck
BS"D


:

Any suggestions on adding your suggestion to this piece of code that
calculates my due date. I know this code will not mean much to you, but as
you can see it goes thru a lot of changes, to calculate the due date:

=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,14,0)))



:

Try something like

=Dmax("[DateField]","[TableName]","[DateField] < = Date()")

--
Good Luck
BS"D


:

Is it possible to identify the nearest date??
The below code from a Text Box in a report, calculates a due date, but it
just returns the first one.
I'm trying to add to it - to say - nearest date to today.!!

Is this possible, I've tried adding "Date()" a few was with no luck...

Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dmax for nearest date 7
Dmin for nearest date 1
rounding to nearest 5000 4
Earliest Date 5
Highlight date nearest to TODAY() 4
Highlight date nearest to TODAY() 1
nearest date 1
Rounding date formula results 3

Top