# VBForums CodeBank > CodeBank - Visual Basic 6 and earlier >  VB - SQL 'Select' statement formatter/checker

## si_the_geek

The attached module contains a function called FormatSelect, which formats an SQL Select statement string so that it is easier to read.
This should hopefully help you to make changes more easiy, or (if you have errors) enable you to see any mistakes much more quickly.

As a little bonus, the function will also notify you if there are obvious errors, such as the wrong number of quotes to mark the start/end of strings.

Example usage:

VB Code:
Dim sSQL as String
  sSQL = "SELECT Table1.Field1 FROM Table1 WHERE Field2 = 3"
  sSQL = FormatSelect(sSQL)
  MsgBox sSQL

A couple of examples of the inputs/outputs of the function:
1) if the following Select statement is provided as input:


```
SELECT Table1.Field1 FROM Table1 WHERE Field2 = 3
```

...it gets converted into this:

```
SELECT    Table1.Field1

FROM      Table1

WHERE     Field2 = 3
```


2) if this Select statement is provided as input:


```
SELECT Table1.Field1 AS Field1, Table1.Field2 AS Field2, CASE WHEN Table2.Field3 = 0 THEN 'NO' 
WHEN Table2.Field3 = 1 THEN 'YES' WHEN Table2.Field3 IS NULL THEN 'NO' END CASE as Field3, IIf(Field4=1,1,0)
From Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1 LEFT JOIN Table3 ON Table1.Field1 = Table3.Field1
Where Table1.Field5 Between '1/1/2005' AND '1/20/2005' AND Table1.Field6 Between '1/1/2005' AND '1/20/2005' 
OR Table1.Field7 = '1/20/2005' 
AND Table1.Field8 IN(SELECT Field1 FROM table4 WHERE field2 IN(SELECT Field1 WHERE a=b FROM table2))
```

...it gets converted into this:

```
SELECT    Table1.Field1 AS Field1,
          Table1.Field2 AS Field2,
          CASE WHEN Table2.Field3 = 0 THEN 'NO' WHEN Table2.Field3 = 1 THEN 'YES' WHEN Table2.Field3 IS NULL THEN 'NO' END CASE as Field3,
          IIf ( Field4 = 1, 1, 0 )

FROM      Table1
          LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1
          LEFT JOIN Table3 ON Table1.Field1 = Table3.Field1

WHERE     Table1.Field5 Between '1/1/2005' AND '1/20/2005'
          AND Table1.Field6 Between '1/1/2005' AND '1/20/2005'
          OR Table1.Field7 = '1/20/2005'
          AND Table1.Field8 IN (

                    SELECT    Field1
                    
                    FROM      table4
                    
                    WHERE     field2 IN (
                    
                                        SELECT    Field1
                                        
                                        WHERE     a = b
                                        
                                        FROM      table2
                    
                              )
```

...with the following warning message:

```
(in sub-query: 
  (in sub-query: 
    Incorrect section ordering: FROM should be before WHERE
    )
  )
```

Note that the blank lines are optional, and can be disabled like this:
VB Code:
sSQL = FormatSelect(sSQL, False)

The number of spaces to use as an indent (for 'normal' text, and for subqueries) can also be specified, using two further optional parameters. eg:

VB Code:
sSQL = FormatSelect(sSQL, , 4, 2)

An alternative version by CVMichael can be found here: VB - SQL Select Formatter.


Please let me know if there are any parts of SQL syntax which are not handled appropriately.  :Smilie:

----------


## CVMichael

Hi si_the_geek,
First off... I've been looking for something like this for a long time because in my application I made for work I have over 100 SQL statements that are like reports.
All the SQL statements are very complex, so I guess it's perfect for testing your code.

First off, I tried with 3 SQL statements until now, and 2 of them I get an infinite loop in this function: FormatSelect_Worker
In this code:

