Hi all.

I'm working on SLX LAN v7.2.1.

I'm creating a datagrid dynamically and all the fields except one is appearing on the datagrid with incorrect data.

The field is of type numeric in the table, some data in that field has up to 4 decimal places. 

For example, some of the data in that field is: 12.5687, 10.2258, .6358

But when I create the datagrid and display the field the data on the datagrid appears as something totally different: 187832344, -89766577, 2554578

What's going on?

I ran the SLXProfiler and when I look at the executed query, it shows the correct data: 12.5687, 10.2258, .6358 etc.

The field in question is STDCOST 

Thank you in advance!

Below is a snippet of my code:

<p>With Datagrid1
 
      strSQL_Monthly = "SELECT sysdba.C_VOLUMEANALYSIS_ALL.CUSTOMERNUMBER,sysdba.C_VOLUMEANALYSIS_ALL.CUSTOMERNAME, sysdba.C_VOLUMEANALYSIS_ALL.ITEMGROUP,sysdba.C_VOLUMEANALYSIS_ALL.OEMDESCRIPTION,sysdba.C_VOLUMEANALYSIS_ALL.BRAND,sysdba.C_VOLUMEANALYSIS_ALL.ItemNumber,sysdba.C_VOLUMEANALYSIS_ALL.UnitsPerCase,sysdba.C_VOLUMEANALYSIS_ALL.STDCOST,SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear1.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 1 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS JAN_1, SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear1.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 2 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS FEB_1,"

      strSQL_Monthly = strSQL_Monthly & "SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear1.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 3 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS MAR_1, SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear1.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 4 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS APR_1,"

      strSQL_Monthly = strSQL_Monthly & "SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear1.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 5 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS MAY_1, SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear1.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 6 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS JUN_1,"

      strSQL_Monthly = strSQL_Monthly & "SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear1.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 7 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS JUL_1, SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear1.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 8 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS AUG_1,"

      strSQL_Monthly = strSQL_Monthly & "SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear1.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 9 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS SEP_1, SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear1.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 10 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS OCT_1,"

      strSQL_Monthly = strSQL_Monthly & "SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear1.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 11 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS NOV_1, SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear1.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 12 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS DEC_1,"

      strSQL_Monthly = strSQL_Monthly & "SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear2.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 1 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS JAN_2, SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear2.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 2 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS FEB_2,"

      strSQL_Monthly = strSQL_Monthly & "SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear2.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 3 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS MAR_2, SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear2.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 4 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS APR_2,"

      strSQL_Monthly = strSQL_Monthly & "SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear2.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 5 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS MAY_2, SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear2.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 6 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS JUN_2,"

      strSQL_Monthly = strSQL_Monthly & "SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear2.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 7 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS JUL_2, SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear2.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 8 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS AUG_2,"

      strSQL_Monthly = strSQL_Monthly & "SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear2.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 9 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS SEP_2, SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear2.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 10 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS OCT_2,"

      strSQL_Monthly = strSQL_Monthly & "SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear2.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 11 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS NOV_2, SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear2.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 12 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS DEC_2"

      strSQL_Monthly = strSQL_Monthly & " FROM sysdba.C_VOLUMEANALYSIS_ALL WHERE sysdba.C_VOLUMEANALYSIS_ALL.DIVISION = 'Distributors' AND  ((sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear1.Text & "') OR (sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear2.Text & "'))  GROUP BY sysdba.C_VOLUMEANALYSIS_ALL.CUSTOMERNUMBER,sysdba.C_VOLUMEANALYSIS_ALL.CUSTOMERNAME, sysdba.C_VOLUMEANALYSIS_ALL.ITEMGROUP,sysdba.C_VOLUMEANALYSIS_ALL.OEMDESCRIPTION,sysdba.C_VOLUMEANALYSIS_ALL.BRAND,sysdba.C_VOLUMEANALYSIS_ALL.ItemNumber,sysdba.C_VOLUMEANALYSIS_ALL.UnitsPerCase,sysdba.C_VOLUMEANALYSIS_ALL.STDCOST"
        .SQL.Text = strSQL_Monthly
            With .Columns
                If (.Count > 0) Then
                    For i_Monthly = 0 To .Count - 1
                        .Item(0).Delete
                    Next
                End If 

             Set col_Monthly = .Add(0)

             col_Monthly.FieldName = "CUSTOMERNUMBER"

             col_Monthly.Caption = "Customer No"

             col_Monthly.Readonly = True

             col_Monthly.Width = 110

             col_Monthly.Alignment = 2

             col_Monthly.HeaderAlignment = 2
             Set col_Monthly = .Add(0)
             col_Monthly.FieldName = "CUSTOMERNAME"
             col_Monthly.Caption = "Customer Name"
             col_Monthly.Readonly = True
             col_Monthly.Width = 90
             col_Monthly.Alignment = 2
             col_Monthly.HeaderAlignment = 2 

             Set col_Monthly = .Add(0)
             col_Monthly.FieldName = "ItemNumber"
             col_Monthly.Caption = "Item Number"
             col_Monthly.Readonly = True
             col_Monthly.Width = 90
             col_Monthly.Alignment = 2
             col_Monthly.HeaderAlignment = 2
 
             Set col_Monthly = .Add(0)
             col_Monthly.FieldName = "UnitsPerCase"
             col_Monthly.Caption = "U/Case"
             col_Monthly.Readonly = True
             col_Monthly.Width = 60
             col_Monthly.Alignment = 2
             col_Monthly.HeaderAlignment = 2 

             Set col_Monthly = .Add(0)
             col_Monthly.FieldName = "STDCOST"
             col_Monthly.Caption = "Std Cost"
             col_Monthly.Readonly = True
             col_Monthly.Width = 60
             col_Monthly.Alignment = 2
             col_Monthly.HeaderAlignment = 2

 End With
 
        .Refresh

        End With
            Datagrid1.Visible = True

