[Solved] Unable to open report when grouped by calculated field

[_Chris]

Hi
I only recently upgraded from Windows XP to Windows 10 and of course my Access 2000 database will no longer work. Luckily I was able to import the tables and structure into openoffice base but I am having trouble creating a certain report. It's based on this query:

SELECT `CostsID`, `DatePaid`, MONTH( `DatePaid` ) AS `Month` FROM `Sundries` GROUP BY `Sundries`.`CostsID`, `Sundries`.`DatePaid`

It's not the full thing but it's enough to illustrate the problem which is that when I group by 'Month' in the report it won't open. It says 'parameter Sundries.Month has no default value'.

The Month field is a 'made up' field that is derived from the underlying Sundries table. I have other calculated fields in similar queries and I can group by those and display the report no problem.

Any ideas on what I am doing wrong please?

[UnklDonald418]

parameter Sundries.Month has no default value'.

Try using "Month" instead of "Sundries"."Month" in your group statement.

[_Chris]

Thanks for your reply. I am using 'Month' instead of 'Sundries.Month' in the query & group statement but it makes no difference I still get the same or similar error 'unable to open report Sundries.Expr1002 has no value' or as above.

SELECT `CostsID` AS `CostsID`, `DatePaid` AS `DatePaid`, MONTH( `DatePaid` ) AS `Month` FROM `Sundries` GROUP BY `Sundries`.`CostsID`, `Sundries`.`DatePaid`
or

SELECT `CostsID` AS `CostsID`, `DatePaid` AS `DatePaid`, MONTH( `DatePaid` ) FROM `Sundries` GROUP BY `Sundries`.`CostsID`, `Sundries`.`DatePaid`

[Villeroy]

There are two types of queries:
1) Parsed mode
2) Direct SQL (in Access known as "passthrough query")

In parsed mode you have all the functionality of the Base component but you can't do certain things that are specific to your database driver.

In direct SQL mode you can edit the query in the SQL editor only.
The result set is always read-only.
You can't apply named parameters, filters, sort orders from the Base GUI. Form/subform relations don't work.
You can use the SQL syntax of your backend. I use it quite often for debugging because line breaks are preserved and the database engine gives clearer error messages than Base does.

Your query translated into parsed mode:

Code: Select all

SELECT "CostsID", "DatePaid", MONTH( "DatePaid" ) AS "Month" 
FROM "Sundries" 
GROUP BY "CostID", "DatePaid"

I tested this with a database of mine and quoted names replaced. A report based on that query runs fine with LibreOffice 7.3

[_Chris]

Thanks,
It's not the query that's the problem really. I made your query and it does indeed run fine. It's when I try to create a report from it that I run into a problem. I want to be able to sub-total each month so I add a grouping level in the report. Sorting and Grouping -> Field/Expression 'Month'.
When I try to run the report I get 'The document Villlleroy (that's the name of the query) could not be opened. Parameter 'Sundries.Month' has no default value

[_Chris]

PS maybe I should be using LibreOffice 7.3 instead of OpenOffice 4 1 12?

[Bidouille]

Is it a native database or connected to a MDB?

As usual, provide an ODB with few datas and your report.
More easy to understand and reproduce.

[UnklDonald418]

If you are not a long time user of OpenOffice then I would recommend using LO.
LibreOffice is a better maintained fork of OpenOffice. The LO developers made some annoying changes to the user interface which makes the transition from OO to LO difficult for some long time users of OO or we probably would have all made the switch by now.
From the standpoint of the Base module, probably the biggest difference is that LO uses the Oracle Report Builder as the default report writer, while OO by default has a rather crude report writer. But the ORB extension can be installed in OO bringing i report writing up the level of LO.

[Villeroy]

https://www.mediafire.com/file/bzeecqs9 ... 9.odb/file has 4 reports based on 2 queries that are identical except for parsed/direct mode.
Amounts_Old_DirectSQL is an old style report made with OpenOffice from the equally named query in direct SQL mode and works with both office suites.
Amounts_Old_Parsed is an old style report made with OpenOffice from the equally named query in parsed mode and fails to show any records with OpenOffice.

Amounts_ReportBuilder_DirectSQL and Amounts_ReportBuilder_Parsed require LibreOffice or OpenOffice+Report Builder.

[_Chris]

Thanks again, I've looked at your examples and I've got my report to work as I would like by using direct mode. So that is SOLVED.

I've also installed Libreoffice but cannot get my odb database that I created in OO to open. I have registered it in LO but when I try to open it I get
'The connection could not be created. Perhaps the necessary data provider is not installed'.
I'm not sure how to proceed here - should I open a new request or is there a quick and easy answer to this?

[Villeroy]

Does your LibreOffice open my example database?
A registration is only needed when Writer and/or Calc documents have links to the database.

[_Chris]

Yes it opens yours OK

[Villeroy]

The status bar of my database document reads: "embedded HSQL" or similar. What do you read in the status bar of your database?

[_Chris]

I'm not sure where the status bar is

[RoryOF]

Status Bar is bottom bar of OpenOffice window. If it's not there, turn it in with /View /Status bar.

[Villeroy]

You can't hide it in Base.

[_Chris]

Mine says Microsoft Access. It must be from when I imported it

[Villeroy]

You did not import anything. You connected a Base document to an Access database. Just like MS Access, Base is NOT a database. Both applications are tools to work with various types of databases. You can connect Base and Access with dozends of database types.
For self-contained, single-file databases Base comes with HSQL as kind of "native" database engine whereas MS Access is shipped with the JET database engine.
If your Base document is connected with a self-contained Access database, the file (with mdb or accdb suffix) needs to exist and you need some database connectivity tools from Microsoft.
However, I do not see any reason why OpenOffice Base can work with a Base connection while LO Base fails to do so on the same system. Do you run OO and LO on the same WIndows system?

[Villeroy]

If you deleted the Access file, both applications will fail since you deleted the actual database. You should restore the database then. You find the expected location in the status bar next to the connection type.
If you moved the Access file to another place, menu:Edit>Database>Connection lets you reconnect the Base document to the right Access file.

[_Chris]

Bloody good job I made a copy of the original Access database and did all new work on that. I'm going to try and install mySQL and use a program called Bullzip to convert the mdb to mySQL. I'll then connect to it from LibreOffice and redo my forms & reports etc.

Does that seem like it could work?

[Villeroy]

The idea of connecting an self-contained Access database is that you can continue working with MS Access while having all the data at hand with Libre/OpenOffice. Database hopping from one product to another is not a good idea. If you want a server based (networkable) database usable from multiple clients simultaniously, the transfer to MySQL may be worth the effort. For a simple desktop database I would keep the MS database and use it with Base's forms and reports and with LibreOffice documents. As far as I know, this works pretty well.

[Villeroy]

I'll then connect to it from LibreOffice and redo my forms & reports etc.

Does that seem like it could work?

If you have some forms and reports in your Access connected document, you may also re-connect this document to your new MySQL database via menu:Edit>Database>Connection... Queries, forms, reports should work as far as the names of tables and columns are identical.