Formula to see if value exists in another field in another table (1 Viewer)

Apples241

Registered User.
Local time
Today, 03:39
Joined
Aug 29, 2016
Messages
54
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.
 

plog

Banishment Pending
Local time
Today, 05:39
Joined
May 11, 2011
Messages
11,611
Without an explicit example its hard to follow the specifics. So I focus on this:

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

To achieve that I would use a DCOUNT. It will count the number of records that meet your criteria. If 0, no matches, if greater than 0 that means it exists. DCount works very similar to Dlookup--in fact if you have a working DLookup you can change it to DCount and it will produce accurate results.

Of course, your Dlookup isn't syntatically correct. What you have for your Dlookup makes no sense to the computer:

Code:
DLookUp(“[Node]”,”tblSchedule, etc.”,”[NodeA]”=”[Node]”)

Dlookup\DCount takes 3 arguments:

1. The field to lookup\count. As long as Node is a field in your table, you got this part right.

2. The table/query to use to find that field. Seriously, you named a table/query "tblSchedule, etc."? Bad choice. You should only use alphanumeric characters and underscores in table and field names. No spaces, no punctuation. When you use characters like you have done, you need to put the table name in brackets like you did with the field name.

3. The criteria. This should be a string and you do not have a properly constructed string:

”[NodeA]”=”[Node]”

A string can be constructed of 2 types of data--other strings which are enclosed in quote marks or variables/field names. When you end one type and want to use another type you must use the ampersand & to connect them:

Code:
VarX="this is a variable"
"This is a string and " & VarX & ": Together I've made one string out of them"

You have an equals sign in no man's land and even if that wasn't there you never used a variable/field in your criteria.
 

Apples241

Registered User.
Local time
Today, 03:39
Joined
Aug 29, 2016
Messages
54
Thank you for your reply, plog. I had problems with translating the theory from some other websites to my particular problem, so it's hard from me to follow without examples in this particular case.

My work around is pretty long, but I learned something new about IIF(MOD, etc.):

1. I used a formula** in an Update Query to populate the Node Side field in my Design table in Access with A’s and B’s, not caring if it was matched to the correct node or not.

2. I had an Excel spreadsheet of the Design table that already correctly matched the A and B sides with each node.

3. I just looked at that Excel spreadsheet to manually correctly the Design table in Access. There weren't that many corrections, thank God.

Use plog’s method by all means--mine was a long work around.

** The formula I used in the Update To: line of the new Node Side field was IIf([FieldName] Mod 2,"A", "B" ). This formula looked up the field name DesignID, which was just 1,2,3,4, etc., and returned an “A” for odd number Design IDs and “B” for even number Design IDs. Ex: IIf[DesignID] Mod 2, “A”, “B”), where the [DesignID] is odd number 1. So, IIf([1] Mod 2, “A”, “B”)==>IIf(TRUE, “A”, “B”)==>(1’s are TRUE in programming and 0’s are FALSE) and since the 2nd argument in an IIF formula is if the 1st argument is TRUE, the answer will be “A” for the odd number DesignIDs like 1 in this example.

[Note: Mod is used to divide two numbers and return only the remainder. So (an even number like) 8 Mod 2 is dividing 8 by 2 and there is no remainder so the result is 0. An odd number will always have a remainder, (ex:,) 7 Mod 2 will be 1….This takes advantage of the convention in programming that 0 = FALSE and [any number other than 0, though usually 1 or -1] = TRUE. Odd numbers will have a remainder of 1 - so this is evaluated as TRUE. So, the expression essentially says: IIf(TRUE,"Odd", "Even" ). Even numbers will have a remainder of 0 - so this is evaluated as FALSE. So, the expression essentially says: IIf(FALSE,"Odd", "Even" )]

From: www(dot)utteraccess.com/forum/odd-numbers-t1333872.html

Thank you, again, plog.
 

Simon_MT

Registered User.
Local time
Today, 10:39
Joined
Feb 26, 2007
Messages
2,177
Try using VBA is actually isn't that difficult 0nce you get the hang of it. I'm patently aware that you did not want VBA but it is worth perserving with.

I want to know whether or not the Producer Bonus has been created:

Code:
Private Function Schemes_BonusInvoiceCheck()
    On Error GoTo Err_Schemes_BonusInvoiceCheck

Dim dbBonusCheck As DAO.Database
Dim rsBonusCheck As DAO.Recordset
Dim Scheme As String

    With CodeContextObject
    
        Scheme = .[Pig Scheme Number]

        Set dbBonusCheck = CurrentDb
        Set rsBonusCheck = dbBonusCheck.OpenRecordset("SELECT DISTINCTROW PigSchemesInvoices.[Pig Scheme Number] FROM PigSchemesInvoices WHERE PigSchemesInvoices.[Scheme Invoice Type]='BNP' AND PigSchemesInvoices.[Pig Scheme Number] = '" & Scheme & "'")
        rsBonusCheck.MoveFirst

        Schemes_BonusInvoiceCheck = True

    End With

Exit_Schemes_BonusInvoiceCheck:

    If rsBonusCheck.RecordCount = 0 Then
       Schemes_BonusInvoiceCheck = False
    End If

    rsBonusCheck.Close
    Set rsBonusCheck = Nothing
    Set dbBonusCheck = Nothing
    Exit Function

Err_Schemes_BonusInvoiceCheck:
     Resume Exit_Schemes_BonusInvoiceCheck

End Function

All that is happening form the Form see if the SQL query returns a record then True else the SQL query will Error and if this Error caused by Zero Records then False. You can also interogate other values.

Simon
 

Apples241

Registered User.
Local time
Today, 03:39
Joined
Aug 29, 2016
Messages
54
Thank you, Simon_MT. I will try it out and see if I can use it and tailor it to what I am using it for. Yes, I wanted a formula, but I will try and see.

Thank you for taking the time to answer.

P.S. It might be the weekend before I can try it out, but I will get back to the forum with any feedback.
 

Simon_MT

Registered User.
Local time
Today, 10:39
Joined
Feb 26, 2007
Messages
2,177
Glad to help.

If you have a problem let us know.

Simon
 

Apples241

Registered User.
Local time
Today, 03:39
Joined
Aug 29, 2016
Messages
54
Simon_MT, I tried looking at the function but I can't translate it into something familiar to me. Thank you, again, for posting. I would have to compare it to another code I found on a website.

Thank you, again. I'm sure it would solve the problem.
 

Users who are viewing this thread

Top Bottom