You are adding them all as

Set col_Monthly = .Add(0)

But, you would need to set the type/format as well. Otherwise, they just go in as undefined/general. You would want type (13)

===================
DataGrid Col Types
===================

0 = Standard
1 = Mask
2 = Button
3 = Date
4 = CheckBox
5 = Image
6 = Spin
8 = Combo
9 = Calculator
10 = PictureBlob
11 = Hyperlink
12 = Time
13 = Currency
14 = Memo
15 = Picklist

Thanks for the reply Mike.

I did as you suggested and added the 13 instead of the 0 for the STDCOST column but now it adds a $ to the displayed data which is still wrong: $187832344.00, $-89766577.00, $2554578.00

When it should be $12.5687, $10.2258, $.6358

You've probably misaligned the columns in the recordset with the columns requested. If you have 10 cols in the RS then you'll need 10 in the grid definition (plus any ID's etc. that SLX will add - even if you then need to hide them).

Probably easiest if you simplify this initially. Do a simple select col1,col2 from table1 and then match up the dynamic grid cols with the RS just to make sure. Then add & complicate later. You won't find anyone willing to debug what you have !

Also, use SLXProfiler to examine the query sent, with the query executed and, again, double check the cols in the RS with those in the dynamic grid.

Ok when I remove the SUM() parts of the query it correctly displays the STDCOST field. But when I add just one SUM() as below, that's when it displays garbage data. Is it that it doesn't like SUM()?

<p>strSQL_Monthly = "SELECT sysdba.C_VOLUMEANALYSIS_ALL.CUSTOMERNUMBER,sysdba.C_VOLUMEANALYSIS_ALL.CUSTOMERNAME,sysdba.C_VOLUMEANALYSIS_ALL.ITEMGROUP,sysdba.C_VOLUMEANALYSIS_ALL.OEMDESCRIPTION,sysdba.C_VOLUMEANALYSIS_ALL.BRAND,sysdba.C_VOLUMEANALYSIS_ALL.ItemNumber,sysdba.C_VOLUMEANALYSIS_ALL.UnitsPerCase,sysdba.C_VOLUMEANALYSIS_ALL.STDCOST,SUM(CASE WHEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear1.Text & "' AND sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDMONTH = 1 THEN sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDVOLUME ELSE 0 END) AS JAN_1"
 

      strSQL_Monthly = strSQL_Monthly & " FROM sysdba.C_VOLUMEANALYSIS_ALL WHERE sysdba.C_VOLUMEANALYSIS_ALL.DIVISION = 'Distributors' AND  ((sysdba.C_VOLUMEANALYSIS_ALL.REPORTEDYEAR = '" & cmbYear1.Text & "') ) GROUP BY sysdba.C_VOLUMEANALYSIS_ALL.CUSTOMERNUMBER,sysdba.C_VOLUMEANALYSIS_ALL.CUSTOMERNAME, sysdba.C_VOLUMEANALYSIS_ALL.ITEMGROUP,sysdba.C_VOLUMEANALYSIS_ALL.OEMDESCRIPTION,sysdba.C_VOLUMEANALYSIS_ALL.BRAND,sysdba.C_VOLUMEANALYSIS_ALL.ItemNumber,sysdba.C_VOLUMEANALYSIS_ALL.UnitsPerCase,sysdba.C_VOLUMEANALYSIS_ALL.STDCOST"

        .SQL.Text = strSQL_Monthly

 

With .Columns

 

                If (.Count > 0) Then

 

                    For i_Monthly = 0 To .Count - 1

 

                        .Item(0).Delete

                    Next

 

                End If

 

             Set col_Monthly = .Add(0)

             col_Monthly.FieldName = "CUSTOMERNUMBER"

             col_Monthly.Caption = "Customer No"

             col_Monthly.Readonly = True

             col_Monthly.Width = 110

             col_Monthly.Alignment = 2

             col_Monthly.HeaderAlignment = 2

 

            Set col_Monthly = .Add(0)

             col_Monthly.FieldName = "CUSTOMERNAME"

             col_Monthly.Caption = "Customer Name"

             col_Monthly.Readonly = True

             col_Monthly.Width = 90

             col_Monthly.Alignment = 2

             col_Monthly.HeaderAlignment = 2

 

            Set col_Monthly = .Add(0)

             col_Monthly.FieldName = "ItemGroup"

             col_Monthly.Caption = "Item Group"

             col_Monthly.Readonly = True

             col_Monthly.Width = 70

             col_Monthly.Alignment = 2

             col_Monthly.HeaderAlignment = 2

 

            Set col_Monthly = .Add(0)

             col_Monthly.FieldName = "OEMDESCRIPTION"

             col_Monthly.Caption = "OEM Description"

             col_Monthly.Readonly = True

             col_Monthly.Width = 140

             col_Monthly.Alignment = 2

             col_Monthly.HeaderAlignment = 2

 

            Set col_Monthly = .Add(0)

             col_Monthly.FieldName = "Brand"

             col_Monthly.Caption = "Brand"

             col_Monthly.Readonly = True

             col_Monthly.Width = 100

             col_Monthly.Alignment = 2

             col_Monthly.HeaderAlignment = 2

 

             Set col_Monthly = .Add(0)

             col_Monthly.FieldName = "ItemNumber"

             col_Monthly.Caption = "Item Number"

             col_Monthly.Readonly = True

             col_Monthly.Width = 90

             col_Monthly.Alignment = 2

             col_Monthly.HeaderAlignment = 2

 

 

             Set col_Monthly = .Add(0)

             col_Monthly.FieldName = "UnitsPerCase"

             col_Monthly.Caption = "U/Case"

             col_Monthly.Readonly = True

             col_Monthly.Width = 60

             col_Monthly.Alignment = 2

             col_Monthly.HeaderAlignment = 2

 

             Set col_Monthly = .Add(0)

             col_Monthly.FieldName = "STDCOST"

             col_Monthly.Caption = "Std Cost"

             col_Monthly.Readonly = True

             col_Monthly.Width = 60

             col_Monthly.Alignment = 2

             col_Monthly.HeaderAlignment = 2

 

 

             Set col_Monthly = .Add(0)

             col_Monthly.FieldName = "JAN_1"

             col_Monthly.Caption = "JAN " + cmbYear1.Text

             col_Monthly.Readonly = True

             col_Monthly.Width = 90

             col_Monthly.Alignment = 2

             col_Monthly.HeaderAlignment = 2

 

End With

 

        .Refresh

 

        End With

 

            Datagrid1.Visible = True

SUM/COUNT etc. are all fine.

Firstly - remove all references to SYSDBA - they aren't needed.

Then, remove all refs to tables - they shouldn't be fully qualifed - but, rather, aliased

e.g. select a1.lastname from contact a1

Then, add an alias to the Sum e.g.

select sum(x) as x from blah

Then, in the .FieldName use "X" - I know you do that within the CASE - but just to be sure.

Ok I removed the refs to tables and replaced it with aliases. My sum already had an alias JAN_1. I ran it and still get garbage data:

strSQL_Monthly = "SELECT t1.ItemNumber,t1.UnitsPerCase,t1.STDCOST,SUM(CASE WHEN t1.REPORTEDYEAR = '2012' AND t1.REPORTEDMONTH = 1 THEN t1.REPORTEDVOLUME ELSE 0 END) AS JAN_1 FROM C_VOLUMEANALYSIS_ALL t1  WHERE t1.DIVISION = 'Distributors' AND  ((t1.REPORTEDYEAR = '2012') ) GROUP BY t1.CUSTOMERNUMBER,t1.CUSTOMERNAME, t1.ITEMGROUP,t1.OEMDESCRIPTION,t1.BRAND,t1.ItemNumber,t1.UnitsPerCase,t1.STDCOST,t1.REPORTEDVOLUME"

 .SQL.Text = strSQL_Monthly

With .Columns

                If (.Count > 0) Then

                    For i_Monthly = 0 To .Count - 1

                        .Item(0).Delete

                    Next

                End If

Set col_Monthly = .Add(0)

             col_Monthly.FieldName = "ItemNumber"

             col_Monthly.Caption = "Item Number"

             col_Monthly.Readonly = True

             col_Monthly.Width = 90

             col_Monthly.Alignment = 2

             col_Monthly.HeaderAlignment = 2

             Set col_Monthly = .Add(0)

             col_Monthly.FieldName = "UnitsPerCase"

             col_Monthly.Caption = "U/Case"

             col_Monthly.Readonly = True

             col_Monthly.Width = 60

             col_Monthly.Alignment = 2

             col_Monthly.HeaderAlignment = 2

            Set col_Monthly = .Add(0)

             col_Monthly.FieldName = "STDCOST"

             col_Monthly.Caption = "Std Cost"

             col_Monthly.Readonly = True

             col_Monthly.Width = 60

             col_Monthly.Alignment = 2

             col_Monthly.HeaderAlignment = 2

             Set col_Monthly = .Add(0)

             col_Monthly.FieldName = "JAN_1"

             col_Monthly.Caption = "JAN " + cmbYear1.Text

             col_Monthly.Readonly = True

             col_Monthly.Width = 90

             col_Monthly.Alignment = 2

             col_Monthly.HeaderAlignment = 2

End With

        .Refresh

        End With

            Datagrid1.Visible = True

Here's what some of the output looks like:

Sorry, don't know what happened.

ItemNumber---UnitsPerCase---StdCost---Jan_1

344445---10---0.6166---0

123445---8---3.753---10

333566---10---3.719---20

656454---20---3.719---0

Dig the bones out of this.

<p>option explicit
 

Dim strMainSQL

strMainSQL = "SELECT " &_
             "CASE " &_
             "WHEN A2.TYPE = 'P' THEN 'Template' " &_
             "WHEN A2.TYPE = 'W' THEN 'Admin' " &_
             "WHEN A2.TYPE = 'M' THEN 'Remote' " &_
             "WHEN A2.TYPE = 'R' THEN 'Retired' " &_
             "WHEN A2.TYPE = 'N' THEN 'Network' " &_
             "WHEN A2.TYPE = 'V' THEN 'WebViewer' " &_
             "WHEN A2.TYPE = 'T' THEN 'WebOnly' " &_
             "WHEN A2.TYPE = 'C' THEN 'Concurrent' " &_
             "ELSE 'Unknown' " &_
             "END as Type, A1.USERID, A1.LASTNAME LastName, A1.REGION Region, A3.SLX_VERSION SalesLogixVsn, A1.USERNAME UserName, " & _
             "A3.LOGIN5 MostRecentLogin, A3.LOGIN4 Login4, A3.LOGIN3 Login3, A3.LOGIN2 Login2, A3.LOGIN1 OldestLogin, A3.MACNAME MachineName, A3.LOGNAME LoginName, " & _
             "A3.LOGOUT5 MostRecentLogout, A3.LOGOUT4 Logout4, A3.LOGOUT3 Logout3, A3.LOGOUT2 Logout2, A3.LOGOUT1 OldestLogout " & _
             "FROM USERINFO A1 INNER JOIN USERSECURITY A2 ON (A1.USERID=A2.USERID) LEFT OUTER JOIN EM_AUDIT A3 " & _
             "ON (A1.USERID=A3.EM_AUDITID)"

<p>Sub AXFormOpen(Sender)
    PopulateGrid
End Sub
 

<p>Sub PopulateGrid
    Dim strGridSQL
    Dim objRS, objSLXDB, objUsers
    Dim col
    Dim i
    Set objSLXDB = New SLX_DB
 

     If cbShowRetired.Text = "T" then
        strGridSQL = strMainSQL & " WHERE A2.TYPE <> 'R'"
        Else
        strGridSQL = strMainSQL
     End If

     strGridSQL = strGridSQL & " ORDER BY A1.LASTNAME, A2.TYPE ASC"

    Set objUsers = objSLXDB.GetNewRecordSet

    objUsers.Open strGridSQL, objSLXDB.Connection
    objUsers.ActiveConnection = Nothing

    'Remove any records from the Users grid
    With dgUserAudit
        Set .Recordset = objUsers
        If (.Columns.Count > 0) Then
           For i = 0 To .Columns.Count - 1
               .Columns.Item(0).Delete
           Next
        End If
    End With

    'Define columns for Users grid
    With dgUserAudit
         'LastName
         Set col = .Columns.Add(0)
         col.FieldName = "LastName"  'DNL
         col.Caption = "LastName"  'DNL
         col.Width = 120
         'Type
         Set col = .Columns.Add(0)
         col.FieldName = "TYPE"  'DNL
         col.Caption = "Type"  'DNL
         col.Width = 90
         'Region
         Set col = .Columns.Add(0)
         col.FieldName = "REGION"  'DNL
         col.Caption = "Region"  'DNL
         col.Width = 90
         'Sales Logix Version
         Set col = .Columns.Add(0)
         col.FieldName = "SalesLogixVsn"  'DNL
         col.Caption = "SLX Vsn"  'DNL
         col.Width = 80
         'UserName
         Set col = .Columns.Add(0)
         col.FieldName = "UserName"  'DNL
         col.Caption = "UserName"  'DNL
         col.Width = 100
         'Login 5 (Most Recent)
         Set col = .Columns.Add(0)
         col.FieldName = "MostRecentLogin"  'DNL
         col.Caption = "Most Recent Login"  'DNL
         col.Width = 110
         'Login 4
         Set col = .Columns.Add(0)
         col.FieldName = "Login4"  'DNL
         col.Caption = "4"  'DNL
         col.Width = 110
         'Login 3
         Set col = .Columns.Add(0)
         col.FieldName = "Login3"  'DNL
         col.Caption = "3"  'DNL
         col.Width = 110
         'Login 2
         Set col = .Columns.Add(0)
         col.FieldName = "Login2"  'DNL
         col.Caption = "2"  'DNL
         col.Width = 110
         'Login 1 (Oldest)
         Set col = .Columns.Add(0)
         col.FieldName = "OldestLogin"  'DNL
         col.Caption = "Oldest Login"  'DNL
         col.Width = 110

         Set col = .Columns.Add(0)
         col.FieldName = "MachineName"  'DNL
         col.Caption = "Machine Name"  'DNL
         col.Width = 110

         Set col = .Columns.Add(0)
         col.FieldName = "LoginName"  'DNL
         col.Caption = "Login Name"  'DNL
         col.Width = 110

         'Logout 5 (Most Recent)
         Set col = .Columns.Add(0)
         col.FieldName = "MostRecentLogout"  'DNL
         col.Caption = "Most Recent Logout"  'DNL
         col.Width = 110
         'Logout 4
         Set col = .Columns.Add(0)
         col.FieldName = "Logout4"  'DNL
         col.Caption = "4"  'DNL
         col.Width = 110
         'Logout 3
         Set col = .Columns.Add(0)
         col.FieldName = "Logout3"  'DNL
         col.Caption = "3"  'DNL
         col.Width = 110
         'Logout 2
         Set col = .Columns.Add(0)
         col.FieldName = "Logout2"  'DNL
         col.Caption = "2"  'DNL
         col.Width = 110
         'Logout 1 (Oldest)
         Set col = .Columns.Add(0)
         col.FieldName = "OldestLogout"  'DNL
         col.Caption = "Oldest Logout"  'DNL
         col.Width = 110


    .ReadOnly = True
    .RowSelect = True
    .Sortable = True
    .Active = True

    End With
End Sub

Basically, run the query (your group/sum query) and set the grid recordset to the RS from the query itself.

Thanks Mike. I'll work on this and let you know how it goes.

I always add a hidden column for the row ID field that is probably being added by SLX automatically to its SQL statement.

Mike, (and others): I for the life of me can't figure out what you are doing here, it shows up EVERYWHERE including Ryan Farley's how to guide, your code above and SLX VB scripts:

   'Remove any records from the Users grid
    With dgUserAudit
        Set .Recordset = objUsers
        If (.Columns.Count > 0) Then
           For i = 0 To .Columns.Count - 1
               .Columns.Item(0).Delete
           Next
        End If
    End With


Then you rebuild the columns.
OVER AND OVER AND OVER again. If a user goes to the tab 1000 times, and does a ShowViewForRecord\AddEdit Option Popup (which automagically fires off an AXFormChange which fires off your DELETE the columns and rebuild sequence).

WHY!

1. Start out with NO columns in your datagrid in the SLX Architect.
2. 
   'Remove any records from the Users grid
    With dgUserAudit
       -- NOT NEEDED: Set .Recordset = objUsers
        If (.Columns.Count > 5) Then '''yes, we have ALREADY BUILD THE COLUMNS, NO NEED TO DELETE THEM, NO NEED TO REBUILD THEM AGAIN.
    ELSE
        Build all of your columns here, including columns for hidden ID fields!
    END IF

GO out and fetch your data into an ADO Recordset.....and then
 Set .Recordset = MyADORS with ALL of my data in it
''' and no you don't have to set .SQL.TEXT if you've slammed a recordset into the grid.
''' and NO you don't have to .Refresh the grid, that's just yet another waste of processing time.
    End With

With as much experience as we've had in building grids, I am surprised to find any SLX Developed still Deleting and Rebuilding the Columns in a Grid control on every Refresh...
Actually not surprised, more disappointed.

Come now RJ you can't be disappointed - that would just be sad.

you have NO context of where this is being used (quick hint, it's the user audit). It can't be used MILLIONS of times - it's used ONCE, on form invocation.
You also have ZERO context of how old this code is. The fact is, just grabbed it to help, didn't say it would work, didn't say it was the best code out there! In fact, I don't remember the last time I did it this way either.

I never said to use it in a tab did I ? If so, please point me to that....

You would do better to educate rather than try and slam your opinion into someone skull !

Point taken.

But you still have one extra recreation of the columns, and that's fact, not opinion.