Expression not recognised when using form selections in queries

[EricS]

Hi,

Was wondering if anyone can help. I have multiple queries in a database. The selection criteria for these queries is always a time period I want to do the analysis by. Rather than having to go into each query individually and update the criteria to reflect the month I want to analyse by, is there an easy way to change the query criteria once?

I have tried doing this by linking to a form with a combo box getting values from a table with all valid time periods stored in it (which had the disadvantage that the form actually had to be open all the time when running the queries). I've been having problems with this - getting error message saying the jet database engine does not recognise the combo box as a valid field or expression (even when i set it up using the builder rather than typing). Can anyone either help solve this problem or help me with an alternative that would work? E.g storing the value selcted in the combo box in a table and getting to query to run based on that instead - but would only want the current value stored - i.e. each change in the selection from the combo box shoud overwrite anything stored rather than adding a new record - eg if Jan 2004 is selected the first time, and queries run on this basis, i would want all queries to run on this value, until I went into the form and changed it for the following month Feb 2004 - at whioch point would want the queries to use this month not Jan 2004.

[EricS]

Hi,

I have multiple queries in a database. The selection criteria for these queries is always a time period I want to do the analysis by. Rather than having to go into each query individually and update the criteria to reflect the month I want to analyse by, is there an easy way to change the query criteria once?

I have tried doing this by linking to a form with a combo box getting values from a table with all valid time periods stored in it (which had the disadvantage that the form actually had to be open all the time when running the queries). I've been having problems with this - getting error message saying the jet database engine does not recognise the combo box as a valid field or expression (even when i set it up using the builder rather than typing). Can anyone either help solve this problem or help me with an alternative that would work? E.g storing the value selcted in the combo box in a table and getting to query to run based on that instead - but would only want the current value stored - i.e. each change in the selection from the combo box shoud overwrite anything stored rather than adding a new record - eg if Jan 2004 is selected the first time, and queries run on this basis, i would want all queries to run on this value, until I went into the form and changed it for the following month Feb 2004 - at which point would want the queries to use this month not Jan 2004.

Help please!

[ADezii]

Hi,

I have multiple queries in a database. The selection criteria for these queries is always a time period I want to do the analysis by. Rather than having to go into each query individually and update the criteria to reflect the month I want to analyse by, is there an easy way to change the query criteria once?

I have tried doing this by linking to a form with a combo box getting values from a table with all valid time periods stored in it (which had the disadvantage that the form actually had to be open all the time when running the queries). I've been having problems with this - getting error message saying the jet database engine does not recognise the combo box as a valid field or expression (even when i set it up using the builder rather than typing). Can anyone either help solve this problem or help me with an alternative that would work? E.g storing the value selcted in the combo box in a table and getting to query to run based on that instead - but would only want the current value stored - i.e. each change in the selection from the combo box shoud overwrite anything stored rather than adding a new record - eg if Jan 2004 is selected the first time, and queries run on this basis, i would want all queries to run on this value, until I went into the form and changed it for the following month Feb 2004 - at which point would want the queries to use this month not Jan 2004.

Help please!

It is a simple matter to change a Query's underlying SQL. Assuming your Query Name is qryTest, then:

CurrentDb.QueryDefs("qryTest").SQL = "SELECT tblEmployees.FirstName FROM " & _
                                     "tblEmployees ORDER BY tblEmployees.FirstName DESC;"

[Rabbit]

How are the dates stored? I'm assuming you're running queries based on
SomeDate BETWEEN DateStart AND DateEnd
If this is the case, then the table must have 2 fields, DateStart and DateEnd. These two fields must be stored as a Date/Time Data Type.
The form then, if you're going to use combo boxes, would need either two combo boxes or one combo box with the end date being calculated from the start date.

[MMcCarthy]

This was a double post and I have merged both of them together and deleted Rabbits posts pointing to the other post. I agree with Rabbit I think this was accidental.

ADMIN

[NeoPa]

Although you can put SQL directly into a QueryDef (See ADezii's post #3) I normally take it out of the QueryDef first, update only the relevant bits, then put it back.
This saves having the full complication of the query being stored in your code.
NB. This is actually a design change to your database, so you should handle multi-user interaction carefully.