Parse SQL with unquoted exclamation points used in control references

[joyfullservice]

In Microsoft Access you can refer to a control on a form using syntax like this: [Forms]![frmColors]![Text18].

However, when this is parsed by the SQL formatter, we end up with spaces around the exclamation points:

SELECT
  [Forms] ! [frmColors] ! [Text18] AS FormControl;

This is obviously an unwanted change, and could create problems if the project is using the option to overlay the SQL code after importing the queries. (Possibly breaking the queries.)

My hunch is that this is one of those cases where Access is using some special syntax that is different from other dialects that the parser is more familiar with. I would like to address this before rolling out version 4.

[bclothier]

One strategy you could pursue is to inspect a query's Parameters collection and therefore extract the parameters out of the SQL before parsing it. This way, you don't have to worry about accommodating the expressions which would involve too much baggage relating to VBA & Expression Service syntax rules.

[joyfullservice]

One strategy you could pursue is to inspect a query's Parameters collection and therefore extract the parameters out of the SQL before parsing it. This way, you don't have to worry about accommodating the expressions which would involve too much baggage relating to VBA & Expression Service syntax rules.

That's a really interesting idea... Thanks for mentioning that!

[joyfullservice]

An interesting side effect of this change is that embedded expressions are also formatted with indenting and line breaks.

SELECT
  [Forms]![frmColors]![Text18] AS FormControl,
  IIf(
    [Forms]![frmVCSInstall]![chkUseRibbon],
    Eval("True"),
    False
  ) AS TestExpression;

Even with the formatting changes, the expression seems to still be valid Access SQL, and can be pasted directly into the SQL query editor and run with no errors.

[joyfullservice]

@bclothier - This should be resolved as of the latest commits in the dev branch. Let me know if you encounter any other issues with other types of expressions. I will close this as completed for now, but if we encounter other related issues, we can reopen the issue.