ACCESS VBA Unmatched Records Query Not Working When Concatenating-CodePudding

I have been trying to run a query from MS ACCESS VBA. My query works well when I don't add concatenated fields. When I use a concatenated field like in the code below, it turns an empty result.

Is there any work around?

lstStudentName.RowSource = "SELECT [sdtName] & ' ' & [sdtFatherName] & ' ' & [sdtLastName] AS sdtFullName, sdtID FROM tbl_sdt_Info " & _
   " LEFT join tbl_sdt_Rounds ON tbl_sdt_Info.sdtID = tbl_sdt_Rounds.sdtID " & _
   " WHERE IS NULL(tbl_sdt_Rounds.sdtID)"

CodePudding user response：

Issues with your SQL:

Incorrect use of IS NULL - should be either IsNull(tbl_sdt_Rounds.sdtID) or tbl_sdt_Rounds.sdtID IS NULL. The latter is preferable because it is SQL, IsNull() is a VBA function.
Since there are two sdtID fields, query shouldn't work without table prefix to specify field. I am surprised you get anything.
Although possibly not an issue as is, my preference would be to make sdtID the first field and set ColumnWidths as 0";1.0" and first column as BoundColumn. This will allow viewing and typing first letter of name but sdtID will be listbox value.

Never hurts to build and test query object and when it works, replicate SQL statement in VBA.

lstStudentName.RowSource = "SELECT  tbl_sdt_Info.sdtID, sdtName & ' ' & sdtFatherName & ' ' & sdtLastName AS sdtFullName FROM tbl_sdt_Info " & _
   "LEFT join tbl_sdt_Rounds ON tbl_sdt_Info.sdtID = tbl_sdt_Rounds.sdtID " & _
   "WHERE tbl_sdt_Rounds.sdtID IS NULL;"