Problem with "WHERE-IN" statement in SQL

[saddist]

Hello,

I have an access report with WHERE clause: "WHERE tMonth in (Forms!frmMain!txtMonths)"

when txtMonths = "7" (f.e.) it works just fine, but when I try to execute the code when txtMonths = "7,8" it displays empty report

Any idea what can be wrong?

[FishVal]

Hello,

I have an access report with WHERE clause: "WHERE tMonth in (Forms!frmMain!txtMonths)"

when txtMonths = "7" (f.e.) it works just fine, but when I try to execute the code when txtMonths = "7,8" it displays empty report

Any idea what can be wrong?

Hi, there.

The idea is that "7,8" is being treated as single value, not as list of values.
Try to generate SQL statement in VBA using string concatenation and then either put it to Report.RowSource or QueryDef.SQL of the query the report based on.

[Stwange]

Hello,

I have an access report with WHERE clause: "WHERE tMonth in (Forms!frmMain!txtMonths)"

when txtMonths = "7" (f.e.) it works just fine, but when I try to execute the code when txtMonths = "7,8" it displays empty report

Any idea what can be wrong?

You could try this instead, it should work I think:

WHERE (Forms!frmMain!txtMonths) LIKE '*tMonth*'

If it doesn't work (I'm not sure if * matches an empty string or not), you might need two ORs, ie. *value*, *value and value*, but I doubt it.

Hope this helps.

[saddist]

The problem is that SQL statement is too long and it places 9 at the end of string and stops adding :/
Besides I can't change recordsource of report. When I try to do that I either get message "can't change row source becouse report is not open" or "u can't change row source when report is open or printing".
I can only change recordsource while in design view, and I dont want user to be is design view...

[FishVal]

The problem is that SQL statement is too long and it places 9 at the end of string and stops adding :/
Besides I can't change recordsource of report. When I try to do that I either get message "can't change row source becouse report is not open" or "u can't change row source when report is open or printing".
I can only change recordsource while in design view, and I dont want user to be is design view...

Open the report with

DoCmd.OpenReport WhereCondition:="tMonth IN (" & Forms!frmMain!txtMonths & ")"
 

[JConsulting]

Hello,

I have an access report with WHERE clause: "WHERE tMonth in (Forms!frmMain!txtMonths)"

when txtMonths = "7" (f.e.) it works just fine, but when I try to execute the code when txtMonths = "7,8" it displays empty report

Any idea what can be wrong?

It's been my experience that you can't pass a string to an IN() clause. You whould create an OR statement instead.