Forget the form for a moment. I thought I was simplifying it by simply
pulling the checkbox result from a query as a Yes/No result versus pulling it
from a from a an "Is Not Null" criteria. Why does that have any bearing on
the formula itsself? I have a query result that holds the results of the
check boxes from the form input.
From there I want to say if the result in a specific field is "yes" then
perform the lookup, if not then use zero. If the result to the IIf statement
is "yes" then I want it to perform the lookup based on a value from a
different table (28,000 for example) compared to the hi/lo values and give me
a result (one number) from a specific column on another table.
Does that make it any clearer? The method of how the result from a checkbox
or yes/no answer is gathered is secondary here. I would be happy simply
understanding just how the basic lookup when used with the between command
works!
SELECT IIf([Forms]![YourForm]![CheckBoxABC] Is Not Null,"abc " &
[abc],IIf([Forms]![YourForm]![CheckBoxDEF] Is Not Null,"def " & [def],"ghi "
& [ghi])) AS [Selected Output]
FROM Lookup
WHERE ((([User input value]) Between [Lo_Accts] And [Hi_Accts]));
KARL DEWEY said:
Your orignal post said you had a form for user to input 28,000 and
checkboxes. What you just posted does not look like any form input to a
query. I do not follow your SQL.
Lookup value from form vs table column said:
Okay, after slowing down and reading what is actually happening I understand
your SELECT statement. I have added an additional field to my table and now
have high and low fields (great idea!). Now I placed the statement below in
a query nder the "Field" (not criteria correct?) I replicated your formula
to better suit my database and it now reads as follows:
SELECT IIf([Calulated Proposal Details]![ODS]=Yes,"ODS" &
[ODS],0) AS [DW IH Rtl Price/IHCost/OutsrcCost License]![CBSODS] FROM Lookup
WHERE ((([# of Total Accounts]) Between [Lo Accounts] And [High Accounts]))
I keep getting the message "Check the subquery's syntax and enclose the
subquery in parentheses". I think we are almost there if I can resolve the
syntax issue. Any thoughts?
Thanks for your help!
:
How would you use a similar formula in a query?
What I post was a query.
how can I get the result from your forumla in to populate another table?
I do not understand your question.
Explain more as to what you are trying to do.
:
I used a Hi & Lo Accts field in the table. Try this --
SELECT IIf([Forms]![YourForm]![CheckBoxABC] Is Not Null,"abc " &
[abc],IIf([Forms]![YourForm]![CheckBoxDEF] Is Not Null,"def " & [def],"ghi "
& [ghi])) AS [Selected Output]
FROM Lookup
WHERE ((([User input value]) Between [Lo_Accts] And [Hi_Accts]));
:
Let me see if I can illustrate my issue:
TABLE
# of Accts abc DEF ghi
5000 $5,000 $2,000 $300
10000 $5,500 $2,500 $350
25000 $6,000 $3,000 $400
30000 $6,500 $3,500 $450
35000 $7,000 $4,000 $500
From the table above I have a field from a form where the user inputs a
value, say 28,000 and then they select a check box for item {abc}. I want to
write something (query or an expression in a form) that compares the input
value to the first column in the table, "# of Accts", and return the value
under the checked box column "abc" of $6,000 (greater than 25,000 but less
than 30,000). How can I do this? In Excel it is a simple lookup formula.
Is there an equivalent in Access or do I need to design the database
differently?