Days calculation in a Base form

[Martulus]

Good day. I have a database with certificates. These certificates have expiry dates. I am displaying the certificate information, but need to also show the number of valid days remaining on a certificate. This would be a dynamically calculated field, since the number of valid days decrease daily. My problem is that I cannot get this to show on a form. I do not want to add a field into the table to store that value (makes no sense). Any assistance would be deeply appreciated.

[RobertG]

Have a look here:

Autodecrement in a form

Let’s say I have a “NbDays” field in a form where I can manually insert a number, like 5 for 5 days. . I would like this number to autodecrement when opening the DB. Autodecrement should only be done based on the number of days passed. For example, if 3 days have passed before opening the DB, when it is opened again this field will be decremented by 3 automatically and will display 2. If all days have passed, the field will simply be empty. Obviously no decrement can be made within the same da…

Might be the thread helps.

[Martulus]

RobertG, thanks for your response. However, my problem is more of a display problem. Let me explain further.

The form has the fields from the table. I put a numeric box (unlinked to any field) next to the expiry date. That is to show the user the number of days remaining on the certificate. I go to the Control Properties, to the Data side, and put in:
SELECT DATEDIFF(‘dd’, CURRENT_DATE, “XD”) FROM “Certificates”
“Certificates” is the name of the table.
I’ve also tried NOW ( ) and CURDATE ( ) instead of CURRENT_DATE. Nothing shows in the box. If I try that same line in a query, it performs. So I’m thinking I’m setting this thing up wrong.

Thoughts, pointers, and your patience appreciated.

[RobertG]

The query should be database of the whole form. This field is connected to a formatted field. It could be formatted as ‘23 days’ or what you want, because the result of the query is an integer value.

[Villeroy]

Bind the form to a query like the following:

SELECT * , DATEDIFF('dd',"DateField",CURRENT_DATE)AS"Remaining" 
FROM "Somewhere"

This selects all fields plus a calculated field named “Remaining” based on a field named “DateField” from a table named “Somewhere”. Test this with the double-quoted names replaced by the actual ones.

[Martulus]

Villeroy, this is the closest I have come to the solution. Coming from an Access background, I know what I want, but have yet to find the way in Base. Nevertheless, I am using your suggestion for now. Thank you.

[Villeroy]

There is nothing special about “Base”. It is pure SQL according to the SQL standard. If we are talking about an embedded HSQLDB (see status bar of your Base window), then the details are documented here: Chapter 9. SQL Syntax

Recent topic about what Base is: Confusion about "Standalone Form"