Auto Calculate Date

[cyliyu]

I am trying to convert my Excel spreadsheet database into Access.


There are many auto calculated in my Excel's cell based on another cell inputs and I would like to do the same in Access's Table.
One of them was the warranty date.
given the warranty, the date was 3 months base on the received date.
What should be the correct expression to use?

The ([InDate])+90 only based on 90 days.

e.g. 10 May 2020 InDate the warranty expired date should be 09 Aug 2020.

[moke123]

Use the DateAdd()

DateAdd ( interval, number, date )

https://www.techonthenet.com/access/...te/dateadd.php

[cyliyu]

Use the DateAdd()

DateAdd ( interval, number, date )

https://www.techonthenet.com/access/...te/dateadd.php

can this command used in table -> calculated expression?

[Bob Fitz]

Try using the DateAdd() function to calculate future or past dates. See: https://www.techonthenet.com/access/...te/dateadd.php
Something like:
To add 30 days
FutureDate = DateAdd("d",30,[YourStartDate])

To add 3 months
DateAdd("m",30,[YourStartDate])

However, calculations are not usually saved in a table but used whenever and wherever the calculation is required

[CJ_London]

I am trying to convert my Excel spreadsheet database into Access.

be aware that database structures (any database, including Access) are completely different to those in Excel. Simplistically excel has data 'short and wide', databases 'tall and thin'. Also Excel combines data and presentation (such as calculations of this nature) in one view, databases store data in tables and views are created using forms and reports via queries. If you try to apply excel thinking to a database you will soon find you have problems. If you have not done so already, google/bing 'normalisation'

@bob think that should be DateAdd("m",3,[YourStartDate])

can this command used in table -> calculated expression?

I don't believe so.

[cyliyu]

Thanks, Bob and Ajax.
Does that mean most of the auto calculations required in the table field will need the queries to do so?
i will Google and find out more normalisation.

[CJ_London]

no, it means you can't do them in the table - period.

[ArviLaanemets]

Normal and foolproof way to work with data in any Access DB is:

1. Users don't have access to tables (and as best, they can't even see any tables);
2. All manual data entry is done through forms;
3. All calculated data entry is done through form or form control events;
4. All reporting (printing or viewing) is done through reports, which are run from form control events.

[cyliyu]

Normal and foolproof way to work with data in any Access DB is:

1. Users don't have access to tables (and as best, they can't even see any tables);
2. All manual data entry is done through forms;
3. All calculated data entry is done through form or form control events;
4. All reporting (printing or viewing) is done through reports, which are run from form control events.

Thanks for your inputs.
Yes, I am halfway thru creating a Form to input data and save in the table.
Manage to auto calculate the warranty date in the Form menu and still looking at how can this warranty date saved in the table (if possible).

[CJ_London]

still looking at how can this warranty date saved in the table

you would use vba code in the received date after update event

warrantydate=dateadd("m",3,receiveddate)


however as nearly every responder has said - you do not need to (should not) store the value - potentially doing so can cause issues down the line if you change the received date

instead in the form control for the warrantydate just put

=dateadd("m",3,receiveddate)

[cyliyu]

Thanks, Ajax.

This is what I have put in the Form Control

=dateadd("m",3,receiveddate)-1

I will first not to save this info in the table.
When more data keyed in, especially the same serial number rejected and returned from warranty many times, I will see how this info can be display back in the form for the "supervisor" to view it.

[Bob Fitz]

be aware that database structures (any database, including Access) are completely different to those in Excel. Simplistically excel has data 'short and wide', databases 'tall and thin'. Also Excel combines data and presentation (such as calculations of this nature) in one view, databases store data in tables and views are created using forms and reports via queries. If you try to apply excel thinking to a database you will soon find you have problems. If you have not done so already, google/bing 'normalisation'

@bob think that should be DateAdd("m",3,[YourStartDate])


I don't believe so.

Thanks Ajax. You are of course quite right, it should be a 3, not 30 . These kinds of errors seem to be occurring with increasing frequency as I get older. I'd like to think that is due to poor eyesight rather thank mental deficiency but in truth it's probably due to both .

[moke123]

I agree with everything said about not storing the calculated date in a table as it can always be calculated on demand. I like to think long term when it comes to such things and would suggest that you do store the length of the warranty as a field in your table. I dont know what your business is but I make this suggestion only because over time the length of the warranty may change or perhaps you may offer an extended warranty in the future. If you hardcode a 3 month warranty and it changes you would have to re-code everything and that would effect existing records. Its much like tax rates or hourly rates that change over time.

[cyliyu]

The warranty period has been fixed at 3 months since the project started in 18 years ago.- I joined 3 years ago and trying my very best to help to set up a tracking system, RMA return was one of them.
I have the system up and running 1 year ago but in Excel and would like to have it set up in Access.
I will try to avoid storing the calculated date/value in a table - Access was new to me. Thanks.