434,960 Members | 2,241 Online
Need help? Post your question and get tips & solutions from a community of 434,960 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
5 Replies

 Expert Mod 15k+ P: 31,492 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 SELECT [OrderDate]      , [ShipDate]      , DateDiff('d',[OrderDate],[ShipDate]) AS [DaysToShip] 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 SELECT [OrderDate] , [ShipDate] , DateDiff('dd',[OrderDate],[ShipDate]-1) AS [DaysToShip] FROM [YourTable] In Access Query Desing View Try This code, It may work Expand|Select|Wrap|Line Numbers select DateDiff(dd,OrderDate,ShippedDate)-1 Apr 29 '12 #3

 Expert Mod 15k+ P: 31,492 @Roshan. As you have simply used my suggested code with a couple of minor changes let me just deal with the changes :You changed 'd' to 'dd'. This simply makes the call fail as 'dd' is not a valid parameter to DateDiff(). 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

 Expert Mod 15k+ P: 31,492 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