[Solved] Subtracting Date fields

[AlanE]

Hi, I'm a newbie, but can't find an answer to this question in Help or on the forum. I've created a database with 10 fields. I'll be entering data directly and don't need to use a form. I want to add/subtract one date field from another to generate a 'days between' number. In my Basefile, I will be entering a 'start date' and would like Base to automatically calculate and enter the 'end date' into an 'end date' field.

Example, Field Name is 'Invoice Date' which I enter manually. Another field is 'Payment Due Date' which I want Base to calculate as 'Invoice Date' + 10
In Calc, if I manually enter a date in cell B2 (the invoice date), then I could put the following formula in cell C2: +B2+10 and the output would be 10 days later than the date in B2.

My assumption is I would need to enter a 'default value' formula in the 'Payment Due Date' under Field Properties to do the calculation, but I can't figure out how to indicate the reference field/cell 'Invoice Date.'

Thank you in advance for your help

[Villeroy]

If the status bar of your database window looks like this:

then you are woking with a HSQLDB of version 1.8. This is a rather outdated database engine and documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html
Under built-in functions you find the DATEDIFF function which calculates
DT1 - DT2 => N
SELECT * , Datediff('day', "DT1", "DT2" AS "Days" FROM "Table X" selects all the columns plus a column "Days" calculated from the difference in days between columns "DT1" and "DT2"
where DT1 and DT2 are date, time or timestamp values and N is a resulting number of days, months, years, hours, minutes or seconds.
This database engine does not include any DATEADD function to calculate D1 + N => D2

[UnklDonald418]

I'll be entering data directly and don't need to use a form.

Generally, not a good plan. Forms give base much of it's power.

I will be entering a 'start date' and would like Base to automatically calculate and enter the 'end date' into an 'end date' field.

Storing a value like 'end date' that can be calculated at run time is a normalization violation and can lead to inconsistent data, i.e. if the 'start date' is changed the stored 'end date' will likely be wrong.

If you use a form for fields such as 'Invoice Date' then use a query like Villeroy recommended as the data source for a SubForm, you can display the correct 'Payment Due Date' even if 'Invoice Date' is changed.

[AlanE]

"Generally, not a good plan. Forms give base much of it's power."

[AlanE]

Generally, not a good plan. Forms give base much of it's power.