Iff Statement stored in table

[zkoneffko]

I am developing a access program that has a large amount of queries in it. Most of the queries will use an iff statement in them. The iff statement will be the same across most queries but it will also change every few weeks. Is it possible to save the iff statement in a table and just call that field in the queries? This would allow me to change the iff statement in only one place. How would I go about calling this is the query so it looks at it as an iff statement not just a line of text like it seems to be doing.

 

[Rabbie]

I am developing a access program that has a large amount of queries in it. Most of the queries will use an iff statement in them. The iff statement will be the same across most queries but it will also change every few weeks. Is it possible to save the iff statement in a table and just call that field in the queries? This would allow me to change the iff statement in only one place. How would I go about calling this is the query so it looks at it as an iff statement not just a line of text like it seems to be doing.

Do you mean "IIF". It would not be good practice to store an "iif" expression in a table. You shouldn't have calculated fields in tables. Your question makes me wonder if your design is normalised. I would advise you to look up "DAta Normalization" both in these forums and in Access help. Make sure you understand and implement it or you will have a lot of avoidable problems in future

 

[zkoneffko]

Yes I meant iif, sorry for the mistype. I understand it might not be the best way but I personally don't know another way. If someone can tell me another way to only change the iif statement in one place to affect a large amount of queries I willing to listen. If not if there a possibility to do it this way?

 

[Rabbie]

If we actually knew what you want to do in more detail and knew what your table format was it would be much easier to advise you. AFAIK it is not possible to have an IIF in a table. It just wouldn't make sense in a correctly normalised DB.

If you can give examples of what you are trying to do it would help

 

[zkoneffko]

I trying to have a way to to have the same iif statement across multiple queries without have to go in and change each query every time that statement changes. The statement will change rather often so this would make future use much easier.

 

[Rabbie]

I trying to have a way to to have the same iif statement across multiple queries without have to go in and change each query every time that statement changes. The statement will change rather often so this would make future use much easier.

You have already told us that. Why can't you post some samples of what you are trying to do and how your tables are structured. As already stated it doesn't make sense to have an iif statement in a table. I get the impression your design is seriously flawed but since you wont publish your design it hard to be sure.

 

[MSAccessRookie]

I trying to have a way to to have the same iif statement across multiple queries without have to go in and change each query every time that statement changes. The statement will change rather often so this would make future use much easier.

I think he means tell what the purpose of the project is. Many times that helps. Seeing examples of the query you want to execute would also be a plus

Addendum:

Rabbie's point about table structures is also very good. So to sum it up, to provide the best assistance to you, we need:

1. An idea as to the purpose of the project to help to understand the need.
2. An example of the Design of the Table(s) (No Data Required, just the design) to see how to assist
3. An example of the Query that you want to execute.

 

[The_Doc_Man]

The problem you have is that the IIF is really a form of metadata but is stored as literal data in the table. You are attempting to cross a barrier that is there for a reason.

Table lookups are supposed to return ONLY data (recordsets). They are supposed to be pretty much unconditional. To store an IIF in a table is going to be a pain in the toches because there is no valid context in which that IIF can execute. The barrier I mentioned earlier has to do with the Access expectation of what a table contains. It is a design barrier that if you have an "IIF" inside a field, it is simple text when in recordset context and there is no reasonable way for the table-based recordset to know that it has anything to evaluate. Essentially, in a table, there IS NOT (CANNOT BE) anything deferred or computed.

Query evaluation is by its nature a slower process and is very different. If it takes a little while to evaluate that query, it is kind of "expected behavior." If there is a function or something else in a given QUERY field (not the underlying table field), SQL will try to evaluate it. The catch here is that crossing that data/meta-data interface is considered a violation of good programming practices. You see, it presumes that the contained data in that function-field hasn't been compromised. One mistake and suddenly you killed everything that depended on that single datum. You introduced a single point of failure in your code. Which where I work is grounds for dismissal if you do it too often.

OK, having explained why it is risky and having given other appropriate admonitions, here are a couple of ways to approach the issue. Both suffer from the same exact failing: There is no function/data checking here. Either it works or it blows up right away due to bad data in the targeted field.

1. Edit your "IIF" statement in the appropriate table. Then dynamically build your queries using the string stored in that table plus whatever else you needed to include in the query's recordset. Then and only then execute the dynamic query. Or store the query, which will cause it to be evaluated for optimization when you store it. You would of course have to eliminate older copies with the same name or generate a unique name. The former is tricky and requires strict adherence to a programming convention on the name. The latter risks running out of database objects if you aren't careful. Not to mention making everything run slower when it has to search the database's object list to find the query you wanted to run.

2. Have your queries include the field as a reference the "EVAL" function (which you can look up in Access help). EVAL( [fieldname] ) will evaluate any expression (without the leading equals-sign) in the database field if that expression returns either a text string or a numeric value. You will get back a VARIANT data type when you use this function, so VBA code based on that field in your query would have to know to receive a VARIANT or do a typecast. If there is a problem with the field, you won't know it because Access cannot check on this expression with the same attention associated with other query expressions.

Design-wise, you probably need to make this query a JOIN of the contents of that single table where you store the IIF with the table to which it applies. That might represent yet another can of worms, but if you have to do some fishing around, maybe that is what you need anyway.

Be it herein noted that this is "playing with fire" and could very well cause you to get burned. You have been warned.

 

[Brianwarnock]

Has as already been stated we do not know anything about the project, but is there the possibility that the IIf could be replaced by a Function then the poster need only change the Function?

This is why we need the information requested earlier.

Brian

 

[gemma-the-husky]

you can accomplish more with a general purpose function, that returns a boolean true or false

so in a module

function myquery(field1, field2, field3 etc) as boolean

'then within here you can say anything much easier than within an iif statement
compare field arguments to stored variables etc
use dlookups etc to gather other information
finally return a function value of either true or false


Then in your query

you just have

myquery(arg1, arg2, arg3) as a column in the query
and set criteria value to true or false, as you need