Passing variable to report record source

[jmsjazz]

Hi

I have a subroutine that successfully builds a SQL statement "strSQL", which is a public variable.
Using msgbox, I can read that the value is correct -
SELECT * from tblIncidents WHERE [Nature] = 'Hover';

(The select statement may be complex, e.g. [Nature] = 'hover' AND [COLOUR]= 'Blue' AND [GRADE] = 'High')

I want to pass the variable strSql to my report rptIncident in the following command:

Private Sub CmdPrintReport_Click()
If Right(strsql, 1) <> "'" Then 'check if statement was built
Else
strsql = strsql & ";" 'add trailing ; to statement
MsgBox strsql
DoCmd.OpenReport "tblincidents", acViewNormal, , strsql
End If
End Sub

I get a flashing error, then runtime error 3075 - |1 in query expression '|2'.

Can anyone explain, and suggest the best way of doing this?

 

[pbaldy]

You're passing it in the where condition argument, which expects an SQL "Where" clause without the word where. This type of thing:

http://www.baldyweb.com/wherecondition.htm

 

[jmsjazz]

Thanks for your help. I put the "'" after the string, but still get the error.

Using msgbox to check the string (which correctly populates a different form), I get the attached string before I trim it, using

strsql = right (strsql, len(strsql)-33) - this leaves me [NATURE] = 'hover'

strsql is public.

Do you spot anything else I may have done wrong?

Thanks again

 

[pbaldy]

Are you sure of the result after trimming? What error do you get now? Can you post the db here?

 

[Meehphill]

thank you everyone

 

[jmsjazz]

Thanks pbaldy, I posted this and didn't realize you had replied. I did get it working with your help.

 

[pbaldy]

Glad you got it sorted out.