VB Code:
Select Case aKeywords(lThisSectionType)
    Case "SELECT", "GROUP BY", "ORDER BY"
      
      lTempPos = lCurrPos
      Do
        Do
          lTempPos = InStr(lTempPos, sText, ",")
          lBracketStart = InStr(lCurrPos, sText, "(")
          If (lBracketStart > 0) And (lBracketStart < lTempPos) Then
            lBracketEnd = FindCloseBracket(sText, lBracketStart)
            If lBracketEnd < lNextSectionStart Then
              lTempPos = lBracketEnd
            Else
              lBracketStart = 0
            End If
          Else
            lBracketStart = 0
          End If
        Loop While (lBracketStart > 0) And (lTempPos > 0)
This is one of the simple SQL statements it's doing this:


```
SELECT FORMAT(abt.DateChanged, "MM/DD/YYYY") AS "Date", aa.Action, Count(*) AS "Total"
FROM tblAbnormalTran abt, tblAbnormalTranActions aa
WHERE abt.Action = aa.[ID]
AND CDATE(FORMAT(abt.DateChanged, "MM/DD/YYYY"))
BETWEEN a AND b
GROUP BY FORMAT(abt.DateChanged, "MM/DD/YYYY"), aa.Action
ORDER BY FORMAT(abt.DateChanged, "MM/DD/YYYY") DESC
```

----------


## si_the_geek

Ok, I've fixed that - it was due to a 'last minute' change I made, that obviously wasn't quite right!

I also spotted a couple of things raised by your SQL, and a couple of code issues/improvements.

Updates:
Allowed strings to be marked by ' or " (both can be used in the same SQL statement)Changed formatting of brackets (they now have a space before and after them)Fixed a couple of potenial issues where part of the next 'section' could be added to the same sectionFixed issue when strings start/end with doubled-up quote characters (they were previously removed from the string)

----------


## szlamany

Si, we've had a major server/network meltdown - and I was visiting a client for a couple ofday - nothing ever goes smooth...

I've got lots and lots of stored procedures - would you like me to send you some or should I just run the function on them and report back to you?

----------


## si_the_geek

I read about the meltdown in another thread, sounds very very painful - hope you manage to get it all sorted quickly.


Either way is fine by me - although I'd like to see any SQL statements that show up issues with my code!

Note tho that the formatter only works for Select statements (for the moment at least!).

----------


## dee-u

> I read about the meltdown in another thread, sounds very very painful - hope you manage to get it all sorted quickly.
> 
> 
> Either way is fine by me - although I'd like to see any SQL statements that show up issues with my code!
> 
> Note tho that the formatter only works for Select statements (for the moment at least!).


I'm waiting for your version on those other sql's specially for action queries...   :Smilie:

----------


## si_the_geek

That could be quite awkward, as different DBMS's have much more variation in formatting for those, so don't hold your breath!

I wont attempt that until I'm sure it works for Select's tho, as most action queries can include them!

----------


## CVMichael

OK... weird things are hapening.... I will highlight in bold those weird things:
Original SQL


```
SELECT FORMAT(fo.FileDate, "MM/DD/YYYY") AS "File Date",
Count(*) AS KITs, pa.Parts

FROM (tblOrderFileList ofl INNER JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID) LEFT JOIN
(
SELECT CDate(FORMAT(fo.FileDate, "MM/DD/YYYY")) AS FileDate,
OrderInstructionCode AS "Type",
Count(*) AS Parts

FROM tblOrderFileList ofl INNER JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID
WHERE fo.CompanyName = 'ABC'
AND fo.FileDate BETWEEN a AND b
AND OrderInstructionCode = "PART"
GROUP BY FORMAT(fo.FileDate, "MM/DD/YYYY"), OrderInstructionCode
) AS pa ON pa.FileDate = CDate(FORMAT(fo.FileDate, "MM/DD/YYYY"))
WHERE fo.CompanyName = 'ABC'
AND fo.FileDate BETWEEN INSERT_VALUE_1 AND INSERT_VALUE_2 + 1
AND OrderInstructionCode = "KIT"
GROUP BY FORMAT(fo.FileDate, "MM/DD/YYYY"), OrderInstructionCode, pa.Parts
ORDER BY CDate(FORMAT(fo.FileDate, "MM/DD/YYYY")) DESC
```

Formated SQL:


