How can I use fields in a table to change the parameters in a que.

[Guest]

I have a db that has numerous queries. I need to change the parameters in
the 28 queries to the same information. Can I design a table with the
parameters I need and point the queries to use those when the quenry is run.
I know this can be done with a form but I would prefer to use a table.
These changes only need to be made yearly. ex: to use a form like
"*"&[forms]!{formname]![formfield] the problem with a form is that the
form has to be open to work, that's why i would prefer using a table and
entering my criteria one time.

thanks for your help!

 

[Guest]

Hi,
Yes, you can use a table to store your input, but you have to chage all 28
queries to join that table.

 

[Guest]

How do i join the table to the queries?

Hi,
Yes, you can use a table to store your input, but you have to chage all 28
queries to join that table.

I have a db that has numerous queries. I need to change the parameters in
the 28 queries to the same information. Can I design a table with the
parameters I need and point the queries to use those when the quenry is run.
I know this can be done with a form but I would prefer to use a table.
These changes only need to be made yearly. ex: to use a form like
"*"&[forms]!{formname]![formfield] the problem with a form is that the
form has to be open to work, that's why i would prefer using a table and
entering my criteria one time.

thanks for your help!

 

[Guest]

Thanks JL for your help How do I link the queries to the table?

 

[Guest]

Hi,
A simple example:
select Query1.*, Table1.*
from Query1 INNER JOIN Table1 ON Query1.Join_Field = Table1 .Join_Field
WHERE ...;

Or in the Query Design view, insert the table that have your input on it and
drag the field that you want join and link up to your table.

Hope this helps.

How do i join the table to the queries?

Hi,
Yes, you can use a table to store your input, but you have to chage all 28
queries to join that table.

I have a db that has numerous queries. I need to change the parameters in
the 28 queries to the same information. Can I design a table with the
parameters I need and point the queries to use those when the quenry is run.
I know this can be done with a form but I would prefer to use a table.
These changes only need to be made yearly. ex: to use a form like
"*"&[forms]!{formname]![formfield] the problem with a form is that the
form has to be open to work, that's why i would prefer using a table and
entering my criteria one time.

thanks for your help!

 

[Guest]

Mark:
Try opening the query in design view, and in the field you choose, type the
following for the Criteria: DLookUp("[MyField]","My Table")

Thanks JL for your help How do I link the queries to the table?

I have a db that has numerous queries. I need to change the parameters in
the 28 queries to the same information. Can I design a table with the
parameters I need and point the queries to use those when the quenry is run.
I know this can be done with a form but I would prefer to use a table.
These changes only need to be made yearly. ex: to use a form like
"*"&[forms]!{formname]![formfield] the problem with a form is that the
form has to be open to work, that's why i would prefer using a table and
entering my criteria one time.

thanks for your help!

 

[Guest]

Hi Mark,
Just a word of caution, Dlookup will not work if there is more than one
record in your table.

Mark:
Try opening the query in design view, and in the field you choose, type the
following for the Criteria: DLookUp("[MyField]","My Table")

Thanks JL for your help How do I link the queries to the table?

I have a db that has numerous queries. I need to change the parameters in
the 28 queries to the same information. Can I design a table with the
parameters I need and point the queries to use those when the quenry is run.
I know this can be done with a form but I would prefer to use a table.
These changes only need to be made yearly. ex: to use a form like
"*"&[forms]!{formname]![formfield] the problem with a form is that the
form has to be open to work, that's why i would prefer using a table and
entering my criteria one time.

thanks for your help!

 

[Guest]

Yes. I think that Mark intends to use this table especially for controlling
criteria in queiries and not for record-keeping, in which case, it is an easy
solution.

Hi Mark,
Just a word of caution, Dlookup will not work if there is more than one
record in your table.

Mark:
Try opening the query in design view, and in the field you choose, type the
following for the Criteria: DLookUp("[MyField]","My Table")

Thanks JL for your help How do I link the queries to the table?

:

I have a db that has numerous queries. I need to change the parameters in
the 28 queries to the same information. Can I design a table with the
parameters I need and point the queries to use those when the quenry is run.
I know this can be done with a form but I would prefer to use a table.
These changes only need to be made yearly. ex: to use a form like
"*"&[forms]!{formname]![formfield] the problem with a form is that the
form has to be open to work, that's why i would prefer using a table and
entering my criteria one time.

thanks for your help!

 

[Guest]

Actually, I don't see why DLookup couldn't be used with more than one record.
It would just be important to add a domain criteria to find the record that
you want (otherwise it would always return the field value from the first
record)

You could have a table with just two fields, such as:

CriteriaName, CriteriaVal

with a record for each criteria name and the corresponding criteria value
that you would want to look up.

Then, you could use DLookup to specify the appropriate CriteriaName in the
domain criteria, and CriteriaVal as the lookup column, such as:

DLookup("CriteriaVal","CriteriaTable","CriteriaName = SomeCriteria").

Of course, you would have to be careful with how to handle different field
types if they vary. In some cases you may have to take the val() of the
Dlookup result (for numeric fields), or possible concatenate # signs around
it for dates (or use a DateVal() function). But, in concept, I think that
the DLookup part should work fine.

HTH, Ted Allen

Hi Mark,
Just a word of caution, Dlookup will not work if there is more than one
record in your table.

Mark:
Try opening the query in design view, and in the field you choose, type the
following for the Criteria: DLookUp("[MyField]","My Table")

Thanks JL for your help How do I link the queries to the table?

:

I have a db that has numerous queries. I need to change the parameters in
the 28 queries to the same information. Can I design a table with the
parameters I need and point the queries to use those when the quenry is run.
I know this can be done with a form but I would prefer to use a table.
These changes only need to be made yearly. ex: to use a form like
"*"&[forms]!{formname]![formfield] the problem with a form is that the
form has to be open to work, that's why i would prefer using a table and
entering my criteria one time.

thanks for your help!