Different Format in Monaco Editor

[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.

 

[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.

 

[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

 

[JTrilloJ]

I agree, Colin:
I didn't quite express what I wanted to point out.
I know there's a circular reference, and from there, I didn't explain myself well.
What I'm trying to say is that even with a circular reference, Monaco shows differences when expanding.
In the one I cited,(Post # 18) despite the circular reference and the use of the alias to generate another alias, the editor expands it very well.
I was also trying to point out that the use of IIf doesn't seem to be the cause.
However, in others, it doesn't.
It's not of great importance to me, but it's possible that I'll use it in the future.
I'm not in a hurry.
Best regards, and thanks again.

 

[isladogs]

Ah it seems I misunderstood your previous post. Yes I agree with all of that which is why I previously said I was baffled.
At one point I did get your query to auto expand after modifying that one field. I then renamed the query and it compacted again.
No idea why.

 

[JTrilloJ]

Ah it seems I misunderstood your previous post. Yes I agree with all of that which is why I previously said I was baffled.
At one point I did get your query to auto expand after modifying that one field. I then renamed the query and it compacted again.
No idea why.

Well:
Bill Gates is definitely on vacation.
We'll always have the A-Team, with whom you have influence.
Have a good weekend.