Help needed with After Update Event

[Mycle]

HSQLDB LibreOffice 6.1.3.2

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.

Mike

[Ratslinger]

Hello,

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:

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

[Mycle]

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

Mike

[Ratslinger]

@Mycle,

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.

[Mycle]

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

[Ratslinger]

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.

[Mycle]

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

Mike

[Ratslinger]

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?

[Mycle]

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

Mike