```
SELECT
         FORMAT ( fo.FileDate, "MM/DD/YYYY" ) AS "File Date",
         Count ( * ) AS KITs,
         pa.Parts
FROM
         ( tblOrderFileList ofl INNER
         JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID )
         LEFT JOIN (
                  SELECT
                           CDate ( FORMAT ( fo.FileDate, "" ) ) AS FileDate,
                           OrderInstructionCode AS "",
                           Count ( * ) AS Parts
                  FROM
                           tblOrderFileList ofl INNER
                           JOIN tblOrderFile fo ON fo.OrderFileID = ofl.OrderFileID
                  WHERE
                           fo.CompanyName = ''
                           AND fo.FileDate BETWEEN a AND b
                           AND OrderInstructionCode = ""
                  GROUP BY
                           FORMAT ( fo.FileDate, "" ),
                           OrderInstructionCode
         ) AS pa ON pa.FileDate = CDate ( FORMAT ( fo.FileDate, "MM/DD/YYYY" ) )
WHERE
         fo.CompanyName = 'ABC'
         AND fo.FileDate BETWEEN INSERT_VALUE_1 AND INSERT_VALUE_2 + 1
         AND OrderInstructionCode = "Type"
GROUP BY
         FORMAT ( fo.FileDate, "PART" ),
         OrderInstructionCode,
         pa.Parts
ORDER BY
         CDate ( FORMAT ( fo.FileDate, "MM/DD/YYYY" ) ) DESC
```

It's a problem with the quotes, it's removing stuff in the quotes, or replacing with wrong things...

----------


## si_the_geek

Ah yes, thats a perfect example to test my flaw!

I remove the strings & subqueries at the start of the routine, but my code to add them back to the final string was in the wrong order.  :Blush: 

I've uploaded the corrected version.

----------


## CVMichael

Found more errors...
Original SQL:


```
SELECT FORMAT(ShipDate, "YYYY MM") AS [Month], Count(*) AS Total
FROM tblInvoiceOrderFileList
WHERE OrderClaimStatusCode = 4 AND ShipDate
BETWEEN a AND b
GROUP BY FORMAT(ShipDate, "YYYY MM")
ORDER BY FORMAT(ShipDate, "YYYY MM") DESC
```

Formated SQL:


```
SELECT
         FORMAT ( ShipDate, "YYYY MM" ) AS [Month],
         Count ( * ) AS Total
FROM
         tblInvoiceOrderFileList
WHERE
         
         OR derClaimStatusCode = 4
         AND ShipDate BETWEEN a AND b
GROUP BY
         FORMAT ( ShipDate, "YYYY MM" )
ORDER BY
         FORMAT ( ShipDate, "YYYY MM" ) DESC
```

I think you have to make it search for "OR " (with space after) because some words (like the previous) might contain or...

*[Edit]*
PS, I made my own SQL formater, should I post it here, or should I make a new thread ?

----------


## si_the_geek

Fixed, I had a ">" where I should have had a ">="!

it checks for any of these characters: " '""#()=<>[]+-/*" (or end of string) before & after strings to find, but missed the 'after' check when the match was the first character.




> PS, I made my own SQL formater, should I post it here, or should I make a new thread ?


It's probably best to start a new thread (as I'm sure there'll be discussions for both), but add a link to the thread here - I'll add a link in the top post too.

----------


## CVMichael

> It's probably best to start a new thread (as I'm sure there'll be discussions for both), but add a link to the thread here - I'll add a link in the top post too.


OK, I'm gonna post it soon, I just want to do a little more testing...

I found another problem with your SQL formatter:
I get an error at this line (in FindFirstByArray function):

VB Code:
Const csPunctuation = " '""#()=<>[]+-/*"
 Dim lFirstPos As Long
Dim lArrayIndex As Long
Dim lTempPos As Long
Dim lCount As Long
   lFirstPos = Len(sText) + 1
  lArrayIndex = 0
   For lCount = 0 To UBound(vArray)
    lTempPos = InStr(lStartPos, sText, vArray(lCount), vbTextCompare)
    Do While (lTempPos >= lStartPos)
      [COLOR=Orange][B]If InStr(csPunctuation, Mid$(sText, lTempPos - 1, 1)) Then[/B][/COLOR]
        If lTempPos + Len(vArray(lCount)) > Len(sText) Then Exit Do
        If InStr(csPunctuation, Mid$(sText, lTempPos + Len(vArray(lCount)), 1)) Then Exit Do
      End If
      lTempPos = InStr(lTempPos + 1, sText, vArray(lCount), vbTextCompare)
    Loop
    If (lTempPos > 0) And (lTempPos < lFirstPos) Then
      lFirstPos = lTempPos
      lArrayIndex = lCount
    End If
  Next lCount
