WHERE Field IN([TextBox]) 2

[PWD]

Good afternoon. I hope this is possible. I can get my Query (Access 2007) to run with either of the following code:-

WHERE (((Amalgamated_Data.[Week number])=[Forms]![LbL_Data]![WeekNo]))

or

WHERE (((Amalgamated_Data.[Week number]) In (20,22)));

What I'd like to be able to do is let my user put "20,22" in the 'WeekNo' text box and run code like:-

WHERE (((Amalgamated_Data.[Week number]) In ([Forms]![LbL_Data]![WeekNo])));

I get no errors, but no data either.

Is this just a simple sytax thing?

Many thanks,

Des.

 

[randy700]

Open the Immediate Window, put [blue]Debug.Print[/blue] in the next line after building your WHERE clause and see what you have.


Randy

 

[PWD]

Hi Randy. This is the SQL behind my Query - in the 'Criteria' cell, so I'm not sure I can do that.

It's really frustrating because if I add to the SELECT, to show the value of the text box ,i.e.

, [Forms]![LbL_Data]![WeekNo] AS [Date Selected]

it shows it as "20,22"

Des.

 

[PHV]

WHERE InStr(',' & [Forms]![LbL_Data]![WeekNo] & ',',Amalgamated_Data.[Week number])>0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

How about:

WHERE Amalgamated_Data.[Week number] In [blue]([/blue][Forms]![LbL_Data]![WeekNo][blue])[/blue];

but you have to play around with [tt][][/tt] and [tt]()[/tt]

Have fun.

---- Andy

 

[Gammachaser]

Try this:

WHERE Amalgamated_Data.[Week number] In (" & [Forms]![LbL_Data]![WeekNo] &");"

Your method is looking for the week number in the string "[Forms]![LbL_Data]![Weekno]"... it does not exist there, but it didn't cause any errors to look. What you want is to look for the data in the string that is the contents of [Forms]![LbL_Data]![WeekNo]. You should get what you want from the above code... I think.

 

[LarrySteele]

Des,

The problem is how JET is building the query...

From your example....

WHERE (((Amalgamated_Data.[Week number]) In ([Forms]![LbL_Data]![WeekNo])));

If you have [tt]20,22[/tt] in your form text box, this is what JET will see:

WHERE (((Amalgamated_Data.[Week number]) In ([red]"20,22"[/red])));

So, the query is going to return all records "Week number" equals "20,22". It's syntactically correct, which is why you're not getting errors, but the logic error is causing no records to return.

What you really want is:

WHERE (((Amalgamated_Data.[Week number]) In ([blue]20,22[/blue])));

or

WHERE (((Amalgamated_Data.[Week number]) In ([blue]"20","22"[/blue])));

Depending if your field "Week number" is a string or number.

If memory serves, the last time I did this, I had VBA regenerate the query after user entered their criteria.

Of course, if others know a way to turn your string into a comma-delimited list that JET assembles correctly. I started looking at the split/join functions, but nothing stood out as workable for this situation.

Hope this helps at least explain why the original method didn't work.

- Larry

 

[PHV]

Another way, without external function:
WHERE (',' & [Forms]![LbL_Data]![WeekNo] & ',') Like '*,' & Amalgamated_Data.[Week number] & ',*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWD]

Thanks for the explanation Larry; that makes sense. I'd guessed something was going on.

Sorry, Gammachaser that just plain didn't work.

PHV. Where did you dig that out from????

Here is my final code - obviously I've pinched the 'NotNull' bit from somewhere else!!

WHERE (',' & Forms!LbL_Data!WeekNo & ',') Like '*,' & Amalgamated_Data.[Week number] & ',*' Or (((IsNull(Forms!LbL_Data!WeekNo))<>False));

Man, that is SOOOO B E Utiful!!!

Works just fine with this multiple too-

20,23,24,17

Job done, many thanks.

Des.