Stumped by SQL

[msay]

The following is the code:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = DBEngine.OpenDatabase("c:\program files\Client Time\clienttimer.mdb")
'Open the Recordset
Set rst = db.OpenRecordset("TimeData")
Dim SQL As String
A = Combo1.Text
SQL = "SELECT SUM(totalTime) FROM TimeData WHERE name =" & Chr(34) & A & Chr(34)
Text1.Text = SQL

rst.Close
db.Close

This SQL returns this string: "SELECT SUM(totalTime) FROM TimeData WHERE name =" & Chr(34) & A & Chr(34)

Any help? I appreciate it!!

 

[SkipVought]

Hi,

Why isn't your sql

SQL = "SELECT SUM(totalTime) FROM TimeData WHERE name ='" &  A & "'"

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[msay]

Skip,
I tried that, but that still returns the SQL string itself.
That's why I'm stumped!

 

[SkipVought]

If you want to execute SQL code, you need to submit the code.

Dim db As DAO.Database
 Dim rst As DAO.Recordset
 Set db = DBEngine.OpenDatabase("c:\program files\Client Time\clienttimer.mdb")
   'Open the Recordset
 Dim SQL As String
 A = Combo1.Text
 SQL = "SELECT SUM(totalTime) FROM TimeData WHERE name ='" &  A & "'"
   Set rst = New ADODB.Recordset
   rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
   rst.movefirst
   Text1.Text = rst("SUM(totalTime)")
  rst.Close
  db.Close

maybe

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[johnwm]

Note small typo in Skip's code:
rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

should read:
rst.Open SQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[msay]

Skip, I cut and pasted your code (correcting typo)and I get a "User-defined type not defined" compile error on this part: New ADODB.Recordset
Just as a check what References need to be set to execute this? Sorry, I haven't used VB for some time and I'm a bit rusty!
Thanks.

 

[gmmastros]

Click Project->References
Select Microsoft ActiveX Data Objects 2.x Library

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SkipVought]

oops,

I was using ADO. You are using DAO. Sorry, I don't have an example at hand.

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[msay]

With Microsoft ActiveX Data Objects 2.x Library selected, code progresses to rst.open and produces the same error. ???

 

[Golom]

For DAO

Click Project->References
Select Microsoft DAO 3.6 Object Library

 

[msay]

Microsoft DAO 3.6 Object Library has been checked from the start. I typically always have that checked when doing SQL's like this. I think this is one of those times it's so simple I'm missing it!

 

[Golom]

The syntax for DAO is

Dim db As DAO.Database
 Dim rst As DAO.Recordset
 Set db = DBEngine.OpenDatabase("c:\program files\Client Time\clienttimer.mdb")
   'Open the Recordset
 Dim SQL As String
 A = Combo1.Text
 SQL = "SELECT SUM(totalTime) FROM TimeData WHERE name ='" &  A & "'"
   Set rst = db.OpenRecordset(sSQL)
   rst.movefirst
   Text1.Text = rst.Fields(0).Value
  rst.Close
  db.Close

 

[msay]

Golom,
Thank you so much! That worked. I was going nuts!!

MS