lTempPos is = 1, so the result is 1 - 1 = 0, therefore an invalid # for the MID

And if I take off the -1, then the formatter says the select query is wrong on all my selects...

*[Edit]*
Also, something I forgot to say, Your formatter formats the code like this:


```
SELECT
     Field1,
     Field2,
     ....
```

When it's putting the "Field1" (the first field) on the second line, and I run the query, for some reason it does not return any data. But when I change it to this:


```
SELECT Field1,
     Field2,
     .....
```

Then everything is OK... it executes properly, and returns data...

----------


## si_the_geek

:Frown:  I added an extra bug when removing the previous one.  

I'm gonna take a little while over it, and make sure I get it right this time.

----------


## CVMichael

How's it going with your formatter ?

I posted my formatter here:
VB - SQL Select Formatter

----------


## si_the_geek

Cool, I've added a link  :Smilie: 

Well I finally got my head into a coding mood again - I really don't know what was wrong with me!

Here's the updates:
Corrected FindFirstByArray.Due to the problem mentioned above (SQL will not run in ?Access?), I removed the new line immediately after "Select"/"From"/etc, and added the option for blank lines between each 'section' instead.Fixed typo for bracket errors.Corrected mis-use of a variable for passing the And in a Between clause (could cause infinite loop if Between was malformed!).Better formatting of error messages.Allowed # for dates, rather than just ' or ".Added more Join types.

----------


## CVMichael

The new version is MUCH better ! No errors at all !   :Thumb:  

But could you make it have an option for how many spaces to put for indent ?
Because 10 spaces is WAY too much for me, I like 4 spaces...

To give you an idea, this:


```
SELECT DISTINCT MFrom AS "From Model", MWith AS "To Model" FROM
(
SELECT DISTINCT MFrom, TRIM(IIF(MWith LIKE 'abc5%', MID(MWith, 5, 100), MWith)) AS MWith FROM
(
SELECT MFrom, IIF(INSTR(MWith, '(') = 0, MWith, LEFT(MWith, INSTR(MWith, '(')-1)) AS MWith FROM
(
SELECT DISTINCT MFrom, MWith FROM
(
SELECT MFrom, TRIM(IIF(Num3 = 0, MWith, LEFT(MWith, Num3 - 1))) AS MWith FROM
(
SELECT MFrom, MWith, IIF(Num3 = 0, Num2, Num3) AS Num3 FROM
(
SELECT MFrom, MWith, IIF(Num2 = 0, Num1, Num2) AS Num2, IIF(Num2 = 0, 0, INSTR(Num2 + 1, MWith, '-')) AS Num3
FROM
(
SELECT MFrom, MWith, Num1, IIF(Num1 = 0, 0, INSTR(Num1 + 1, MWith, '-')) AS Num2 FROM
(
SELECT MFrom, MWith, INSTR(MWith, '-') AS Num1 FROM
(
SELECT LEFT(m_ofl.Notes, INSTR(m_ofl.Notes, '|') - 1) AS MFrom
, IIF(INSTR(repl.Notes, ',') > 0, LEFT(repl.Notes, INSTR(repl.Notes, ',')-1), repl.Notes) AS MWith

FROM tblInvoice AS repl INNER JOIN
(
SELECT OrderNumber, Notes FROM tblInvoice
WHERE StatusCode = 4
AND INSTR(Notes, '|') > 0
) AS m_ofl ON m_ofl.OrderNumber = repl.OrderNumber

WHERE repl.RecordIDType = 'STAT'
AND repl.StatusCode = 7
AND repl.Notes NOT LIKE '%abc%'
AND repl.Notes NOT LIKE '%abc2%'
AND repl.Notes NOT LIKE '%abc3%'
AND ShipDate > #Apr 01, 2004#
))))))
WHERE LEN(MWith) > 1
)
WHERE INSTR(MWith, MFrom) = 0
))
ORDER BY MFrom, MWith
```

