Dynamic Criteria

[Littlemalky]

I need to know how to make a criteria dynamic to read a field in another table so that whenever the data in that field changes the criteria can adjust to it. The data in that field is a date, if that's relevant. Is this possible?

 

[Joe4]

Is it a one-record table? If so, just add it to your query. You won't even need to do a join if it has just one record. Then it is available for you to use.

 

[Littlemalky]

No, it is not one record. How would you set up the criteria if it were one record?

 

[Joe4]

Can you post your code and explain exactly what you are trying to do?
Maybe post some data samples?

 

[jackd]

Suppose the field in the otherTable that contains the Date is CriteriaDate,
and the table you are dealing with is CurrentTable and the field to be compared is MyDate.
Then a query along these lines should work. I'm using =, but it could be < or > etc.

Select
CurrentTable.field1,
CurrentTable,field2,
CurrentTable.fieldx
from CurrentTable, otherTable
WHERE
CurrentTable.MyDate = otherTable.CriteriaDate

 

[Shogun]

Access Criteria Issue

I have a similar problem and tried to use your example jack but it’s not working for me. I’m new to SQL but know VBA well so bare with me as I learn. I am uploading a spreadsheet into a table, then I’m trying to run a delete query in another table that holds the history of this spreadsheet, I want to make the delete query's criteria to be the avg of one of the fields from the first table that happens to be a date. After that is done I will be appending the new table over to the history table. How do I call the field from the first table as the criteria for the query on the second table?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[jackd]

I have a similar problem and tried to use your example jack but it’s not working for me. I’m new to SQL but know VBA well so bare with me as I learn. I am uploading a spreadsheet into a table, then I’m trying to run a delete query in another table that holds the history of this spreadsheet, I want to make the delete query's criteria to be the avg of one of the fields from the first table that happens to be a date. After that is done I will be appending the new table over to the history table. How do I call the field from the first table as the criteria for the query on the second table?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Can you show your tables and what you want to achieve? I'm confused with the Avg of one of the fields that happens to be a Date ?

 

[Joe4]

Re: Access Criteria Issue

So as not to hijack the original poster's question, I have split your question from this from the original thread and made it its own thread.

 

[Shogun]

Re: Access Criteria Issue

Actually Malky and I work together so it was supposed to be under the same issue.

 

[Joe4]

Actually Malky and I work together so it was supposed to be under the same issue.

OK, I moved it back.

Jack and I both requested more information so we can get a better picture of what you trying to do. We are awaiting on responses to those requests so we can help you (details, please!).