binary1
asked on
whats wrong with my code????
i wrote a small program to access the db and place it into msflexgrid. However i don't know waht is wrong in my sql statement, it give me the ff error"you tried to execute the query that does not included the specified expression 'username' as part of aggregate function" however if i replace the field name to *, it work.
can anyone here tell me also how to populate the data to the next row, because i only know how to populate the first row
here is my db
id(auto_no) username(text) userid(no.)ldate(date)ltim e(date/tim e)event(te xt)
Private Sub Form_Load()
Dim line_text As String
Dim i
Dim cnESS
Set cnESS = New ADODB.Connection
With cnESS
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "c:\log\log.mdb"
.Open , "", ""
End With
Dim rsESS As New ADODB.Recordset
With rsESS
.ActiveConnection = cnESS
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
End With
rsESS.Source = "select username, userid, ldate,event, max(ltime), min(ltime) from log_monitor" group by ldate, userid"
rsESS.Open , , , , adCmdText
'While Not rsESS.EOF
With msf
.ColWidth(0) = 1500
.Col = 0
.Text = rsESS(1)
.ColWidth(1) = 300
.Col = 1
.Text = rsESS(2)
.ColWidth(2) = 1000
.Col = 2
.Text = rsESS(3)
.ColWidth(3) = 1000
.Col = 3
.Text = rsESS(4)
.ColWidth(4) = 1000
.Col = 4
.Text = rsESS(5)
End With
'MsgBox (rsESS("ltime"))
Close #1
rsESS.Close
'MsgBox ("Finish Convert")
End Sub
can anyone here tell me also how to populate the data to the next row, because i only know how to populate the first row
here is my db
id(auto_no) username(text) userid(no.)ldate(date)ltim
Private Sub Form_Load()
Dim line_text As String
Dim i
Dim cnESS
Set cnESS = New ADODB.Connection
With cnESS
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "c:\log\log.mdb"
.Open , "", ""
End With
Dim rsESS As New ADODB.Recordset
With rsESS
.ActiveConnection = cnESS
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
End With
rsESS.Source = "select username, userid, ldate,event, max(ltime), min(ltime) from log_monitor" group by ldate, userid"
rsESS.Open , , , , adCmdText
'While Not rsESS.EOF
With msf
.ColWidth(0) = 1500
.Col = 0
.Text = rsESS(1)
.ColWidth(1) = 300
.Col = 1
.Text = rsESS(2)
.ColWidth(2) = 1000
.Col = 2
.Text = rsESS(3)
.ColWidth(3) = 1000
.Col = 3
.Text = rsESS(4)
.ColWidth(4) = 1000
.Col = 4
.Text = rsESS(5)
End With
'MsgBox (rsESS("ltime"))
Close #1
rsESS.Close
'MsgBox ("Finish Convert")
End Sub
If you are using a Group By (An aggregate function), then any fields you include in the Select must also appear in the Group By clause.
ASKER
if my db have ff feild
id (auto_no),username, userid, event, ltime, ldate
i need to get all the table fields while limit the selection of the first login time(min(ltime)) and last of the logout time(max(ltime))for every days for each user (group by username,ldata)
can anyone show me the right way of the sql statement
id (auto_no),username, userid, event, ltime, ldate
i need to get all the table fields while limit the selection of the first login time(min(ltime)) and last of the logout time(max(ltime))for every days for each user (group by username,ldata)
can anyone show me the right way of the sql statement
hi
Try this
rsESS.Source = "select username, userid, ldate,event, max(ltime), min(ltime) from log_monitor" group by ldate, userid,username,event,ltim e
"
rsESS.Open , , , , adCmdText
Good Luck
Jay
Try this
rsESS.Source = "select username, userid, ldate,event, max(ltime), min(ltime) from log_monitor" group by ldate, userid,username,event,ltim
"
rsESS.Open , , , , adCmdText
Good Luck
Jay
ASKER
hi jdhananjay
u code work by including all the fields of the table, however it don't filter out some records that between the first login and last logout of the ltime.
u code work by including all the fields of the table, however it don't filter out some records that between the first login and last logout of the ltime.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
opps. already two people answered. I guess i have to wait for the next question.
jdhananjay and mcix has given the answer for you friend. Go ahead.
jdhananjay and mcix has given the answer for you friend. Go ahead.
I see you used my code.. <smile>
1. Try This..
SELECT DISTINCT(ldate, userid),
username,
event,
max(ltime),
min(ltime)
FROM log_monitor
ORDER by ldate, userid
2. In the following code..
'While Not rsESS.EOF
With msf
.ColWidth(0) = 1500
.Col = 0
.Text = rsESS(1)
.ColWidth(1) = 300
.Col = 1
.Text = rsESS(2)
.ColWidth(2) = 1000
.Col = 2
.Text = rsESS(3)
.ColWidth(3) = 1000
.Col = 3
.Text = rsESS(4)
.ColWidth(4) = 1000
.Col = 4
.Text = rsESS(5)
End With
Reactivate the While loop and add a rsESS.MoveNext.
1. Try This..
SELECT DISTINCT(ldate, userid),
username,
event,
max(ltime),
min(ltime)
FROM log_monitor
ORDER by ldate, userid
2. In the following code..
'While Not rsESS.EOF
With msf
.ColWidth(0) = 1500
.Col = 0
.Text = rsESS(1)
.ColWidth(1) = 300
.Col = 1
.Text = rsESS(2)
.ColWidth(2) = 1000
.Col = 2
.Text = rsESS(3)
.ColWidth(3) = 1000
.Col = 3
.Text = rsESS(4)
.ColWidth(4) = 1000
.Col = 4
.Text = rsESS(5)
End With
Reactivate the While loop and add a rsESS.MoveNext.
try this:
rsESS.Source = "select 'username', 'userid', 'ldate','event', 'max(ltime)', 'min(ltime)' from 'log_monitor' group by 'ldate', 'userid' "
rsESS.Source = "select 'username', 'userid', 'ldate','event', 'max(ltime)', 'min(ltime)' from 'log_monitor' group by 'ldate', 'userid' "
Grid is FlexGrid
Screen.MousePointer = vbHourglass
grid.Cols = rs2.Fields.Count
grid.Row = 0
j = -1
For Each fld In rs2.Fields
j = j + 1
grid.TextMatrix(0, j) = fld.Name
Next fld
rs2.MoveNext
grid.Rows = rs2.RecordCount + 1
i = 0
rs2.MoveFirst
'Move through each row in the recordset
Do Until rs2.EOF
i = i + 1
grid.Row = i
'Loop through all fields
j = -1
For Each fld In rs2.Fields
j = j + 1
grid.TextMatrix(i, j) = fld
Next fld
rs2.MoveNext
Loop
Screen.MousePointer = vbDefault