You get:


```
SELECT    DISTINCT MFrom AS "From Model",
          MWith AS "To Model"
FROM      (
                    SELECT    DISTINCT MFrom,
                              TRIM ( IIF ( MWith LIKE 'abc5%', MID ( MWith, 5, 100 ), MWith ) ) AS MWith
                    FROM      (
                                        SELECT    MFrom,
                                                  IIF ( INSTR ( MWith, '(' ) = 0, MWith, LEFT ( MWith, INSTR ( MWith, '(' ) - 1 ) ) AS MWith
                                        FROM      (
                                                            SELECT    DISTINCT MFrom,
                                                                      MWith
                                                            FROM      (
                                                                                SELECT    MFrom,
                                                                                          TRIM ( IIF ( Num3 = 0, MWith, LEFT ( MWith, Num3 - 1 ) ) ) AS MWith
                                                                                FROM      (
                                                                                                    SELECT    MFrom,
                                                                                                              MWith,
                                                                                                              IIF ( Num3 = 0, Num2, Num3 ) AS Num3
                                                                                                    FROM      (
                                                                                                                        SELECT    MFrom,
                                                                                                                                  MWith,
                                                                                                                                  IIF ( Num2 = 0, Num1, Num2 ) AS Num2,
                                                                                                                                  IIF ( Num2 = 0, 0, INSTR ( Num2 + 1, MWith, '-' ) ) AS Num3
                                                                                                                        FROM      (
                                                                                                                                            SELECT    MFrom,
                                                                                                                                                      MWith,
                                                                                                                                                      Num1,
                                                                                                                                                      IIF ( Num1 = 0, 0, INSTR ( Num1 + 1, MWith, '-' ) ) AS Num2
                                                                                                                                            FROM      (
                                                                                                                                                                SELECT    MFrom,
                                                                                                                                                                          MWith,
                                                                                                                                                                          INSTR ( MWith, '-' ) AS Num1
                                                                                                                                                                FROM      (
                                                                                                                                                                                    SELECT    LEFT ( m_ofl.Notes, INSTR ( m_ofl.Notes, '|' ) - 1 ) AS MFrom,
                                                                                                                                                                                              IIF ( INSTR ( repl.Notes, ',' ) > 0, LEFT ( repl.Notes, INSTR ( repl.Notes, ',' ) - 1 ), repl.Notes ) AS MWith
                                                                                                                                                                                    FROM      tblInvoice AS repl
                                                                                                                                                                                              INNER JOIN (
                                                                                                                                                                                                        SELECT    OrderNumber,
                                                                                                                                                                                                                  Notes
                                                                                                                                                                                                        FROM      tblInvoice
                                                                                                                                                                                                        WHERE     StatusCode = 4
                                                                                                                                                                                                                  AND INSTR ( Notes, '|' ) > 0
                                                                                                                                                                                              ) AS m_ofl ON m_ofl.OrderNumber = repl.OrderNumber
                                                                                                                                                                                    WHERE     repl.RecordIDType = 'STAT'
                                                                                                                                                                                              AND repl.StatusCode = 7
                                                                                                                                                                                              AND repl.Notes NOT LIKE '%abc%'
                                                                                                                                                                                              AND repl.Notes NOT LIKE '%abc2%'
                                                                                                                                                                                              AND repl.Notes NOT LIKE '%abc3%'
                                                                                                                                                                                              AND
```

(I could not paste all the code, too many characters)
Wich is way to much indenting for me....

Also, where whould I have to change your code to make it act as if {} brackets are like a string ? I don't want your formatter to make any changes between the {} brackets, so how do I do that ?

----------


## si_the_geek

I disagree with your spacing amounts, but agree with making it optional  :Smilie:   I have done that, and changed the default subquery indent size to be just over the normal indent, rather than double it.

To make {} act as a string, the best course of action would probably be to add an extra bit of code before/after the strings are removed to store the {} 'strings' to a separate array, then re-insert them after/before the strings (opposite of what you did above). 

