(Error 3071) Access

0
votes

I'm trying to search for the Course ID by checking the the faculty ID and Course ID in my Course table. Its a simple query but when launched I receive a

(This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

The VB script I'm using looks like this.

Private Sub Course_ID_DblClick(Cancel As Integer)

Dim x As Integer

Dim y As String

Dim z As Integer

x = Me.Faculty_ID.Value

Me.Course_ID.RowSource = "SELECT Course.[Course ID] FROM Course WHERE Course.[Course Name]=['Course']AND Course.[Faculty ID]='" & x & "'"

End Sub
2
ms-accessvba

2 Answers

0
votes

I think you are getting this message because you are using wrong delimiters and confusing terminology in object names (where field content 'course' and table name [course] are the same.

I guess your datasource is an ms-access database. You should then use the " (double quote) as value separator (you'll have to double the sign inside the expression) for text, and nothing for numbers. your select instruction could then look like:

"SELECT Course.[Course ID] FROM Course WHERE Course.[CourseName]=[""Course""] AND Course[FacultyID]=" & x

Add a 'debug.print Me.Course_ID.RowSource' to check the final string that should look like:

SELECT Course.[Course ID] FROM Course WHERE Course.[CourseName]=["Course"] AND Course[FacultyID]= 3
0
votes

There should be no brackets around your string literals:

Me.Course_ID.RowSource = "SELECT Course.[Course ID] FROM Course WHERE Course.[Course Name]='xyz' AND ...

Also, you can use either single or double quotes as string delimiters in an access query.