Ask Your Question

Help needed with After Update Event

asked 2018-12-03 01:26:29 +0100

Mycle gravatar image

HSQLDB LibreOffice

I have a table "tbl_Caravans" with text fields "Model" and "Length". As the length is part of the Model ie: MR22ES2 SUBSTRING([Model],3,2) returns the length in feet = 22

Tried using SUBSTRING etc in a Query to provide the length, but I am then unable t sum or count this query field so I thought I would add the field to tbl_Caravans and simply update the "Length" field when the "Model" field is updated on the entry form with no need for an operator to see or use the "Length" field it could be invisible.

I am an ex Access user, frustrated as I would have used an event proc and me.length = SUBSTRING([me.Model],3,2) for this. Can someone simply explain and help with creating the macro.


edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-12-03 01:46:29 +0100

Ratslinger gravatar image


Better not to add something like that to the table when SQL can do it:

Select "Length", count("Length") From (Select substring("Model", 3, 2) AS "Length" From "tbl_Caravans") Group By "Length" Order by "Length" asc

gives result as:

image description

If this answers your question please tick the ✔ (upper left area of answer). It helps others to know there was an accepted answer.

edit flag offensive delete link more


Thanks so much Ratslinger, "Perfect" I was wanting to achieve this using SQL rather than ad the extra field etc. Thanks Again


Mycle gravatar imageMycle ( 2018-12-03 01:52:22 +0100 )edit


Originally, many years ago, I also came from Access but decided to learn to use Star Basic & Uno (now some Python). There is currently included with LO a library which may help you in your transition. It is called Access2Base and you can find the info here -> Access2Base. I dabbled with it but really prefer to do without. To me it's just another interim.

Ratslinger gravatar imageRatslinger ( 2018-12-03 02:06:45 +0100 )edit

Thanks for the advice,much appreciated, I am determined to keep on learning. I am now using the query as above as the data source for a report. Some of the length fields contain no data so I used an IF(Length <> '', Length, "???") in the report data field to have question marks appear when the field is blank. This is not working, have tried other formatting such as IF([Length] <> '', [Length], "???") and IF(Length <> ''; Length; "???") etc with no luck. What am I doing wrong????? Mike

Mycle gravatar imageMycle ( 2018-12-03 02:58:23 +0100 )edit

Actually this may be a different question. Don't understand what you are doing in the report that this field needs to contain data. If it does, why not supply it with data in the query using something such as coalesce? More information on just what you are doing and maybe a new question needed.

Ratslinger gravatar imageRatslinger ( 2018-12-03 03:32:05 +0100 )edit

Thanks Ratslinger! Some of the length fields contain no data due to no model data so the first line of the report based on the query looks like

Length No. Vans 5 16 119 17 1 18 104 19 213

I want to show something for the blank Length results, chose "???"

Have not come across coalesce


Mycle gravatar imageMycle ( 2018-12-03 03:48:46 +0100 )edit

coalesce selects the first non null value:

Select coalesce("MyCount",'???') from "MyTable"

This may work but it all depends on your query & how the data is used in the report.

If you still have problems, ask as new "Report" question and include specific problems you are having. Forum is designed to ask a question & get an answer. If a Report question is buried here, who can find it?

Ratslinger gravatar imageRatslinger ( 2018-12-03 04:42:36 +0100 )edit

Thank you will do This worked IF([Length] <> "";[Length];"???")


Mycle gravatar imageMycle ( 2018-12-03 04:46:54 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-12-03 01:26:29 +0100

Seen: 16 times

Last updated: Dec 03 '18