Unfortunately the strings code can't really be altered for this easily enough, as all other markers are the same for start/end, whereas these differ - a modified version ofthe strings code would work well tho.  On the plus side, for this you would only need a one dimensional array - as you know the markers.

----------


## CVMichael

When I call it like this:
    Me.txtQuery.Text = FormatSelect(Me.txtQuery.Text, False, 4, 8)

I get an error at this line (In FormatSelect_Worker):

VB Code:
'add keyword (Ucase), and spaces to indent
sRetString = sRetString & aKeywords(lThisSectionType) & Space(Len(sIndent) - Len(aKeywords(lThisSectionType)))
But I fixed the problem by changing the code to this:

VB Code:
'add keyword (Ucase), and spaces to indent
      If Len(sIndent) - Len(aKeywords(lThisSectionType)) <= 0 Then
        sRetString = sRetString & aKeywords(lThisSectionType) & " "
      Else
        sRetString = sRetString & aKeywords(lThisSectionType) & Space(Len(sIndent) - Len(aKeywords(lThisSectionType)))
      End If

----------


## si_the_geek

Ah yes, I should have checked a few values like that!

I've updated using basically the same code you posted, with just a minor variation of the If for efficiency:
VB Code:
If Len(aKeywords(lThisSectionType)) >= lIndentSize Then

----------


## avihayt

i tried this on the odbc commands and it got problem ...

exec sp_executesql N'SELECT "pk"  FROM "dbo"."m_clients" WHERE id = @P1 AND closing_status = @P2 AND calc_status = @P3 AND opening_effec_date <= @P4 AND closing_effec_date > @P5 ', N'@P1 int,@P2 int,@P3 int,@P4 datetime,@P5 datetime', 11893738, 20, 0, 'Jul  1 2004 12:00:00:000AM', 'Jul  1 2004 12:00:00:000AM'

exec sp_executesql N'INSERT INTO "dbo"."p_costing_sheet_results" ("policy_no","cover_no","benefit_type","role","role_id","membership_category_no","series","opening_s  tatus","opening_reg_date","opening_effec_date","opening_ref","closing_status","closing_reg_date","cl  osing_effec_date","closing_ref","member_id","gender","entry_age","from_age","to_age","salary","pensi  onable_salary","quantity","annual_premium","annual_basic_premium","sum_assured_amount","basic_premiu  m_rate","total_restricted_sa_amount","capitalised_sum_assured","last_charging_date","current_net_ann  ual_premium","prev_net_annual_premium","periodic_premium","action_code","adjustment_code","effec_dat  e_adjustment_feed","charging_season_factor","event_date","dob","occupation","internal_cover_no","clo  sing_effec_date_at_term","restricted_sa_max_benefit","total_sa_for_high_earners","capitalise_factor"  ,"factor_for_retiree","closed_record_invoiced_date","benefit_type_param1","ref_date_time_changes"  ) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P2  2,@P23,@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40,@P41,@P4  2,@P43,@P44,@P45,@P46,@P47,@P48,@P49)', N'@P1 float,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 datetime,@P10 datetime,@P11 varchar(255),@P12 int,@P13 datetime,@P14 datetime,@P15 varchar(255),@P16 int,@P17 int,@P18 int,@P19 int,@P20 int,@P21 float,@P22 float,@P23 int,@P24 float,@P25 float,@P26 float,@P27 float,@P28 float,@P29 float,@P30 datetime,@P31 float,@P32 float,@P33 float,@P34 int,@P35 int,@P36 datetime,@P37 float,@P38 datetime,@P39 datetime,@P40 int,@P41 int,@P42 datetime,@P43 float,@P44 float,@P45 float,@P46 float,@P47 datetime,@P48 int,@P49 datetime', 6.348980000000000e+005, 9000, 0, 13, 11893738, 4, 0, 10, 'Oct  5 2005 12:00:00:000AM', 'Jul  1 2005 12:00:00:000AM', '', 0, 'Jan  1 3000 12:00:00:000AM', 'Jan  1 3000 12:00:00:000AM', '', 12605586, 2, 37, 37, 0, 2.565040000000000e+004, 0.000000000000000e+000, 1, 2.360000000000000e+001, 2.360000000000000e+001, 5.130100000000000e+004, 4.600000000000000e-001, 5.130080000000000e+004, 0.000000000000000e+000, 'Jan  1 1900 12:00:00:000AM', 2.139359900000000e+001, 0.000000000000000e+000, 2.360000000000000e+001, 1, 0, 'Jan  1 1900 12:00:00:000AM', 1.000000000000000e+000, 'Jan  1 1900 12:00:00:000AM', 'Jul 11 1968 12:00:00:000AM', 0, 0, 'Jan  1 1900 12:00:00:000AM', 5.130080000000000e+004, 0.000000000000000e+000, 0.000000000000000e+000, 1.000000000000000e+000, 'Jan  1 1900 12:00:00:000AM', 0, 'Jan  1 1900 12:00:00:000AM'

