By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,233 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

How do I add a calculated field that calculates a number of days?

P: 1
I need to calculate a shipping efficency. I have two sets of data, OrderDate and ShipDate, I need to Figure out how long it took each order to ship. Essientially I need to minus the Ship date from the Order date in a new field, entitiled "DaysToShip". I'm new to access, so thank you for the help!!

P.s. If you need more information or I'm not being clear please let me know.
Apr 27 '12 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Are these two sets of data fields in a single table? The table name would help if so. I'm also assuming you want to create a query to show both the table fields and the calculated field [DaysToShip]. If so, your query would look something like :
Expand|Select|Wrap|Line Numbers
  1. SELECT [OrderDate]
  2.      , [ShipDate]
  3.      , DateDiff('d',[OrderDate],[ShipDate]) AS [DaysToShip]
  4. FROM   [YourTable]
Apr 27 '12 #2

P: 21
As you wand to minus the shipped date I think this code will help you
Expand|Select|Wrap|Line Numbers
  1. SELECT [OrderDate]
  2. , [ShipDate]
  3. , DateDiff('dd',[OrderDate],[ShipDate]-1) AS [DaysToShip]
  4. FROM [YourTable]
In Access Query Desing View Try This code, It may work
Expand|Select|Wrap|Line Numbers
  1. select DateDiff(dd,OrderDate,ShippedDate)-1
Apr 29 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
@Roshan.
As you have simply used my suggested code with a couple of minor changes let me just deal with the changes :
  1. You changed 'd' to 'dd'. This simply makes the call fail as 'dd' is not a valid parameter to DateDiff().
  2. You subtracted one from the calculation. This simply causes the calculation to give an incorrect result.
In your second suggestion you took dd out of the quotes. This simply makes no sense and cannot possibly work (without a predefined object / field named dd which is not the case here).
Apr 29 '12 #4

P: 21
The sugestion work in case Of MySql Check the code.Thanks for comment
Apr 29 '12 #5

NeoPa
Expert Mod 15k+
P: 31,186
This is an Access question in an Access forum and it doesn't work in Access. I'm pretty sure nothing else is relevant.
Apr 29 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.