SQL statement

Hi All,

What is wrong with this SQL statment, as VB does not like it

VB Code:

rst.Open "SELECT Controller.FirstName, Controller.Lastname, Controller.Time, Controller.Teachers_Name From Controller=WHERE (((Controller.Time)="17:00") AND ((Controller.Teachers_Name)="Lauren ellis"))", Adodc1.ConnectionString, adOpenKeyset, adLockOptimistic

Thnaks

Loftty

TIME is a reserved word - you should avoid field names with reserved words.

See the link in my signature for a list of other reserved words.

You can overcome the problem by wrapping the column name in []-brackets, but that doesn't always work (bugs in some tools make the field simply not accessible).

Hi,

I have changed the time to Time_Lesson

but I get an error saying expected end of statement and points to 17

VB Code:

rst.Open "SELECT Controller.FirstName, Controller.Lastname, Controller.Time_Lesson, Controller.Teachers_Name From Controller WHERE (((Controller.Time_Lesson)="17:30")" AND ((Controller.Teachers_Name)="ben riches"))", Adodc1.ConnectionString, adOpenKeyset, adLockOptimistic

What am I doing wrong here?

Thanks

Loftty

Use single quotes to delimit the values within the string and double quotes to delimit the entire string

VB Code:

WHERE (((Controller.Time_Lesson)="17:30")" AND ((Controller.Teachers_Name)="ben riches"))", adodc
 
should be
 
WHERE (((Controller.Time_Lesson)='17:30') AND ((Controller.Teachers_Name)='ben riches'))", adodc

Quote:

---

Originally Posted by brucevde

Use single quotes to delimit the values within the string and double quotes to delimit the entire string

VB Code:

WHERE (((Controller.Time_Lesson)="17:30")" AND ((Controller.Teachers_Name)="ben riches"))", adodc
 
should be
 
WHERE (((Controller.Time_Lesson)='17:30') AND ((Controller.Teachers_Name)='ben riches'))", adodc

---

I didn't even scroll that far over!

Hi,

Done what you said by using singe quotes, thankyou, how can I see the output that I get?

Thanks

Loftty

The RST object has property and methods - look up help file on RECORDSET object...

Here a really simply snippet - there is more to this (moving to next record - stuff like that).

Code:

---

    rs.Open "SELECT dbo.GetConf('" & ConfItem _
        & "') as ConfData", gCn
    GetConf = rs!ConfData & ""
   
    rs.Close
    Set rs = Nothing

---