Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

DAYS360(start_date,end_date,method) - function in Access?

Posted on 2004-04-27
10
Medium Priority
?
602 Views
Last Modified: 2007-12-19
Hi.

I have a table with two date colums 'From' and 'To', with beginning and end dates, obviously. ;)

Is it possible to create a third column called 'Days', which calculates the number of days' difference between the 'From' and 'To' columns?

I read something in the help file about DAYS360(start_date,end_date,method), but isn't that an Excel function?

Thanks!
R;^)
0
Comment
Question by:DarkDarling
  • 6
  • 3
10 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 10935747
Hi DarkDarling,

You can use the VBA function DateDiff to calculate the number of days between two dates,
e.g.

DateDiff("d", start_date, end_date)

See http://www.database-design-2000.com/dateDiff.htm for more information.

Yes, DAYS360 is an Excel (only) worksheet function that returns the number of days between two dates based on a 360-day year (twelve 30-day months).


BFN,

fp.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 10935815
DateDiff is what you use, like fanpages suggested

but I wouldnt recommend you create a days column, you will have to keep maintaining it each time you update the dates in the table and when u insert.

It is generally not good practice to put derved fields in the database

you should use it in your code, e.g. easy to use this function in a query as well,

0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 10935917
Hi,

Yes, thank you rockiroads, I meant to mention that!

There would be no need to store start, end, and days fields.

If you really did need to store a days value (perhaps to speed up processing or for immediate export to another data repository), you would only ever need to store either the start date, or the end date, not both, as you could calculate either date given the opposing date & the days difference!  [I hope that made sense]

BFN,

fp.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:DarkDarling
ID: 10935963
What I want it for is to create a chart showing energy use over periods of time... all I have are start and end dates, and thought it would be easier to create the chart from the number of days..... :)

R
0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 375 total points
ID: 10936000
Hi DarkDarling,

Yes, that's true, but as the number of days can be derived from the start & end dates, there is little value to storing the actual days alongside the other columns in your database, unless, there is a great number of rows (records) that are static.

That is, if the records that already exist are not going to change once written to your table, and the time taken to process each record & calculate the days difference before you chart the values is excessive in terms of processing &/or user-wait time.


BFN,

fp.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 10936091
Hi,

Thanks for the points by accepting my answer.

Were out combined answers not helpful enough?

I was curious why you graded as a 'B'.

BFN,

fp.
0
 

Author Comment

by:DarkDarling
ID: 10936158
They half-helped. :)

I ended up finding the rest on my own.

But thanks!

R
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 10936214
:)  OK.

Which half?  Then I know which half to let you know next time.

BFN,

fp.
0
 

Author Comment

by:DarkDarling
ID: 10936252
:)  You gave me the syntax...I figured out where to put it.

R
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 10936339
Ooh... expert yourself now, huh? ;)

Seriously, fair enough... we didn't get to the second half of your question before you graded it.

Have fun!

BFN,

fp.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question