SQL and MSflexgrid

[WeeBeng]

I have this data base which I am trying to access throught Data Access object ,DAO. I wrote the following code to select a part of the database.
[vbcode]
Private Sub Form_Load()
Dim db As DAO.Database
Dim ws As DAO.Workspace

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(App.Path & "\signals.mdb")
Dim rstbl As Recordset

Dim sql1 As String
Dim now As String
now = "11:27:35 AM"
sql1 = "SELECT [Date],[Time],[Mean_Power],[Time_of_Entry] FROM [Signal] Where [Time_of_Entry]=now "

Set rstbl = db.OpenRecordset(sql1, dbOpenDynaset)
End Sub
However I found the the recordset.recordcount to be zero. Using MSaccess, I designed the following query which does what I want and gives me a database with data.
SELECT Signal.Time_of_Entry, Signal.Date, Signal.Time, Signal.Min_Power
FROM Signal
WHERE (((Signal.Time_of_Entry)="11:27:35 AM"));

How should I code this query inside VB so that it performs the same function as the above query?
Is there also a simple way to insert all the data in the recordset into a MSFlexGrid?

Thanx

[O.K]

Hi,
at first: the .recordcount property does not contain the affected records, only those you have used. So do not use this property for getting the number of rows!
second: it seems to me that the column "Time_Of_Entry" is type DATETIME and you're SQL-Statement seems to be wrong:
I think you want to use the function NOW for getting actual time and date but you wrote it into the (SQL-)string, so you shoult use format(now) and add it to the string.
Bye
O.K

[dineshasanka]

sql1 = "SELECT [date],[time],[Mean_Power],[Time_of_Entry] FROM [Signal] Where [Time_of_Entry]= " & now & "




Dinesh Asanka

[Iouri]

1. Function Now will return Date abd time, that is why you cannot compare Time = Now.


2.If you use an MSFlexGrid control to display data returned in an ADO recordset, you can use this code to
dynamically populate the grid-including the header row-with the information in the recordset. You need an
open ADO recordset named rst and a form containing an MSFlexGrid control named msfGrid:

Dim cln As Field
With msfGrid
.Rows = 2
.Cols = rst.Fields.Count
'get the number of grid cols
.FixedRows = 1
.FixedCols = 0
.Row = 0
.Col = 0
For Each cln In rst.Fields
.Text = cln.Name
'populate header row with names of fields
If .Col < .Cols - 1 Then .Col = .Col + 1
Next
Do While Not rst.EOF
'loop thru recordset to populate grid
.Row = rst.AbsolutePosition
'move to the next row
.Col = 0
'reset ourselves back to column(0)
For Each cln In rst.Fields
If Not IsNull(cln.Value) Then
.Text = Trim(CStr(cln.Value))
Else
.Text = ""
End If
If .Col < .Cols - 1 Then .Col = .Col + 1
Next
rst.MoveNext
.Rows = .Rows + 1
'add a new row to the grid
Loop
.Rows = .Rows - 1
'remove the last row because it's blank
.Row = 0
End With


Iouri Boutchkine
[email protected]

[WeeBeng]

With regards to the first point, I declared my now to be a string in order to contain just the time and not date. Thus shouldn't it be possible to compare the time with now?

Is there any simple way to fill up the msflex grid using DAO and not ADO as I am not too familiar with ADO.

Thanx

[WeeBeng]

I tried using that sql statement but it still fail to work. Btw, now is a string containing the time, do I still need to use "&"?

Thanx.

[dammansky]

sql1 = "SELECT [date],[time],[Mean_Power],[Time_of_Entry] FROM [Signal] Where [Time_of_Entry]= '" & now & "'"

now is a function used by VB, so i would give the variable an other name (now1 f.e.g.)