Help on a query

[Herkatus]

Hello.

I have this code:

SELECT Table1.Field1, Table1.Field2
FROM Table1
WHERE (((Table1.Field1) In ([Forms]![Form1]![TextBox])));

Inside the textbox is a VBA generated code like this

S1502,S1510,S1000

If I only put one string (like S1502) it gives me the result but with 2 or more it just don't work... Any idea?

 

[Uncle Gizmo]

Are you saying you want to find the 3 separate codes?

 

[Herkatus]

I want to do multiple filters on the same field

 

[Uncle Gizmo]

Sorry, no sure I understand ....

 

[Herkatus]

What I want to do is this:

SELECT Table1.Field1, Table1.Field2
FROM Table1
WHERE (((Table1.Field1) In (S1502,S1510,S1000)));

But instead of putting in the specific criteria (S1502,S1510,S1000), I want the query to "fetch" the criteria in a textbox inside a form ([Forms]![Form1]![TextBox])

But isn't working...

 

[Uncle Gizmo]

Are you expecting the query to return result(s) where the query finds a match to this Exact string: S1502,S1510,S1000

Or are you expecting the query to return matches to each individual, separated value?

A match for: S1502 and S1510 and S1000

 

[Herkatus]

A match for: S1502 and S1510 and S1000

That's exactly what I want...

 

[Uncle Gizmo]

Read and Answer ALL these Questions:

Is there ALWAYS 3 Items?

Are they ALWAYS the same Length? (5 Characters)

Is the Delimiter ALWAYS there? (That's the ",")

Is it ALWAYS the Same Delimiter?

Where do the Items Come From?

 

[Herkatus]

Is there ALWAYS 3 Items?

No. It can be one or more item...

Are they ALWAYS the same Length? (5 Characters)

Normally its 7 characters but I don't want to limit it

Is the Delimiter ALWAYS there? (That's the ",")
Is it ALWAYS the Same Delimiter?

Where do the Items Come From?

I have a form with 4 objects: a ListBox, a TextBox and 2 buttons. The purpose of this form is to retrieve multi-selection items from the ListBox in the TextBox seperated by a comma, in order to use a query with the code I'm having problems with.
The form has a VBA code which inserts the comma.
http://support.microsoft.com/Default.aspx?scid=kb;en-us;827423&x=13&y=10

 

[Herkatus]

Anyone?!? Please!!!!

 

[wilpeter]

It sounds as though you've gone to a lot of trouble to combine into a string a series of chosen values. Now you want to use the result in a query that has to uncombine them to replace the comma with " or " so that your Criteria row would replace S1005,S15123,S152345 with S1005 or S15123 or S152345
Is that what you intend?

 

[Herkatus]

It sounds as though you've gone to a lot of trouble to combine into a string a series of chosen values. Now you want to use the result in a query that has to uncombine them to replace the comma with " or " so that your Criteria row would replace S1005,S15123,S152345 with S1005 or S15123 or S152345
Is that what you intend?

yes, that resumes it all!

 

[namliam]

What I want to do is this:


But instead of putting in the specific criteria (S1502,S1510,S1000), I want the query to "fetch" the criteria in a textbox inside a form ([Forms]![Form1]![TextBox])

But isn't working...

This will result in a search for "S1502,S1510,S1000" which obviously wont work.

Instead you need
1) Quotes in the text field
"S1502","S1510","S1000"
2) SQL writen via code
Sql = "Select ... bla bla ... from ... where Yourfield in (" & Me.YourFOrmField & ");"
3) Write sql to the query
Using querydefs, look at the access help
4) Run the query as per normal

 

[wilpeter]

Could you not skip the combining into string step in your form and have each choice from the List Box appear in a separate text box as "Choice1", "Choice2", etc.? Then your Criteria would be [Forms].[Form1].[Choice1] or ... etc. with an Is Not Null requirement.

 

[wilpeter]

Sorry, Mailman, your response interceded my slower typing.

 

[Herkatus]

This will result in a search for "S1502,S1510,S1000" which obviously wont work.

Instead you need
1) Quotes in the text field
"S1502","S1510","S1000"

But I have to change vba code to achieve
"S1502","S1510","S1000"?

2) SQL writen via code
Sql = "Select ... bla bla ... from ... where Yourfield in (" & Me.YourFOrmField & ");"

Can you give me a full example of the code?

 

[namliam]

Can you give me a full example of the code?

I did didnt I? I mean it is your query theonly thing you do is replace the last part.

Then lookup the access help.... and yes you have to change the VBA...

 

[Herkatus]

I've found another way to achieve this by, instead of converting the filters to a string, I've used a temp table to put the different. It was much more easy.

Thank you all for your help.

 

[namliam]

temp table... *ugh*

 

[Brianwarnock]

If ever a thread lost its way this one did.
The first post said

What I want to do is this:

Quote:
SELECT Table1.Field1, Table1.Field2
FROM Table1
WHERE (((Table1.Field1) In (S1502,S1510,S1000)));

But instead of putting in the specific criteria (S1502,S1510,S1000), I want the query to "fetch" the criteria in a textbox inside a form ([Forms]![Form1]![TextBox])

But isn't working...

Thus the guy wanted to use the IN function, he wanted ORs not ANDs , but as we know you cannot achieve the IN function with parameters passed via a Form.

Several years ago Pat Hartman gave a work around but I've never been able to find it again. Pity as it keeps coming up.

Brian