Page 1 of 1

[Solved] Subtracting Date fields

Posted: Wed May 12, 2021 8:33 am
by 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

Re: Subtracting Date fields

Posted: Wed May 12, 2021 10:36 am
by Villeroy
If the status bar of your database window looks like this:
Image
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

Re: Subtracting Date fields

Posted: Thu May 13, 2021 10:04 pm
by 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.

Re: Subtracting Date fields

Posted: Thu May 13, 2021 11:46 pm
by AlanE
"Generally, not a good plan. Forms give base much of it's power." :knock:

Re: Subtracting Date fields

Posted: Thu May 13, 2021 11:58 pm
by AlanE
Generally, not a good plan. Forms give base much of it's power.
:knock: