Calculate data

[Mihail]

Hello !

In a table I have a field "DataStart" As Date.
In the second field I wish to enter a period (i.e. 2 years). The field name is "Period".

The question is:
What format is required for the second field (Period) in order to calculate the EndData field ( EndData = DataStart + Period) in a query.

Thank you in advance !

[NeoPa]

Mihail,

I'm sure you don't need me to tell you that storing all three values in the record is not Normalised ==> a bad idea. Assume I just have.

Nevertheless, whether it is saved away or simply calculated for other uses, the question still makes sense so I will deal with it here :

It is possible for two date values to be added together to make a third date value (A + B = C). However, Not all of them would then be correctly displayable as dates in the way we normally expect (m/d/yyyy or d/m/yyyy depending on location). Consider A = today (7/7/2011) and B = 2 days. A + B = C would leave C as 9 July 2011 clearly. If you were to display A and B using a format of "d mmmm yyyy" the result for A would be "7 July 2011" whereas the result for B would be "1 January 1900". Clearly not as intended. The result for C would be fine too of course ("9 July 2011").

This rather overlooks the fact that days are simpler than other elements to add. Add 2 months today and the date will move on 62 days, yet do the same on 7 January 2011 and the move will be a mere 59 days. This is what the DateAdd() function can help you with. You specify the number and type of period to progress.

This means you must decide what you want your setup to support, and how to manage that. Clearly, if you want anyone to be able to select the type of period, as well as the number of such periods, then you will need to ensure you have a way of storing both pieces of information in your solution. That may be by storing it as a string, or by having two fields that do the individual jobs. Until you really know what you want to do there's not much point trying to answer this question in any more detail.

[Mihail]

Thank you for reply, NeoPa. My english is (again) too poor.
Please let me explain again, maybe better:
YES: In the table I have only two fields: DataStart (As Date) and Period. I wish to use this information to calculate the EndData (= StartData + Period).
But I don't know what type of data is required for Period field.
I think (but I am not sure) that the period can be Byte (in order to reduce the size of DataBase.
In this moment I don't know if period will be Month or Years. My client will decide that later. This is the reason I ask you: I wish to progress with my database until my client take a decision. Maybe, if I make a lot of tests, I can find the answer myself. But is more efficient (for me) to ask here instead to reinvent the warm water.

[NeoPa]

If the client simply needs to tell you which type of period to include in the design then that is easily changed later on. If, on the other hand, they would need to choose on a per-item basis, then your design would need to handle them specifying both a value for the period as well as one for the type. This is a little more complex but fundamentally would follow along the same lines (using DateAdd()).

[Mihail]

Thnk you again NeoPa.
I understand from you that the field must be As Long (according with DateAdd() arguments).
Please confirm !

[NeoPa]

The (Value) field could certainly be Long, but frankly an Integer field would convert automatically for the function call so could be used instead if required. I see no reason to use anything other than Long though.

[Mihail]

Thank you, NeoPa !

[NeoPa]

A pleasure to help Mihail :-)