SQL statement

[loftty]

Hi All,

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

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

[szlamany]

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).

[loftty]

Hi,

I have changed the time to Time_Lesson

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

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

[brucevde]

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

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

[szlamany]

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

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!

[loftty]

Hi,

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

Thanks

Loftty

[szlamany]

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).

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