Populating unmatched rows using function

[Datafiend]

I have a number of names and Ids in a table (TblTransactions) and a subset of these in another table (TblSubset). The values in tblSubset are unique. I’ve written a function for a field in a query that I want to populate for each row from tblTransactions with a ‘Yes’ if the name from tblTransactions also occurs in tblSubset and a ‘No’ if it doesn’t. There is a one to many join between the two tables. The code I am using is below.

Public Function FindMatch(Id1 As String, Id2 As String) As String
          If Id1 = Id2 Then
              FindMatch = "Yes"
          Else: FindMatch = "No"
          End If
   
  End Function

When I run the query I get a message ‘Data type mismatch in criteria expression’.

If I replace the one-to many join by a one to one join the function works but then I only get the rows common to both tables instead of all the rows in TblTransactions.

If I set up the query, however, with an IIF statement, as below, it works fine.

Output1: IIf([Id1]=[Id2],"Yes","No"

)

Can anyone tell me how I should be writing the function?


Thanks in advance for any help you can offer.

 

[Isskint]

I would suggest using the DCOUNT function for this:

(TblTransactions) and a subset of these in another table (TblSubset).

=Iif(Dcount("[Name]","[TblSubset]","[ID]=" & me.ID)=0,"No","Yes")

 

[MarkK]

It seems a very unusual approach, in a database system, to have a subset of one table's data in a separate table. Maybe you have a design problem that, if solved, would eliminate the need to do this cross-table matching.

 

[Datafiend]

Hmmm Lagbolt. that's worth thinking about. My reason for this is because I am only linked to tbl Ttransactions as it belongs to another section and I can't change it, but maybe I can run a from it and add a field, populate that field and use that.

Thanks to both of you for your suggestions.

Isskint, I tried to implement your suggestion but couldn't quite work out what field names to put where.

Your time is much appreciated.

datafiend