transform SQL to a more readable format

[rene65]

I want to know if the following is possible: suppose if made a query.
When i turn to sql view it says (simplified):

select name, address, phone
from tbladdresses, tbllookup
where bla, bla, bla

Now i want to copy it into unbound txtbox1 and push a button.
The second txtbox should look like this:

select
name,
address,
phone

from
tbladdresses,
tbllookup

where
bla,
bla,
bla

Now it's more readable for auditors.
Can someone provide me the code for the button ? (if it is possible of course)
I tried this on other forums, but nobody would/could help me.

 

[ted.martin]

If you are having trouble with your SQL, make a test query, test it does what you want and then View/SQL view. All the SQL code is there for you to copy and paste into your form code. (make sure you add a " and the beginning and the end after the ;

 

[rene65]

Ted, I am sorry, but this is not what I mean. I know the code is there, but my external auditor wants to write a report for an audit on access. If I copy the code I must manually copy it into word and after every comma he wants a new line, so I must give an enter after every comma. That's it. For me it's fine the way it is but for him....

 

[chergh]

Tell your external auditor where to go. If he can't understand SQL he has no business auditing it, and I'm saying this as a registered lead auditor myself.

 

[WayneRyan]

Rene,

It is definitely possible to write a routine to "reformat" the SQL for your
queries.

On the other hand, the information already resides in the MSysObjects
table (the names of your queries) and the MSysQueries table (the names
of your fields/tables for the query).

Check out those tables on a sampling of queries. Should provide a much
easier approach.

hth,
Wayne

 

[rene65]

Wayne, could you get me started with your answer?
Could you help me out writing a routine?
In my database i have reports that make use of MSysObjects, but is more to show what queries i have and what tables i use, not the sql itself.
Thanks for helping me out.

 

[rene65]

Chergh, my auditor know his query language, but asks me to make it more readable. In other words, he wants to read the query in seperate lines, not as one line. At work we have a loansystem where we can make sql queries graphically. Upon clicking a button it shows the sql, not as one line, but in seperate parts like:

select a, b, c

from d, e

where f = g
and h = I

Order by J