list of numbers stored as text in a table cell as the criteria for a query?

[Z. Tetics]

I'm trying to insert a list of values from a table into the where
clause of a query. I have a several sets of values stored as text that
I'd like to use as criteria in a query on a different table, for
example,

TableA:
ID CoumnA
1 | "3", "5","17", "34"
2 | "1", "12","15"

Where clauses:

WHERE TableB.ColumnB IN( "3", "5","17", "34")
or,
WHERE TableB.ColumnB IN( "1", "12","15")



Is there an easy way to get those values from a single cell in a table
and into the query? I've tried a few things, DLookUp, for example, but
get no values returned.
I think there should be a quick way of doing this without resorting to
VBA, but I can't think what it is.

 

[Amy Blankenship]

I'm trying to insert a list of values from a table into the where
clause of a query. I have a several sets of values stored as text that
I'd like to use as criteria in a query on a different table, for
example,

TableA:
ID CoumnA
1 | "3", "5","17", "34"
2 | "1", "12","15"

Where clauses:

WHERE TableB.ColumnB IN( "3", "5","17", "34")
or,
WHERE TableB.ColumnB IN( "1", "12","15")



Is there an easy way to get those values from a single cell in a table
and into the query? I've tried a few things, DLookUp, for example, but
get no values returned.
I think there should be a quick way of doing this without resorting to
VBA, but I can't think what it is.

If your table structure were normalized, you'd have your values in rows
rather than columns, so it would look like this:

ID NumValue
1 3
1 5
1 17
1 34
2 1
2 12
2 15

So you could use

WHERE TableB.ColumnB IN (SELECT NumValue FROM TableA WHERE ID=1 OR ID=2)

HTH;

Amy

 

[Z. Tetics]

True. However, I'm trying to add a step in the middle of a series of
queries and calculations and I'm trying to avoid large changes to
existing objects.

If I could just find a way to get the contents of one cell and plug it
into the where clause, it would save me a great deal of time.

I realise that the design is far from ideal, but I think there should
be a way to make it work.

 

[John Spencer]

If you can't fix the data, then you are going to have to rely on VBA
functions and that is probably going to be S L O W.

Perhaps, you can use

WHERE Instr(1,DLookup("ColumnA","YourTable","ID = 1"), & Chr(34) &
TableB.ColumnB & Chr(34)) > 0

Your best bet would be to fix the data or to build the SQL statement with
VBA. If you use VBA, you would end up with something like the following
that you could save as a query. Alternatively, you might be able to apply
the where clause as an argument when you are opening a report.


strSQL = "SELECT ... FROM ... WHERE TableB.ColumnB In (" &
DLookup("ColumnA","YourTable","ID = 1") & ")"

 

[Jeff Boyce]

It may come down to a "pay now or pay later" situation. If you want to be
able to use the features and functions that Access offers, you need to feed
it properly normalized data. So, you may "want", and feel Access "should",
but Access offers excellent tools to use when your data is organized in the
ways it expects to see.

Have you considered exporting the data to Excel or some other tool and
trying to do this?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

If I could just find a way to get the contents of one cell and plug it
into the where clause, it would save me a great deal of time.

I realise that the design is far from ideal, but I think there should
be a way to make it work.

The only way I can think of is to construct the SQL from scratch in
VBA code.


John W. Vinson[MVP]

 

[Amy Blankenship]

You might be able to rig some sort of query that normalizes the data based
on the count of records with a given ID and the Split function, but as
others have commented, you're better off just sucking it up and doing it
right.

HTH;

Amy

 

[Z. Tetics]

Ok then, I accept that this is not as simple to do as I expected.
I'll just use VBA to build the SQL then.

Thanks all.

And, yes, I'm trying to retrofit something into an already complicated
database and
I am well aware that I am not asking for the ideal way to handle this
kind of data. I won't get into all of my reasons for attempting this
approach but, given the various constraints of the situation, this
would have been, by far, the fastest solution.