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]

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]

"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]

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]

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]

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