SQL Code

[sam1fletcher]

Hi i keep getting runtime error 3075 missing opperator

Code:

Set rs = db.OpenRecordset("tblSchool")
With rs
.MoveFirst
While Not .EOF

SchName = rs("SchoolName").Value
sql1 = "SELECT * FROM tblBooking WHERE School Name =" & SchName

the error is on the last line and im guessing its because of the space between School and Name but that is what it is called in the table.

What is the correct way to write this line??

Sam

 

[jdraw]

Use a naming convention that doesn't allow embedded spaces or special characters. Use only alphanumerics to avoid syntax issues.

Tell readers WHAT you are trying to do in plain English so they have some context to understand your issue.

Show all of the code involved.

In this case, what is db? Where is it defined?

 

[sam1fletcher]

Dim rs As DAO.Recordset
Dim rsb As DAO.Recordset
Dim rsb1 As DAO.Recordset
Dim db As DAO.Database
Dim Cnt As Integer
Cnt = 0
Dim SchName As String
Set db = CurrentDb
Dim sql As String
sql = "SELECT * FROM tblSchool"



Set rs = db.OpenRecordset("tblSchool")
With rs
.MoveFirst
While Not .EOF

SchName = rs("SchoolName").Value
sql1 = "SELECT * FROM tblBooking WHERE School Name =" & SchName
sql2 = "SELECT * FROM tblSubBooking WHERE School Name =" & SchName

Set rsb = db.OpenRecordset(sql1)
With rsb
.MoveFirst
While Not .EOF
Cnt = Cnt + 1
.MoveNext
Wend
End With

Set rsb1 = db.OpenRecordset(sql2)
With rsb1
.MoveFirst
While Not .EOF
Cnt = Cnt + 1
.MoveNext
Wend
End With

.Edit
!BookingNum = Cnt
.Update
Cnt = 0
.MoveNext

Wend
End With
rs.Close

 

[sam1fletcher]

it basically counts up all the bookings and subBookings for each school. I cannot change the name of the section in the Table as its used in much more things.

I could program it so it looks at every booking but thats is bad programming and Usage so i want to use an SQL statement to seperate them out

 

[namliam]

really consider addopting that naming convention already mentioned by Jdraw, using spaces in names is generaly a bad idea

Also splashing code on any forum like that is a bad idea, please use the code tags (the # button on the post menu) and if you are not doing that please indent your code so that it remains readable.

That being said, any columns that do have spaces in them need to be wrapped in [your column name inserted]

 

[JHB]

You are missing [] around the field name. And if the criteria is text you need to surround it with '

sql1 = "SELECT * FROM tblBooking WHERE School Name =" & SchName
sql2 = "SELECT * FROM tblSubBooking WHERE School Name =" & SchName

"SELECT * FROM tblBooking WHERE [COLOR=Red][B][[/B][/COLOR]School Name[B][COLOR=Red]][/COLOR][/B] =[B][COLOR=Red]'[/COLOR][/B]" & SchName [COLOR=Red][B]& "'"[/B][/COLOR]