Using a form for custom criteria (list) (1 Viewer)

M

mab9

Registered User.

Local time

Today, 08:27

Joined

Oct 25, 2006

Messages

63

• Jun 19, 2008

• #1

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

Wino Moderator

Staff member

Local time

Today, 06:27

Joined

Aug 30, 2003

Messages

36,124

• Jun 19, 2008

• #2

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

 

khawar

AWF VIP

Local time

Today, 17:27

Joined

Oct 28, 2006

Messages

870

• Jun 19, 2008

• #3

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

 

Attachments

• Sample.zip

  22.3 KB · Views: 120

M

mab9

Registered User.

Local time

Today, 08:27

Joined

Oct 25, 2006

Messages

63

• Jun 19, 2008

• #4

pbaldy said:

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

Click to expand...

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

Wino Moderator

Staff member

Local time

Today, 06:27

Joined

Aug 30, 2003

Messages

36,124

• Jun 19, 2008

• #5

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.

 

M

mab9

Registered User.

Local time

Today, 08:27

Joined

Oct 25, 2006

Messages

63

• Jun 19, 2008

• #6

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

Wino Moderator

Staff member

Local time

Today, 06:27

Joined

Aug 30, 2003

Messages

36,124

• Jun 19, 2008

• #7

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)