I adapted the code below from steve.minnaar (can't thank you enough) for a
table that contains data dating back to 2002 and it works excellent for my
hours and amount fields, but the table also contain two other fields that are
numbers; the month period number (ITDPdNo) and the year (ITDYr) that are
summing up as well. These two fields are vital for my end users to select the
data they need.
Is there anyway to modify the code so that it does not sum these two field. I
can't change the fields to text as my sorts and parameters would not work
correctly. There is only the one table in this applications and it does not
contain a key id.
Would appreciate any suggestions,
Thanks in advance,
dry
Finally here's the grouping solution (work got in the way of play)
Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
' Populate the TableList List Box with all table names
Dim db As Database, Tbl As TableDef
Dim s As String
Set db = CurrentDb
s = ""
For Each Tbl In db.TableDefs
With Tbl
If Left$(.Name, 4) = "MSys" Then
' Skip system tables
Else
s = s & .Name & ";"
End If
End With
Next Tbl
Me!TableList.RowSource = s
End Sub
Private Sub TableList_AfterUpdate()
' Populate the FieldList List Box with all field names from the selected
table
Me!FieldList.RowSource = Me!TableList
End Sub
Private Sub btnGroupQuery_Click()
' Build the SQL to display the selected fields
' Create DynamicQ manually as, say, SELECT ID FROM Table1; and save it.
' This code will replace the SQL with each usage.
Dim db As Database, qd As QueryDef, ctl As Control, s As String, Item As
Variant
Dim Tbl As TableDef, Fld As Field, FieldName As String, Tail As String
Set db = CurrentDb
Set qd = db.QueryDefs("DynamicQ")
If IsNull(Me!TableList) Then
MsgBox "No table selected"
Else
Set Tbl = db.TableDefs(Me!TableList)
' Field Data type values
' 1 dbBoolean Boolean
' 2 dbByte Byte
' 3 dbInteger Integer
' 4 dbLong Long
' 5 dbCurrency Currency
' 6 dbSingle Single
' 7 dbDouble Double
' 8 dbDate Date / Time
' 9 dbBinary Binary
' 10 dbText Text
' 11 dbLongBinary Long Binary (OLE Object)
' 12 dbMemo Memo
' 15 dbGUID Guid
' 16 dbBigInt Big Integer
' 17 dbVarBinary VarBinary
' 18 dbChar Char
' 19 dbNumeric Numeric
' 20 dbDecimal Decimal
' 21 dbFloat Float
' 22 dbTime Time
' 23 dbTimeStamp Time Stamp
' Note that numeric types range from 2 to 7
s = "SELECT "
Tail = " GROUP BY "
' SELECT SalesPerson, Region, Sum(Amount) AS SumOfAmount
' FROM Sales
' GROUP BY SalesPerson, Region;
Set ctl = Me!FieldList
For Each Item In ctl.ItemsSelected
FieldName = ctl.ItemData(Item)
If Tbl.Fields(FieldName).Type 1 And Tbl.Fields(FieldName).Type
< 8 Then
s = s & "Sum([" & FieldName & "]) AS [SumOf" & FieldName &
"], "
Else
s = s & "[" & FieldName & "], "
Tail = Tail & "[" & FieldName & "], "
End If
Next Item
If s = "SELECT " Then
MsgBox "No fields selected"
Else
s = Left$(s, Len(s) - 2) & " FROM [" & Me!TableList & "]"
If Tail <" GROUP BY " Then s = s & Left$(Tail, Len(Tail) - 2)
qd.SQL = s & ";"
DoCmd.OpenQuery "DynamicQ"
End If
End If
End Sub
Private Sub btnSelectQuery_Click()
Dim db As Database, qd As QueryDef, ctl As Control, s As String, Item As
Variant
Set db = CurrentDb
Set qd = db.QueryDefs("DynamicQ")
s = "SELECT "
Set ctl = Me!FieldList
For Each Item In ctl.ItemsSelected
s = s & "[" & ctl.ItemData(Item) & "], "
Next Item
If s = "SELECT " Then
MsgBox "No fields selected."
Else
qd.SQL = Left$(s, Len(s) - 2) & " FROM [" & Me!TableList & "];"
DoCmd.OpenQuery "DynamicQ"
End If
End Sub
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200705/1
