If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculate difference between to dates
I have a table with 2 date fields: [Open Date] and [Close time].
Both field have the following format: dd-mm-yyyy hh:nn. How can I calculate the difference in days between these 2 fields and how can I display this on a report where I want to have 4 colums like: recordnummer - Open date - Close time - difference (or time 'record open') Can any one help me? |
#2
|
|||
|
|||
Calculate difference between to dates
The difference should not be stored in the table. (Calculated values rarely,
if ever, should be stored) Instead, create a query based on the table, and add a computed field to the query. Use the query wherever you would otherwise have used the table. To add the computed field in the graphical query builder, type the following into an empty cell on the Field: row: Difference: DateDiff("n", [Open Date], [Close Time]) That will return the difference between the two times in minutes. (using "s" instead of "n" will return the difference in seconds) It is not possible to return the difference in, say, hh:nn format. To do that, you'd need create a function to convert from total minutes (or total seconds) to hh:nn (or hh:nn:ss) format. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Clemens" wrote in message ... I have a table with 2 date fields: [Open Date] and [Close time]. Both field have the following format: dd-mm-yyyy hh:nn. How can I calculate the difference in days between these 2 fields and how can I display this on a report where I want to have 4 colums like: recordnummer - Open date - Close time - difference (or time 'record open') Can any one help me? |
#3
|
|||
|
|||
Calculate difference between to dates
"Clemens" wrote in message ... I have a table with 2 date fields: [Open Date] and [Close time]. Both field have the following format: dd-mm-yyyy hh:nn. How can I calculate the difference in days between these 2 fields and how can I display this on a report where I want to have 4 colums like: recordnummer - Open date - Close time - difference (or time 'record open') If you look at the DateDiff function documentation in the Help system, you can see your various options. If you want to calculate days, it would be DateDiff("d",OpenDate,CloseTime) It will ignore the hours and minutes. |
Thread Tools | |
Display Modes | |
|
|