Different Format in Monaco Editor (2 Viewers)

[JTrilloJ]

Without making any changes to my databases, when I open queries in the Monaco SQL editor, they appear differently.
Some have fields separated by lines, but others are displayed in blocks.
Do any of you know why this happens, and more importantly, how can I get the fields to appear in lines?
See attached image.
Thanks in advance.

 

[Pat Hartman]

I can't say for sure but the query on the left was probably formatted by you and saved that way. The one on the right was formatted by Monaco. It is possible that once you modify a query using Monaco and save it, that Monaco may reformat it but I never noticed that happening. Probably because I tend to use the QBE view when working with queries unless the query is complicated and QBE obscures the where clause.

Even the old editor would not reformat your code UNLESS you switched to QBE view and modified the query there. THEN the editor would remove all your pretty-print formatting which caused many people to resort to writing SQL in VBA regardless of how painful that was. Monaco may do the same. Now that we have a choice of opening the query in SQL view, I choose the view I want to work in depending on the query.

 

[JTrilloJ]

Thanks, Pat, for your response.
I can assure you 100% that I didn't manually format the query on the left.I have a few more in that format, and several in a block.
For someone with more knowledge than me, it's probably not a mystery, but for me it certainly is.
Best regards.

 

[Pat Hartman]

I just looked a a few databases. One I'm currently working on plus some old ones. I thought maybe I would see differences but Monaco opens even queries that were created years ago and never edited using Monaco and shows them in the field list format rather than the mushed string it used to use. So, the mystery remains.

 

[Gasman]

Switch off the Monaco editor then.?

 

[cheekybuddha]

Do any of you know why this happens, and more importantly, how can I get the fields to appear in lines?

You actually *want* to see the query formatted as in the example on the right?

 

[JTrilloJ]

You actually *want* to see the query formatted as in the example on the right?

David: What I'd like is to see the query like the left side.
That is, with one line per field.
As I mentioned to Pat in my Post #3, I already see several like this.
On the other hand, I see others in a block, with the fields joined together (separated with a comma, of course):
I'm not considering the Gasman option (regards) at the moment because I think the new visualization and the features of the new "Monaco" editor are good.
Best regards, JTJ

 

[cheekybuddha]

David: What I'd like is to see the query like the left side.
That is, with one line per field.

Ah OK, good!

I thought you were saying that the editor was formatting it like the left side, but you wanted it to remain like the right side!

I'm afraid I don't have anything useful to offer as a solution, except keep a copy of your queries properly formatted in a separate text file using a good editor. But I know that is not practical.

 

[jdraw]

I think Crystal (strive4peace) has a free add-in that transforms sql into the format on the left side.

 

[JTrilloJ]

Thanks again, David:Just a few minutes ago, I found an article by Colin Riddington (Isladogs), which mentions that using Ctrl+K to convert it to Line Formatting by Field.
I assume it's coming in a new update, because I run it and it doesn't do anything.
The article is quite comprehensive and graphic.
There are several screenshots.If anyone is interested, you can access it at: Isladog Web
Regards, JTJ:
I saw the jdraw post. I'll take note, and thank you.

 

[isladogs]

Version 2506 included the new expanded SQL format
It should happen automatically. However, if you import SQL from another source, you can trigger the expanded formatting by clicking Ctrl+K.
See section 2 of my article:

Monaco SQL Editor Improvements

This short article outlines three significant improvements to the Monaco SQL Editor recently announced by the Access team and which will roll out to all channels in the coming months.

www.isladogs.co.uk

NOTE: If Ctrl+K doesn't cause it to expand, it is because there is a syntax error in the SQL

Whilst the new SQL formatting feature was one of the most requested Monaco improvements, there are times when developers may want to disable it. For that reason, it will become an optional feature in the near future with the default set to enabled.

For info, I also have code to toggle the SQL between compact & expanded. It is part of this add-in

SQL to/from VBA Converter Add-In

