[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2004-04-27
10
Medium Priority
?
600 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

649 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