How to update value in a table field with date difference query

[hrdpgajjar]

HI all,
I have a table named farmer details, in this table i have three columns 1. App sent date and 2. wo date 3. App sent to wo date diff


now i need to calculate date difference between 1. App sent date to 2. wo date and need result in column 3. App sent to wo date diff


I know how to use "datediff" function in access but dont know how to update result in table directly.


thank you,

 

[Gasman]

I really hope they are not the real table and fieldnames. It is always recommended not to have spaces in them.
You do not need to update the table, as you can calculate it at any time using DateDiff().

 

[hrdpgajjar]

I really hope they are not the real table and fieldnames. It is always recommended not to have spaces in them.
You do not need to update the table, as you can calculate it at any time using DateDiff().

Thanks for the prompt reply. but as per my requirements. i must have to store the values of datediff in a saperate column so that i can export it to excel as and when required. is there any way to do this? thanks

 

[GaP42]

A query, based on the table, which includes the datediff calculation as a column, can be exported to excel. No need to store the calculation.
Many examples are available for exporting to excel: eg Export to Excel using vba

 

[Gasman]

Thanks for the prompt reply. but as per my requirements. i must have to store the values of datediff in a saperate column so that i can export it to excel as and when required. is there any way to do this? thanks

Well Excel can work out the difference anyway.
What happens if one or both of the dates get amended?, then the datediff field is no longer correct.
Calculate it and use a query as mentioned.

 

[LarryE]

HI all,
I have a table named farmer details, in this table i have three columns 1. App sent date and 2. wo date 3. App sent to wo date diff


now i need to calculate date difference between 1. App sent date to 2. wo date and need result in column 3. App sent to wo date diff


I know how to use "datediff" function in access but dont know how to update result in table directly.


thank you,

Use an Update query. And change your field names so they don't have spaces. AppSentDate or App_Sent_Date, for example.
Here's an explanation:
Update Queries | CustomGuide
Use the DataDiff() function in the Update To criteria box for the AppSentToWODateDateDiff field.
If you need to run the query using VBA code, then:

1. Name the Query. For example, UpdateDateSentQuery
2. Use DoCmd.OpenQuery "UpdateDateSentQuery", acViewNormal, acReadOnly in VBA to run the query.
   

 

[Pat Hartman]

Do not store this value. Period. It can easily be calculated in a query using data from a single row. When you export to Excel, export the query rather than the table.