This Access add-in is an updated version of my SQL to VBA And Back Again utility which makes it easy to convert query SQL to a VBA string (or vice versa). Additonal functionality includes creating example code from the VBA string and temp queries to test the SQL / VBA output.

www.isladogs.co.uk

 

[JTrilloJ]

Version 2506 included the new expanded SQL format
NOTE: If Ctrl+K doesn't cause it to expand, it is because there is a syntax error in the SQL

Thanks, Colin:
There may be an error in one of the queries, but I can't see it, and all the queries that are grouped together are running normally.
As I mentioned earlier (Post #10), Ctrl + K isn't working for me.
I'm not sure which version of Access I'm using because I have three pieces of information:
I've attached an image.
I'll leave it at that for now, as it's not urgent.
Best regards, JTJ

 

[isladogs]

If you want to send me the query SQL together with the related table structures, I can try to work out why it won't expand.
Or if its correct SQL, I can ask the Access team why its not expanding properly

You are in fact on Current Channel Preview version 2507 build 19029.20076 In this case, the middle picure is correct.

My version checker gets the monthly version number from the 365 update history site as its impossible to obtain using VBA or the registry.
However, that particular build caused problems for my version checker as the website originally published the version number as 2508 instead of 2507. Normally they get it right!

 

[JTrilloJ]

Thanks, Coling:I'm attaching a sample.
Just a Qry that doesn't expand or that I don't know how to do.
Best regards>> JTJ

 

[isladogs]

I'm equally baffled.
Just expanded it with my add-in but as soon as I saved it, it reverted to the compact version.
Just sent it to the A-team to see if they can explain

 

[JTrilloJ]

Colin:
I think it was a good idea.
Let's wait for the outcome.Thanks again.

 

[isladogs]

I tried disabling all fields that involved an expression and reinstated them one at a time.
The problem field in design view is:

PrecMedCompra: IIf(IsNull(Productos!PrecMedCompra),Productos!PrecioUnidad,IIf(Productos!PrecMedCompra=0,0,Productos!PrecMedCompra))

With that disabled, it auto expands

I looked carefully and realised you had a circular expression for PrecMedCompra so renamed it as PrecMedCompraX
I think that was your syntax error
The second IIf statement also seems pointless and can be removed

PrecMedCompraX: IIf(IsNull(Productos!PrecMedCompra),Productos!PrecioUnidad,Productos!PrecMedCompra)

However as you used that field in several other expressions e.g. CosteCompra &
MrgVentaMoneda, there are knock on effects that I will leave you to deal with if its important enough to you
Good luck!

 

[JTrilloJ]

With that disabled, it auto expands

Thanks, Colin:
In principle, I see this as a very important step in uncovering the deficiencies in the query's construction.
I've followed your instructions for replacing field names to avoid circular references.
They produce an occasional result.
If I close the query with the field disabled, it's lost.
My intuition tells me that "SOMETHING" is being passed to a block and not expanding.
The same thing happens to me in several queries.
In others, it expands cleanly, even with an IIF.
Example:
SELECT
DetallePedido.IdProducto,
DetallePedido.NombreProducto,
Sum(DetallePedido.Cantidad) AS QPedido,
Sum(DetallePedido.QAsignada) AS QAsignada,
IIf(
[QAsignada] > [QPedido],
0,
[QPedido] - [QAsignada]
) AS QPendAsig
FROM
DetallePedido
GROUP BY
DetallePedido.IdProducto,
DetallePedido.NombreProducto
HAVING
(((Sum(DetallePedido.QAsignada)) > 0));

It's true that I don't use the alias "QPendAsig" to calculate another field.
For now, I'll wait for new updates and see if this is resolved.
Best regards.

 

[isladogs]

There is nothing wrong with using an IIf statement in a query though there is a problem with it here

You have another circular reference in the latest query:

Sum(DetallePedido.QAsignada) AS QAsignada

The expression field should have a different name to the field it is based on

You have also used that same field name in the next calculated field based on the IIf statement.

The query could be shortened significantly by using an alias for the table
In fact as you are only using one table, there is no need to specify the table name other than in the FROM cluse