Hello,
I’m using Access 2013 in Windows 7.
I’m trying to use a formula that will tell me if a value from a field in the Design table exists in another field in the Schedule table. If so, then return an “A” in a new field I created in the Design table; if not, return a “B”. I know in Excel it would be =IF(ISERROR(VLOOKUP(A2,$C$2:$C$11, 1, FALSE)),”B”,”A” ).
Backstory: This for the cable TV industry, where a Node, for our project, can have 2 sides, an A side and a B side. The A and B sides will have different names from each other. The Design table has the nodes listed in a single column with no note as to whether it is an A or B side; however, the Schedule table the nodes listed in two separate columns, as Node A and Node B.
I’ve created a form based on two tables, the Design table and the Schedule table. Near the end of creating the form, I realized a needed a new field from the Design table on my form, called Node Side (showing whether a Node was the A or the B side).
So, I created a new field in the Design table called NodeSide and an Update Query as follows: https(colon)//drive.google.com/open?id=0B9IyKJSJ52ghRl93ZnJyVDB4eTA
but the formula, IIf(DLookUp(“[Node]”,”tblSchedule, etc.”,”[NodeA]”=”[Node]”),”A”,”B”), doesn’t work.
Do you know of a formula that would work? Please don’t supply an answer in SQL or VBA unless there is no other solution. I’m trying to ease into more advanced features in Access, if you don’t mind.
Thank you in advance.
P.S. I'm not sure if ELookUp would be better. http(colon)//allenbrowne.com/ser-42.html this site says it is a better function. I don't know.
I’m using Access 2013 in Windows 7.
I’m trying to use a formula that will tell me if a value from a field in the Design table exists in another field in the Schedule table. If so, then return an “A” in a new field I created in the Design table; if not, return a “B”. I know in Excel it would be =IF(ISERROR(VLOOKUP(A2,$C$2:$C$11, 1, FALSE)),”B”,”A” ).
Backstory: This for the cable TV industry, where a Node, for our project, can have 2 sides, an A side and a B side. The A and B sides will have different names from each other. The Design table has the nodes listed in a single column with no note as to whether it is an A or B side; however, the Schedule table the nodes listed in two separate columns, as Node A and Node B.
I’ve created a form based on two tables, the Design table and the Schedule table. Near the end of creating the form, I realized a needed a new field from the Design table on my form, called Node Side (showing whether a Node was the A or the B side).
So, I created a new field in the Design table called NodeSide and an Update Query as follows: https(colon)//drive.google.com/open?id=0B9IyKJSJ52ghRl93ZnJyVDB4eTA
but the formula, IIf(DLookUp(“[Node]”,”tblSchedule, etc.”,”[NodeA]”=”[Node]”),”A”,”B”), doesn’t work.
Do you know of a formula that would work? Please don’t supply an answer in SQL or VBA unless there is no other solution. I’m trying to ease into more advanced features in Access, if you don’t mind.
Thank you in advance.
P.S. I'm not sure if ELookUp would be better. http(colon)//allenbrowne.com/ser-42.html this site says it is a better function. I don't know.