Solved

whats wrong with my code????

Posted on 2000-03-27
9
176 Views
Last Modified: 2010-05-02
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)ltime(date/time)event(text)

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

0
Comment
Question by:binary1
9 Comments
 
LVL 6

Expert Comment

by:Marine
ID: 2662561
Try this.
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
0
 
LVL 4

Expert Comment

by:mcix
ID: 2662578
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.


 
0
 

Author Comment

by:binary1
ID: 2662818
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
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Expert Comment

by:jdhananjay
ID: 2662820
hi
 Try this

  rsESS.Source = "select username, userid, ldate,event, max(ltime), min(ltime) from log_monitor" group by ldate, userid,username,event,ltime

  rsESS.Open , , , , adCmdText

Good Luck
Jay
0
 

Author Comment

by:binary1
ID: 2662965
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.
0
 
LVL 1

Accepted Solution

by:
guyss earned 50 total points
ID: 2662972
if ldate and userid fully determine the
username and other field this wouldn't
be very efficient,

and easier and faster option is:
select first(username), userid, ldate,event, max(ltime), min(ltime) from log_monitor group by ldate, userid
0
 

Expert Comment

by:AshokKumar
ID: 2662989
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.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2663095
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.
0
 
LVL 1

Expert Comment

by:j3877
ID: 2663171
try this:

rsESS.Source = "select 'username', 'userid', 'ldate','event', 'max(ltime)', 'min(ltime)' from 'log_monitor' group by 'ldate', 'userid' " 
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now