Help with Query Excluding empty table data fields

[Mycle]

Following a previous question which was kindly answered by @Ratslinger.
I have a Table “tblCaravans” one of the table text fields “Model” contains the caravan model which includes the length of the caravan in ft. ie: “MR18ES2”, “MR19BES”, “MR16ER” etc. The text position 3 and 4 contains the length of the caravan in ft.
I needed to report on the van length by extracting the text from this field’s pos 3 and 4 and had tried using SUBSTRING in a Query without success.

Thanks to @Ratslinger who provided the SQL:

SELECT “Length”, COUNT( “Length” ) FROM ( SELECT SUBSTRING ( “Model”, 3, 2 ) “Length” FROM “tblCaravans” ) GROUP BY “Length” ORDER BY “Length” ASC

which workes fine…HOWEVER!

Some of the entries don’t contain data in the “Model” field as members may not have taken delivery or may be changing model. This results in a blank field in the query results.

For my report how can I exclude blank entries OR substitute text such as “???” or “N/R” for not recorded for the “Length” for the report.

At Present this is my query result, first cell blank:

Here’s hoping someone has an answer.

Mike

[Ratslinger]

Hello,

The query can be modified but it depends upon the result actually wanted. If you wish to eliminate those items without a “Length” then:

SELECT "Length", COUNT( "Length" ) FROM ( SELECT SUBSTRING ( "Model", 3, 2 )  "Length" FROM "tbl_Caravans") Where "Length" <> '' GROUP BY "Length" ORDER BY "Length" ASC

If you want to include this in the report then something like:

SELECT "Length", COUNT( "Length" ) FROM ( SELECT coalesce(SUBSTRING ( "Model", 3, 2 ), 'N/R') "Length" FROM "tbl_Caravans") GROUP BY "Length" ORDER BY "Length" ASC

[Mycle]

Yahoo! Perfect @Ratslinger Plus I have learned something. Thank you!

Mike

[Ratslinger]

Glad it suits your needs. Seems what you can really use is more knowledge of SQL. For that you can refer to the DB manual. For HSQL v1.8 → Hsqldb User Guide. There are also multiple on line sites for reference for various DB’s (but not always applicable to HSQL).

[Mycle]

Thanks again @Ratslinger

Mike