Match field names in a query

[honeypot3]

Is it possible to set up an if statement in a query to determine whether the contents of a field in one table matches the field name of another. For example, if I have a record in a field called Resource which is Site manager and I want to see if it matches to a field name in another table called Site Manager so I can then proceed with a course of action if there is a match.

For example, iif([Resource]=Site manager field name in another table, "match", "no match")

 

[Golom]

That's not very clear but try something like

(Select R.[Site Manager], "Match" As FieldName
From Resource R INNER JOIN [OtherTable] T
     ON R.[Site Manager] = T.[Site Manager])

UNION

(Select R.[Site Manager], "No Match" As FieldName
From Resource R LEFT JOIN [OtherTable] T
     ON R.[Site Manager] = T.[Site Manager]
WHERE T.[Site Manager] IS NULL)

 

[PaulF]

if the table name will be the same throughout the query then you can build a function to check the field names against such as this

Function fIsMatch(strVal As String, strTableName As String) As Boolean
If Len(Nz(strVal, "")) = 0 Or Len(Nz(strTableName, "")) = 0 Then
MsgBox "Missing Data"
Else
Dim db As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
Set db = CurrentDb
Set tdf = db.TableDefs(strTableName)
For Each fld In tdf.Fields
If fld.Name = strVal Then
fIsMatch = True
GoTo errExit
End If
Next fld
End If
fIsMatch = False
errExit:
Set db = Nothing
Set tdf = Nothing
Set fld = Nothing
Exit Function
errHandler:
MsgBox Err.Number & " : " & Err.Description, vbCritical, "Error In Matching Function"
Resume errExit
End Function

Then you just have to add an Expression Field to your query that passes the value of the field and the table name to the function. Using your example it would be

IsMatch: IIf(fIsMatch([Resource Manager],"[Site Manager]")=True,"Match","No Match")

BTW... it is not nice to have spaces in table names.. it can cause too many headaches.

PaulF