Pulling WHERE clause from a table

[Zaeed]

Hi, if I have a table field with a line of ID's like

323 OR 234 OR 12 OR 654 OR 1232

(memo field)

How would I incorporate that into a WHERE clause of a query?

I tried
WHERE [userID] = [groupIDs] but that just confused access and the query wouldn't open or display the datasheet

Cheers

 

[ajetrumpet]

could you use a function i wonder?:

SELECT * 

FROM table 

WHERE [field] = dlookup("field", "table")

if that field in the table has more than one rec in it too, just use a WHERE portion in the DL function and reference a public function in code


maybe that doesn't work. I don't think it would now that I think about it. but how about using a public function and utilizing SPLIT() to split out all the different values and concat it with the OR operator? it's possible...

 

[georgedwilkinson]

It sounds like your design is wrong. You need to capture each of the allowable user IDs in a separate row.

 

[Zaeed]

Yeah i'm kind of forced to do it this way george.. the list is for people to contact within a department...

Currently I have a query which just pulls people by their name, so the WHERE clause is "Tom Jones", "Frank Peters" etc
this is done in the query builder, using a custom expression joining the firstName and lastName fields together, so the actual WHERE clause is like this
'[firstName] & " " & [lastName]' = "Tom Jones" OR '[firstName] & " " & [lastName]' = "Frank Peters"

This is a really dodge way to do it, so i'm changing it to a list of ID's..

The problem is that the list of people isn't really based on any set criteria such as Position. I could just have the query the same as above but with a list of ID's, but I need to be able to have that list modified from the form. But the db is split, so that wouldn't work, which brings me to the tables..

My idea is to have 6 records, which hold the list of ID's that are to be contacted..
I just can't figure out how to bring that list over to my query.. (in query builder)

 

[georgedwilkinson]

If you do it in a table like I suggested, your where clause might look something like this:

where mycolumnorconcatenatedcolumns in (select thefieldthatholdsthecriteria from thetablethatholdsthatfield)

Otherwise, you could use the method you've been using with the "in" clause:

where [firstName] & " " & [lastName]' in ("Tom Jones", "Frank Peters")