By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,233 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Expression not recognised when using form selections in queries

P: 2
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.
Feb 7 '07 #1
Share this Question
Share on Google+
5 Replies


P: 2
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!
Feb 7 '07 #2

ADezii
Expert 5K+
P: 8,597
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:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.QueryDefs("qryTest").SQL = "SELECT tblEmployees.FirstName FROM " & _
  2.                                      "tblEmployees ORDER BY tblEmployees.FirstName DESC;"
Feb 7 '07 #3

Rabbit
Expert Mod 10K+
P: 12,315
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.
Feb 7 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 8 '07 #5

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 8 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.