No response

  • Thread starter Thread starter Nike
  • Start date Start date
N

Nike

I posted earlier, but got no response. I think I am close
but I keep getting a syntex error. Can someone shed some
light on where my error is? It keeps telling me I am
missing an operator.

Thanks

Set rst = dbs.OpenRecordset("SELECT DISTINCTROW " & _
"tblScheduleClass.ScheduleClassID " & _
"FROM tblScheduleClass" & _
" WHERE " & "[Class] Like " &
Me.REASONLIST.Column(1) & "[Date] = " & Date & ";")
Set rst2 = CurrentDb.OpenRecordset
("tblStudentsAndClasses")
rst2.AddNew
rst2![ClassID] = rst4!
[tblScheduleClass.ScheduleClassID]
rst2![CustomerID] = Me.CustomerID
rst2.Update
rst2.Close
 
When you use "Like" as an operator, you must delimit the value being used as
the "target" with ' characters; you must have a space and the word AND
before the [Date]; and you must delimit a date value with # characters:

" WHERE " & "[Class] Like '" &
Me.REASONLIST.Column(1) & "' And [Date] = #" & Date & "#;")


Try the above in place of your WHERE statement.
 
I would recommend constructing the SQL String in a separate statement. This
way, you can use Debug.Print to see what is actually passed to JET Engine.
Something like:

Dim strSQL As String

strSQL = SELECT DISTINCTROW tblScheduleClass.ScheduleClassID " & _
" FROM tblScheduleClass " & _
" WHERE ([Class] Like " & Chr$(34) & _
Me.REASONLIST.Column(1) & Chr$(34) & _
") And ([Date] = Date())
Debug.Print strSQL

Set rst = dbs.OpenRecordset(")
....

Note that there were 2 errors in your SQL String:

* The argument after LIKE is a String and must be enclosed in quotes.
* Boolean operator ("And" ???) between the 2 criteria.

BTW, "Date" is a bad Field name. If possible, change the Field name to
something else.
 
Ok I am still not able to get this to work even after the
2 suggestions. What I am trying to do is get the
ScheduleClassID from tblScheduleClass where the name of
the Class matches the name in the listbox ReasonList.Column
(1) and also where the Date is today.

Once I get this ScheduleClassID then create a record in
tblStudentsAndClasses where ClassID = ScheduleClassID and
CustomerID = CustomerID from the form.

How do I accomplish this I thought I was on the right
track?

Any help would be appreciated.
 
Back
Top