Variable not recognised in SQL

[Kayleigh]

Hi,
I have code to display query result as a drop-down of field but the variable Wkdy keeps popping up as error. Have I coded this wrong?

Private Sub fldSessionLogID_Enter()
Dim strSql As String
Dim Wkdy As Integer

Wkdy = Weekday(Me.fldIDate)

strSql = "SELECT DISTINCT tblSession.fldSessionID, tblSubject.fldSubjectName, qrylkpStaffNameShort.StaffShortName " & vbCrLf & _
"FROM qrylkpStaffNameShort RIGHT JOIN (tblWeekdays RIGHT JOIN (tblTerm INNER JOIN (tblSubject RIGHT JOIN ((tblRooms RIGHT JOIN (tblSession LEFT JOIN qrylkpClassName ON tblSession.fldClassID = qrylkpClassName.fldClassID) ON tblRooms.fldRoomID = tblSession.fldRoomID) LEFT JOIN jtblSessionWeekday ON tblSession.fldSessionID = jtblSessionWeekday.fldSessionID) ON tblSubject.fldSubjectID = tblSession.fldSubjectID) ON tblTerm.fldTermID = tblSession.fldTermID) ON tblWeekdays.fldWeekdayID = jtblSessionWeekday.fldWeekdayID) ON qrylkpStaffNameShort.fldStaffID = tblSession.fldStaffID " & vbCrLf & _
"WHERE (((tblTerm.fldStart)<[Forms]![frmIncidentLogList]![txtTo]) AND ((tblTerm.fldEnd)>[Forms]![frmIncidentLogList]![txtFrom]) AND ((tblWeekdays.fldWeekdayID)= Wkdy )) " & vbCrLf & _
"ORDER BY tblSubject.fldSubjectName;"

Me.fldSessionLogID.RowSource = strSql
Me.fldSessionLogID.Dropdown

End Sub

 

[Isaac]

You can't refer to variables directly in Access SQL, like you can in T-SQL. You have to build a dynamic sql string by concatenating your static literal sql with the variable's literal value.

For example, instead of:

dim var
var="something"
strSQL = "select * from table where field=var"

You'd need something more like:

dim var
var="something"
strSQL = "select * from table where field=" & var

 

[arnelgp]

also, but not related, you don't need to add vbCrLf on the SQL string.

 

[Galaxiom]

also, but not related, you don't need to add vbCrLf on the SQL string.

That is included to include line breaks if you want to view the output.
The second line would need a lot more of them to really work though.