run-time error 3075

[keelerm]

Hi all the error I am receiving in Access 2007 is as above with
Syntax error (missing operator) in query expression
'CIGlobal_CONTRACT.CIGlobal_aID =`.

When I debug it has this
rivate Sub cmdPrint_Click()
Dim rst As Recordset

If IsNull(Me!lstCIGlobals) Or Me!lstCIGlobals = "" Then
MsgBox " No document selected.", vbExclamation + vbOKOnly, "Select a document"
Exit Sub
End If

strSQL = "SELECT CI.* FROM CIGlobal_CONTRACT INNER JOIN CI ON (CIGlobal_CONTRACT.CNT_NUM = CI.CNT_NUM) AND (CIGlobal_CONTRACT.CIGlobal_aID = CI.CIGlobal_aID) where CIGlobal_CONTRACT.CIGlobal_aID = " & Me!lstCIGlobals.Column(7)
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

If rst.EOF Then
Exit Sub
Else
rst.MoveFirst
Do Until rst.EOF
DoCmd.OpenReport "rptCI", acViewPreview, , "CNT_NUM = '" & rst!CNT_NUM & "' AND CI_NUM = '" & rst!CI_NUM & "'"
Do While IsReportLoaded("rptCI")
DoEvents
Loop
rst.MoveNext
Loop
End If

[Stewart Ross]

Hi. When you next try to run this use the VBA Editor immediate window to check the value of Me!lstCIGlobals.Column(7). This is the value you are inserting into your WHERE clause, and the error message you quote is telling you there is no value being returned for that column.

I am assuming that you are expecting this value to be numeric; if it is a text value you will need to use single quotes on either side of the point at which you insert the value, like this:

" ... where CIGlobal_CONTRACT.CIGlobal_aID = '" & Me!lstCIGlobals.Column(7) & "'"

Columns are numbered from 0, so column(7) refers to the 8th column within the listbox concerned.

-Stewart