Value List

[Stephen Doyle]

I'm learning by doing, slowly. Is it possible to generate a query based on
data in a value list?

 

[Ken Snell \(MVP\)]

Depends upon what you mean by "value list". Show us some examples of what
you seek to do.

 

[Smartin]

I'm learning by doing, slowly. Is it possible to generate a query based on
data in a value list?

Not sure if this is what you mean or not...

Suppose you have a table called "Stores" that includes a "RegionID".
Maybe there are 50 regions, but you only are concerned about 5 of them.
Suppose you have a second table that enumerates the 5 important regions.
This is what I might call a "value list":

Regions.RegionID
================
8
11
14
62
100


Now you can write a query to get info about all the stores in just the 5
listed regions:

SELECT *
FROM Stores INNER JOIN Regions
ON Stores.RegionID = Regions.RegionID;

Or, another way:

SELECT *
FROM Stores
WHERE Stores.RegionID IN (SELECT Regions.RegionID FROM Regions);

Or, even more fun... info about stores /not/ in the value list:

SELECT *
FROM Stores
WHERE Stores.RegionID NOT IN (SELECT Regions.RegionID FROM Regions);

HTH

 

[Stephen Doyle]

Not sure if this is what you mean or not...

Suppose you have a table called "Stores" that includes a "RegionID".
Maybe there are 50 regions, but you only are concerned about 5 of them.
Suppose you have a second table that enumerates the 5 important regions.
This is what I might call a "value list":

Regions.RegionID
================
8
11
14
62
100


Now you can write a query to get info about all the stores in just the 5
listed regions:

SELECT *
FROM Stores INNER JOIN Regions
ON Stores.RegionID = Regions.RegionID;

Or, another way:

SELECT *
FROM Stores
WHERE Stores.RegionID IN (SELECT Regions.RegionID FROM Regions);

Or, even more fun... info about stores /not/ in the value list:

SELECT *
FROM Stores
WHERE Stores.RegionID NOT IN (SELECT Regions.RegionID FROM Regions);

HTH

Not quite.
I have a combo box, and the row source type is a value list and the row
source is a list of types. I need to create a query to generate a report
that includes the value row source data.

Cheers
Stephen

 

[John Nurick]

Hi Stephen,

In that case I'd change the row source type to Table/Query, put the list
of types into a little table and use that in the query.

The alternative is to write VBA code that gets the RowSource from the
combo box into a string variable, reformats it (e.g. from
"Cat;Dog;Hamster;Gerbil" to "'Cat', 'Dog', 'Hamster', 'Gerbil'") and
finally interpolates it into the WHERE clause in your query.

 

[Stephen Doyle]

Hi Stephen,

In that case I'd change the row source type to Table/Query, put the list
of types into a little table and use that in the query.

The alternative is to write VBA code that gets the RowSource from the
combo box into a string variable, reformats it (e.g. from
"Cat;Dog;Hamster;Gerbil" to "'Cat', 'Dog', 'Hamster', 'Gerbil'") and
finally interpolates it into the WHERE clause in your query.

Thanks John, I did the table thing and it seems to work.
Cheers
Stephen