Use query as control data source in Base

[bbater]

Operating system: Windows 10 Pro Version of LibreOffice: Version: 6.2.5.2 (x64) File format: ODB Database engine: Firebird

I have searched Base Handbook and online to no avail.

My Client table has a column ‘DoB’ (Date of Birth). When I display the Client’s details in a form, I want to include a calculated numeric control ‘Age’ which derives its displayed value from a query:

select DATEDIFF (year, “DoB”, CURRENT_DATE) AS “Age” FROM “Client”

I can find no way of specifying that the control’s data source is my query. Please advise.

TIA,

Bob

[Ratslinger]

Hello,

This is a bit tricky since there are still some bugs regarding Firebird embedded and they apply here. The problem is with the SQL - twofold. One you need to bypass the interpreter (for DATEDIFF) and another is the use of Alias names.

So the objective here is to have all the client information on the main form and then with a sub form use the query to display the corresponding age (the query presented doesn’t actually do that).

So the original query needs an additional field to link back to the Client record:

select ID, DATEDIFF (year, "DoB", CURRENT_DATE) AS "Age" FROM "Client"

Now this will not display in any form. So, as silly as this seems and to work around existing bugs, a second query is used (not bypassing the interpreter) selecting the results of the first query:

SELECT * FROM "AgeQuery"

This result can then be used in the sub form and linked to the main form via the ID field. The sub form is set to not allow additions, modifications or deletions - display only.

Sample ----- DisplayAge.odb

You will not see the age on new or changed DoB until the record is updated and refreshed. Easiest method for that is go to next or previous record & back again. Age is then updated.

[bbater]

Great advice, Ratslinger! I’ll be trying that tonight and will let you know how I get on. Many thanks!

[bbater]

Brilliant! It works as required. BTW I had spent ages yesterday running and re-running my query via the GUI and getting a Syntax Error until after a lengthy web search, I discovered that this is caused by a bug in the SQL parser, as you say above. Running via Tools > SQL showed it was a valid query. Many thanks Ratslinger, much obliged.