Subtracting "Settle_Date" from "Deal_Date", how many days ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table:

ID Name Trade_Date Settle_Date Days
1 AA Company 20070905 20071005 ?
2 BB Company 20070905 20070910 ?

----------------------------------------------------------------------------------------
Question 1:

I need number of days between "Settle_Date" and "Trade_Date". So simply ,
how can i adjust the setting of "Settle_Date - Trade_Date".

(either using query or wizard)
----------------------------------------------------------------------------------------
Question 2:

Same question, but i have a holiday table. IF there's a holiday between
"Settle_Date" and "Deal_Date", that holiday should NOT be counted.


So i need a holiday Table, right ? How do i do it ? ^_^!
 
hi ali,
ID Name Trade_Date Settle_Date Days
1 AA Company 20070905 20071005 ?
2 BB Company 20070905 20070910 ?
I need number of days between "Settle_Date" and "Trade_Date". So simply ,
how can i adjust the setting of "Settle_Date - Trade_Date".
Click to expand...
You can simply subtract it:

Days = CLng(Settle_Date) - CLng(Trade_Date)

you may also take a look at the DateDiff() function in the OH.
Same question, but i have a holiday table. IF there's a holiday between
"Settle_Date" and "Deal_Date", that holiday should NOT be counted.
Click to expand...
This is a little bit more complex as you have to do some interval
calculations.
So i need a holiday Table, right ? How do i do it ? ^_^!
Click to expand...
Yes.


mfG
--> stefan <--
 
Stefan, thaks a lot first !

But, it's not working!

could you please tell me where to put those syntax ? how ? and steps .
Please~!! I really need it to be done asap!!

thank you so much
 
First, are your dates being in stored in a date field? Are both dates
always present in every record?

If the dates are stored as text you will need to convert them to dates if
you intend to do any date calculations.

DateDiff("d",Trade_Date,Settle_Date)

If the dates are strings, you can try
DateDiff("d",DateValue(Format(Trade_date,"@@@@\-@@\-@@")),DateValue(Format(Settle_Date,"@@@@\-@@\-@@")))

Calculating elapsed days and excluding holidays is more complex and I would
use a custom VBA function to do so.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
hi Ali,
But, it's not working!
could you please tell me where to put those syntax ? how ? and steps .
Please~!! I really need it to be done asap!!
Click to expand...
Create a new query. Add your table. Select the fields you like to
display. In an additional column put either

Days: CLng(Settle_Date) - CLng(Trade_Date)

or

Days: DateDiff("d", Trade_Date, Settle_Date)

As John wrote, if your fields are not stored as Date/Time fields in your
table then you need to convert them.


mfG
--> stefan <--
 
Thanks a lot both Stefan and John,

It works now!!! I have a new qustioin,

if Saturday and Sunday are holidays, they should NOT be counted, so how do i
create my holiday table to exclude holidays
 
Big thanks Stefan,

I've been reading the link you sent me. I jus wonder, is there any easier
way to do it by just using SQL Query ?

if so, please do let me know.

Thank you so much ! ^_^!
 
hi,
I've been reading the link you sent me. I jus wonder, is there any easier
way to do it by just using SQL Query ?
Click to expand...
Nope, not really, but you can use these functions in queries.


mfG
--> stefan <--
 
Back
Top