CODEWITHSUNDEEP
ms-accessvba
greenn
greenn

Reputation: 309

"this expression is typed incorrectly or it is too complex to be evaluated" error

I am trying to use an SQL statement for a recordsource of a report using VBA. I know that the SQL works because I copied the SQL from a SQL view of a query which was working.

Here it the SQL recordsource that fires on open event of a report. I made one change to the SQL statement which was adding a variable strInput which you can see in the WHERE clause.

Private Sub Report_Open(Cancel As Integer)
Dim sql As String
Dim strInput As String
strInput = Me.OpenArgs

sql = "SELECT DISTINCTROW INV.NUM, INV.SUB, [INVD]![QTY]*([INVD]![VDR]+[INVD]![MATQ]) AS Expr1, [INVD]![QTY]*[INVD]![LPR] AS Expr2, [Expr1]+[Expr2] AS Expr3, [INVD]![MATQ]+[INVD]![VDR] AS Expr4, ([INVD]![MATQ]+[INVD]![VDR])*[INVD]![QTY]*0.8 AS Expr5" & _
    " FROM (JOBS INNER JOIN UN ON JOBS.UNID = UN.JOBS) INNER JOIN ((CONT INNER JOIN INV ON CONT.CTNO = INV.CTNO) INNER JOIN INVD ON (INV.CTNO = INVD.CTNO) AND (INV.INV = INVD.INV)) ON (UN.UNID = INVD.UNID) AND (UN.CTNO = INVD.CTNO)" & _
    " WHERE (((INV.JOB) = [Forms]![02- UNIT JOB INVOICE ENTRY]![JOB])) AND InvNum = ' " & strInput & "'" & _
    " ORDER BY INV.CTNO, INV.JOB, INV.INV, INVD.ITEM;"

If Me.OpenArgs <> "" Then
    Me.RecordSource = sql
    End If



End Sub

The error that I get is

this expression is typed incorrectly or it is too complex to be evaluated

I know that there are similar threads open but those solutions did not work for me. Thank you.

Edit 1 :

Based on your reply I deleted the on open procedure and tried utilizing the where clause like you said

MsgBox strInput
DoCmd.OpenReport "PRINT REPORT", acPreview, , "INVD.InvNum =' " & strInput & "'"

This is now giving me the same error

this expression is typed incorrectly or it is too complex to be evaluated

Upvotes: 0

Views: 848

Answers (1)

greenn
greenn

Reputation: 309

The solution was to remove the string quotation from the WHERE clause because InvNum was a number. The final Where clause looks like this

WHERE (((INV.JOB)=[Forms]![02- UNIT JOB INVOICE ENTRY]![JOB])) AND INVD.[InvNum] = " & strInput

Upvotes: 0

Related Questions