Compile Error: Expected list Seperator or )

[cranebill]

In the following line i get the error "Compile Error: Expected list Seperator or )" and it highlights the word schedule.

Set rs = Db.OpenRecordset("SELECT ToDo.ProjectID, ToDo.ToDo, ToDo.DueDate FROM ToDo WHERE (((ToDo.ToDo)="Schedule Field Check Due&quot)&quot

Any help would be appreciated

Bill

 

[chrissie1]

Set rs = Db.OpenRecordset("SELECT ToDo.ProjectID, ToDo.ToDo, ToDo.DueDate FROM ToDo WHERE (((ToDo.ToDo)=" & [Schedule Field Check Due] & &quot)&quot
Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[cranebill]

One question, maybe i should have elaborated a bit more, but by putting the phrase in brackets wont that have access believe that it is a field, when in fact it is a text string?

Bill

 

[chrissie1]

Set rs = Db.OpenRecordset("SELECT ToDo.ProjectID, ToDo.ToDo, ToDo.DueDate FROM ToDo WHERE (((ToDo.ToDo)= 'Schedule Field Check Due'))&quot

sorry try this
Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[cranebill]

Thank you that worked.... i think. Here is a piece of code i wrote to delete a record from the recordset you helped me with. It does not delete the record for some reason.

Set Db = CurrentDb()

Set rs = Db.OpenRecordset("SELECT ToDo.ProjectID, ToDo.ToDo, ToDo.DueDate FROM ToDo WHERE (((ToDo.ToDo)= 'Schedule Field Check Due'))&quot


Select Case ToDo
Case "Schedule Field Check Due"
rs.Delete
rs.Close
Set rs = Nothing
Set Db = Nothing


Case Else
rs.Close
Set rs = Nothing
Set Db = Nothing

End Select

Am i missing something here? I looked in the table and there is a record that does exist with that text in it therefore to my understanding the recordset created should pull that up, correct?

Bill

 

[tdfreeman]

I am not sure from the above if that is all your code, but if it is you need to scroll through the recordset and select each record checking for the value, then delete the record. From what I can tell, you never access the recordset.

BTW, aren't you deleting the whole recordset, not just a specific record? When you created the recordset you created it with only records that meet the criteria you are deleting.

My suggestion is as follows, note my reference to the ToDo field - rs!ToDo (Of course, if you only have one record with that value you may want to put in a flag that says when you have found and deleted the correct value to exit the procedure):


Set Db = CurrentDb()

Set rs = Db.OpenRecordset("SELECT ToDo.ProjectID, ToDo.ToDo, ToDo.DueDate FROM ToDo WHERE (((ToDo.ToDo)= 'Schedule Field Check Due'))&quot

rs.MoveFirst
Do Until rs.EOF
If rs!ToDo = "Schedule Field Check Due" Then
rs.Delete
End Select
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set Db = Nothing
Thank you for your help.

Tammy

 

[chrissie1]

why make it so difficult when it can be done in one line

docmd.runsql "DELETE ToDo WHERE (((ToDo.ToDo)= 'Schedule Field Check Due'))"
Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[cranebill]

Thank you guys for your help, but i chose Tammy's way due to the fact that in case it had multiple entries it deleted them all so i chose the recordset to be derived from the project number instead of the criteria of ToDo. There were however 2 errors. The first was the End Select Command. It gave me the error that we used that without a case statement... so i removed it... no big deal. The Second Error however i dont understand. Here is the Code:

Set Db = CurrentDb()

Set rs = Db.OpenRecordset("SELECT ToDo.ProjectID, ToDo.ToDo, ToDo.DueDate FROM ToDo WHERE (((ToDo.ProjectID)= Me.ProjectID))&quot

rs.MoveFirst
Do Until rs.EOF
If rs!ToDo = "Schedule Field Check Due" Then
rs.Delete

rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set Db = Nothing

It highlights the Loop command saying there is a loop with no Do.... looks to me there is a Do in the line right after rs.MoveFirst. Any Ideas?

Bill

 

[JimInKS]

cranebill,

One good way to create and learn SQL is to create your query with the MS Access Query designer. Then, switch the query to SQL view, and you will see the SQL MS Access created to implement your query.