Function should replace parts of a query

[Ralf]

Hi All,

Assumed I've a query with more then 20 WHERE conditions, like:
SELECT * FROM table_a
WHERE
(column_a IS NOT NULL) AND
(column_b IS NOT NULL) AND
...
(column_x IS NOT NULL);

Since I use exactly the same WHERE conditions in another content, I copy and
paste it to another query. Now, when the WHERE conditions need to be changed,
I need to change it in all queries where it appears. I hoped applying a
function would facilitate this:

Public Function GetConditions() As String
GetConditions="(column_a IS NOT NULL) AND (column_b) [...]"
End Function

Now include the function in all queries:
SELECT * FROM table_a WHERE (GetConditions());

But this does not work. Now, does someone has an idea how to avoid these
annoying copy and paste activities?

Thank you,
Ralf

 

[Ralf]

I should mention that I work with the Query Builder in MS Access. Replacing
parts in a query would be easy when I would code the thing. But since the
customer wants to have those queries accessible with the Query Builder, I
have to go that way.

 

[Marshall Barton]

Assumed I've a query with more then 20 WHERE conditions, like:
SELECT * FROM table_a
WHERE
(column_a IS NOT NULL) AND
(column_b IS NOT NULL) AND
...
(column_x IS NOT NULL);

Since I use exactly the same WHERE conditions in another content, I copy and
paste it to another query. Now, when the WHERE conditions need to be changed,
I need to change it in all queries where it appears. I hoped applying a
function would facilitate this:

Public Function GetConditions() As String
GetConditions="(column_a IS NOT NULL) AND (column_b) [...]"
End Function

Now include the function in all queries:
SELECT * FROM table_a WHERE (GetConditions());

But this does not work. Now, does someone has an idea how to avoid these
annoying copy and paste activities?

The REASON it does not work because your function has no
arguments to tell it what to check AND because the query
optimizer will only call the function once. The function is
called only once because the function argument values do not
change from one record to another. To deal with this you
need to change the function so it has an argument for every
field you want to check, which would make the where
condition only a little shorter than before and would not
help with the problem of modifying all the places it is
used.

Your PROBLEM appears to be caused an unnormalized table
design.