How do a make a query see a text string for criteria as a Variable not a Value?

  • Thread starter Thread starter jason potter
  • Start date Start date
J

jason potter

How do a make a query see a text string for criteria as a
Variable not a Value?
Solution Table
ID ID_CS ID_SF Title_CS
23 RIIS-CS-004 EK-S-026, RAY-S-003, RAY-S-039,
RAY-S-040, RAY-S-042 (Test data)
24 RIIS-CS-005 EK-S-010, EK-S-011, EK-S-013, EK-
S-014, RAY-S-010, RAY-S-012, RAY-S-020 (Test data)

Shortfalls Table
ID ID_SF Test Title
76 EK-S-026 Test Title
77 EK-S-027 Test Title

I need to query on the on the shortfalls table based upon
the ID_SF in the solution table.

First I create a Solution Table Parameter query by the
ID_CS desired this create will Isolate the Solution ID_SF
Text string. Then copy the Text String

Second I run another Parameter query on the Shortfalls
Table and then paste the Solution ID_SF Text string as
the Criteria for the Shortfalls ID_SF field Using the IN
() Function. In ("EK-S-026","RAY-S-003","RAY-S-039","RAY-
S-040","RAY-S-042")
Notice it places the "" around each item for the string.

This works fine. However: here lye's the problem.
1. I need to use the result from the first query to
auto populate the criteria for the Shortfalls Query.
2. If I try to use SQL or a form or anything it
looks at the text string as a complete value not as a
Variable. IE: IN ([Query1]![ID_SF] ) and the result is a
Null.
3. How can I automate this process without pasting
the criteria each time?
4. I understand that the correct solution would have
been to link the two tables by placing the SF_ ID one to
many relationships in the shortfalls table. However I'm
getting this problem with a little over 48,000 records.
 
Why don't you simply use an INNER JOIN between the two
tables ON ID_SF?

Hope This Helps
Gerald Stanley MCSD
 
I would However it see's the text string as a single
value not as a Variable. In excel I'd use an ARRAY not
available in access?
-----Original Message-----
Why don't you simply use an INNER JOIN between the two
tables ON ID_SF?

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
How do a make a query see a text string for criteria as a
Variable not a Value?
Solution Table
ID ID_CS ID_SF Title_CS
23 RIIS-CS-004 EK-S-026, RAY-S-003, RAY-S-039,
RAY-S-040, RAY-S-042 (Test data)
24 RIIS-CS-005 EK-S-010, EK-S-011, EK-S-013, EK-
S-014, RAY-S-010, RAY-S-012, RAY-S-020 (Test data)

Shortfalls Table
ID ID_SF Test Title
76 EK-S-026 Test Title
77 EK-S-027 Test Title

I need to query on the on the shortfalls table based upon
the ID_SF in the solution table.

First I create a Solution Table Parameter query by the
ID_CS desired this create will Isolate the Solution ID_SF
Text string. Then copy the Text String

Second I run another Parameter query on the Shortfalls
Table and then paste the Solution ID_SF Text string as
the Criteria for the Shortfalls ID_SF field Using the IN
() Function. In ("EK-S-026","RAY-S-003","RAY-S- 039","RAY-
S-040","RAY-S-042")
Notice it places the "" around each item for the string.

This works fine. However: here lye's the problem.
1. I need to use the result from the first query to
auto populate the criteria for the Shortfalls Query.
2. If I try to use SQL or a form or anything it
looks at the text string as a complete value not as a
Variable. IE: IN ([Query1]![ID_SF] ) and the result is a
Null.
3. How can I automate this process without pasting
the criteria each time?
4. I understand that the correct solution would have
been to link the two tables by placing the SF_ ID one to
many relationships in the shortfalls table. However I'm
getting this problem with a little over 48,000 records.

.
Click to expand...
.
Click to expand...
 
Back
Top