By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,556 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Problem with "WHERE-IN" statement in SQL

P: 40
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?
Sep 3 '07 #1
Share this Question
Share on Google+
5 Replies


FishVal
Expert 2.5K+
P: 2,653
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.
Sep 3 '07 #2

Expert 100+
P: 126
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.
Sep 3 '07 #3

P: 40
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...
Sep 3 '07 #4

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport WhereCondition:="tMonth IN (" & Forms!frmMain!txtMonths & ")"
  2.  
Sep 3 '07 #5

JConsulting
Expert 100+
P: 603
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.
Sep 3 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.