----------


## CVMichael

avihayt , it works only for SELECT statements...

----------


## si_the_geek

Yep, if you pass it just the text from "SELECT" up to the first "@P5" then it works.

The second statement is not compatible at all at this time, as I have not allowed for action statements (Insert/Update/etc) yet.

I am not sure if I at some point in the future I will allow for SP's or commands such as Exec.

----------


## ajviradia

si,

   Are you still working on this formatting project? I am working on VB.Net project which can use your formatting function. My project basically allow in place formatting in SQL QUERY ANALYZER using Hooks.

- AJ  :wave:

----------


## si_the_geek

Hi ajviradia, welcome to VBForums!  :wave: 

I haven't done anything more to it yet, the trouble is that there are many different formats for Inserts (for example) depending on what DBMS you are using.  I may have a go at adding more this weekend, assuming I am up to date on my work!

Your use of it sounds good, I'd like to see that when you get it working.  :Smilie:

----------


## chocoloco

Why not just format the Sql string while typing??? Why all this fuss??

----------


## si_the_geek

I originally designed it because we had lots of threads along the lines of "what is the error in this SQL statement?", and they didn't have any formatting at all (just one long string!).

That meant we had to manually format the SQL statements before we could attempt to answer.  I realised that the amount of time I would save by having the code offset the time to write it.. and thankfully it was easy to add basic error checking too.


It also allows for those times when you are given an existing project to support, and the SQL statements aren't formatted (from my experience, this is often the case!).

----------


## Tobiasgar

Thanx)) using it really makes everything easier:  making changes and  seeing mistakes much more quickly,notifying errors.. :Thumb:

----------


## barnesa127

Hi, nice code!

I noticed the VB formatter doesn't handle simple UNIONs, eg
"SELECT * FROM A UNION SELECT * FROM B"

I am using VBA 6.5

----------


## si_the_geek

Welcome to VBForums  :wave: 

Thanks for your comments... I can confirm that it doesn't handle Unions at the moment (I'm surprised nobody has mentioned it before!), and I will try to add that in the next few days.

----------


## davidcarta

This project was incredibly helpful to me, so I figured I would post my changes to make this VB.NET compliant.  Nothing else seemed to be out there and completely free.

Best Regards to all,

David

----------


## getusama

Hi

I would really like to try this module out but need help please. 
Using Excel and/or Access how can I make use of this module?

Thanks in advance

----------


## si_the_geek

You should be able to add the .bas file by right-clicking in the Project Explorer window (or whatever it is called where you can see the code files), make sure you right-click on a folder rather than a file.

If that doesn't work just create a new module, then use Notepad to copy the text of the .bas file (except the first line), and paste the text into the new module.


You can then use it as shown earlier in this thread.

----------


## getusama

> You should be able to add the .bas file by right-clicking in the Project Explorer window (or whatever it is called where you can see the code files), make sure you right-click on a folder rather than a file.
> 
> If that doesn't work just create a new module, then use Notepad to copy the text of the .bas file (except the first line), and paste the text into the new module.
> 
> 
> You can then use it as shown earlier in this thread.


Alright thanks I think the best thing for me to do woudl be to go through some tutorials and try to figure out how I can write something to implement this.

----------

