Using a form for custom criteria (list)

[mab9]

I have an odd problem that I can't figure out a decent way to do it. I have a simple table:

Table1
Item Price
1 $5
2 $10
3 $15
4 $20

I'd like to have a form where the users could paste a list of the items they want to query off of, then click a button and the item criteria is then based on that custom list. I've been playing around with using the replace function but that doesn't seem to help any. I'm trying to avoid appending and deleting constantly from temporary tables as there will be multiple in the same form as one time.

Any ideas?

 

[pbaldy]

What do you mean by "paste a list"? Like copy/paste from some other place? What would the result look like?

 

[khawar]

Check out the sample is it work for you

Select the items you want to include in the query from the list(its a multi select listbox)

Module is copied from a example from Microsoft's Site


Khawar

 

[mab9]

What do you mean by "paste a list"? Like copy/paste from some other place? What would the result look like?

You are correct. I think a lot of the people would be copy/pasting in a list of items from Excel. Based on that pasted data, I'd like that to become the criteria in a query, ie:

From Excel paste an item list into a form somehow:
1
2
3

The query criteria for item becomes: 1 or 3 or 4


With regards to that sample from the MS site, that would work up to a point. The users would probably be pasting in lists of 100+ items.

 

[pbaldy]

It might be doable. A very brief test shows that items copied from a column in Excel appear to paste into Access with carriage returns between them. You might be able to use the Split function to parse them out and build your string.

 

[mab9]

I was playing around with using the Replace function for chr(10) & chr(13) which does get it into the: 1 or 2 or 3 or 4 format, however I think the query is taking that whole string as the criteria instead of the four individual items.

What is the split function? I don't see it as part of the built-in ones.

 

[pbaldy]

You should be able to find it in VBA Help. Keep in mind that this format wouldn't work:

1 or 2 or 3 or 4

as the actual SQL would have to be:

FieldName = 1 or FieldName = 2 or FieldName = 3 or FieldName = 4

I would build an IN clause:

IN(1,2,3,4)