Query references table for comparison

[Jason Lopez]

I have a query that I am working on. However, I don't want to hard code all
of the conditions into the one expression. Instead I would like to somehow
have a IIf statement that will refer to a table that would have the various
conditions and the appropriate response that the IIf statement should spit
out. So essentially something that would look perhaps something like this:

IIf ([ProjectName] Like
"*"+[tbl_compare].[cond]+"*",[tbl_compare].[response],0)

So if I had the table filled like this:

[Cond],[Response]
Bake, Cook
Cold, Refrigerate
Salmon, Fish

Then, when the query would search the project name a find an instance of
"Cold" anywhere in the name, it would return the response of "Refrigerate."
I hope this has been clear enough for someone to help.

Jason

 

[KARL DEWEY]

Did you try it?
I did not see a question.

Maybe like this --
IIf ([ProjectName] Like "*" & [tbl_compare].[cond] &
"*",[tbl_compare].[response],"")

 

[Jason Lopez]

Yes. I tried it like that before I posted. But I remember seeing something
that mentioned how to check the listing in a table for a methodical check
without having to write the IIf statement multiple times and risk and error.

The only response I get from Access when I run that query with the one field
written as I wrote is it asking for the value of "[tbl_compare].[cond]" and
"[tbl_compare].[response]" and no actual comparison. The tbl_compare
already has values in it.

But, afterwards I wondered if adding the table into the SQL code would help
and it did. I just needed to include the table as part of the SQL code
rather than leave it totally seperate.

Jason

Did you try it?
I did not see a question.

Maybe like this --
IIf ([ProjectName] Like "*" & [tbl_compare].[cond] &
"*",[tbl_compare].[response],"")

--
KARL DEWEY
Build a little - Test a little

I have a query that I am working on. However, I don't want to hard code
all
of the conditions into the one expression. Instead I would like to
somehow
have a IIf statement that will refer to a table that would have the
various
conditions and the appropriate response that the IIf statement should
spit
out. So essentially something that would look perhaps something like
this:

IIf ([ProjectName] Like
"*"+[tbl_compare].[cond]+"*",[tbl_compare].[response],0)

So if I had the table filled like this:

[Cond],[Response]
Bake, Cook
Cold, Refrigerate
Salmon, Fish

Then, when the query would search the project name a find an instance of
"Cold" anywhere in the name, it would return the response of
"Refrigerate."
I hope this has been clear enough for someone to help.

Jason

 

[Jason Lopez]

Actually, now I run into another problem. I add that new IIf statement into
the main query with the added table and all of the sudden I get the
occurrences of the record multiple times (the number of times as options
that exist in the [tbl_compare]. Is there anyway to fix this as I only need
a response based on what is found? Only one response per record regardless
of the number of options. I could just go with doing the IIf statement at
the end of the line on the query string and just filter the null responses.
But, if I can include it at the start of the query, then all the data is
available for review and manipulation as it goes down the line.

Jason

Did you try it?
I did not see a question.

Maybe like this --
IIf ([ProjectName] Like "*" & [tbl_compare].[cond] &
"*",[tbl_compare].[response],"")

--
KARL DEWEY
Build a little - Test a little

I have a query that I am working on. However, I don't want to hard code
all
of the conditions into the one expression. Instead I would like to
somehow
have a IIf statement that will refer to a table that would have the
various
conditions and the appropriate response that the IIf statement should
spit
out. So essentially something that would look perhaps something like
this:

IIf ([ProjectName] Like
"*"+[tbl_compare].[cond]+"*",[tbl_compare].[response],0)

So if I had the table filled like this:

[Cond],[Response]
Bake, Cook
Cold, Refrigerate
Salmon, Fish

Then, when the query would search the project name a find an instance of
"Cold" anywhere in the name, it would return the response of
"Refrigerate."
I hope this has been clear enough for someone to help.

Jason