# lookup help needed for selective items depending on previous field



## arnold_fok

calculated field : iff([bank]=9,'abc','cat')  shows correctly
SELECT list.id, list.name FROM list WHERE list.soort='in' Or IIf([bank]=9,'abc',' cat');  shows other soort as wel (no where clause?)
( list is table with multiple lookups works fine if not want different short list depending on other field)


----------



## arnold_fok

arnold_fok said:


> calculated field : iff([bank]=9,'abc','cat')  shows correctly
> SELECT list.id, list.name FROM list WHERE list.soort='in' Or IIf([bank]=9,'abc',' cat');  shows other soort as wel (no where clause?)
> ( list is table with multiple lookups works fine if not want different short list depending on other field)


the space before cat is only here by accident, if i test sql with popup it shows correctly, but in lookup its always cat+in never abc+in


----------



## jackd

"iff([bank]=9,'abc','cat')  shows correctly"???????

The immediate if command is *iif* not _*iff*_


----------



## arnold_fok

jackd said:


> "iff([bank]=9,'abc','cat')  shows correctly"???????
> 
> The immediate if command is *iif* not _*iff*_


----------



## Joe4

Welcome to the Board!

Can you show us a small sample of your data table, and explain exactly which of those records you want to return?


----------



## arnold_fok

Joe4 said:


> Welcome to the Board!
> 
> Can you show us a small sample of your data table, and explain exactly which of those records you want to return?


the list table has 3 fields : autonumber id,, txt name, txt group. the group refer to the field it should offer the dropdown list , this work correctly with a constant.
depending on  a previous dropdown selection i like to have different dropdown to shorten the list and avoid wrong use example : 
buy small article using $ ; buy large article using euro ; buy hughe article using yen that are different accounts different kind of articles , so by creating 3 groups extra the account should use the group related to that account. as stated before running the sql script it show different list depending the value i key in, just the way i like it. but having that script in my table dropdown it not show the true value like the script do.only the false value.


----------



## Joe4

Have a look here: MS Access 2013 - One dropdown dependent on another in datasheet view


----------



## arnold_fok

arnold_fok said:


> the list table has 3 fields : autonumber id,, txt name, txt group. the group refer to the field it should offer the dropdown list , this work correctly with a constant.
> depending on  a previous dropdown selection i like to have different dropdown to shorten the list and avoid wrong use example :
> buy small article using $ ; buy large article using euro ; buy hughe article using yen that are different accounts different kind of articles , so by creating 3 groups extra the account should use the group related to that account. as stated before running the sql script it show different list depending the value i key in, just the way i like it. but having that script in my table dropdown it not show the true value like the script do.only the false value.


(Like & "*" & Forms![FormName]![Combo1]  & "*") i do not have forms i just use dataview, the combobox contain the query that runs fine on the place wherethat forms is suggested


d=dropdown,f=field,#=column,da/db=subdropdown



SELECT reference.reference
FROM reference
WHERE (((reference.dropdown)=(select distinct [reference] from reference where [id]= [Field1])))
ORDER BY reference.reference;
this sql show only the few related by dropdown and if reference is daf2 it show ef as expected
but as u see field2 is empty not ef


----------



## Joe4

I typically would not do this directly at the table level.  I would usually do it at the Form level, and use VBA where you can control what gets updated when.
But there are a lot of other threads, tutorials, and videos out there dealing with the the subject if you just go a Google search on "*Microsoft Access Table Drop-Down Field Dependent on Another Field*".
Perhaps you will find something more to your liking.


----------



## arnold_fok

Joe4 said:


> I typically would not do this directly at the table level.  I would usually do it at the Form level, and use VBA where you can control what gets updated when.
> But there are a lot of other threads, tutorials, and videos out there dealing with the the subject if you just go a Google search on "*Microsoft Access Table Drop-Down Field Dependent on Another Field*".
> Perhaps you will find something more to your liking.


mmm u like to confuse ur colleagues by switching to forms while they learn to use lookup in database, but tnx


----------



## arnold_fok

calculated field : iff([bank]=9,'abc','cat')  shows correctly
SELECT list.id, list.name FROM list WHERE list.soort='in' Or IIf([bank]=9,'abc',' cat');  shows other soort as wel (no where clause?)
( list is table with multiple lookups works fine if not want different short list depending on other field)


----------



## Joe4

arnold_fok said:


> mmm u like to confuse ur colleagues by switching to forms while they learn to use lookup in database, but tnx


I am not trying to confuse anyone at all, just trying to teach good database design (giving them access to underlying tables in dangerous).
In a well-designed database, your users should NOT have access to the underlying tables.  You usually want to limit them to Menus, Forms, and Reports, where you have much greater control of all the data, and can control exactly how data is presented and how users interact with the data.

One of the big issues with trying to do something at the table level where Field B is dependent upon the selection in Field A, what is to keep them from trying to update Field B before Field A?
You cannot control that at the Table level.

Regardless of all that, I told you exactly how you can find out exactly what you want to do.  If you take just a few minutes to da a simple Google search, you will find many posts regarding your topic.
There aren't too many questions which haven't already been asked and answered already.  It just takes a few minutes to search for them.


----------



## arnold_fok

Joe4 said:


> I am not trying to confuse anyone at all, just trying to teach good database design (giving them access to underlying tables in dangerous).
> In a well-designed database, your users should NOT have access to the underlying tables.  You usually want to limit them to Menus, Forms, and Reports, where you have much greater control of all the data, and can control exactly how data is presented and how users interact with the data.
> 
> One of the big issues with trying to do something at the table level where Field B is dependent upon the selection in Field A, what is to keep them from trying to update Field B before Field A?
> You cannot control that at the Table level.
> 
> Regardless of all that, I told you exactly how you can find out exactly what you want to do.  If you take just a few minutes to da a simple Google search, you will find many posts regarding your topic.
> There aren't too many questions which haven't already been asked and answered already.  It just takes a few minutes to search for them.


mmm u refer to user access, i refer to restricted lookup , before u refer to search i did lot of those research already and try and retry.
if user try field2 before field1 then its empty and not allowed perhaps ur design is not as solid as u suggest by using form if table support


----------

