1

Good morning! I am seeking guidance on an issue I have been stuck on since last week, but hopefully there is an easy solution. As you know, you cannot directly link/join memo fields in MS Access. I created a query last week to return rows where a memo field in one table contained the text field from another table via the Where clause "[memo] LIKE '\*[text]\*'" and this worked out perfectly.

However, now I would like to find out the memo values from the table NOT present in the query. I was hoping it would be simple to do with a "Not in" clause, but this does not seem to be the case.

Is there another method to do this? Is there a way to perhaps convert the data type in a SQL query? Or is the only way to do this type of query in VBA?

Thank you in advance! I can provide more info upon request, but I did not feel the field/table names would be of any use.

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • Can you show us your query please :) ? – bonCodigo Jan 28 '13 at 16:21
  • 2
    Hopefully the result set from your first query includes the primary key values for the matching rows. If so, you can tackle the "unmatched" memo field values as those whose primary keys are not present in the first query's result set. If the table does not include a primary key, seriously consider adding one. – HansUp Jan 28 '13 at 16:27
  • @HansUp - Ah, genius! So simple - much better idea! I will try that now. I was focusing on the memo field too much since that is of course the area of interest and used to drive something else; well done, sir! –  Jan 28 '13 at 16:33
  • 1
    otherwise you can always use `mymemo NOT LIKE "somestring"` – iDevlop Jan 28 '13 at 16:38

1 Answers1

1

Cheers to @HansUp! I added the original primary key to the initial query and just compared those as opposed to trying to compare the memo fields; a much simpler solution! I might make adding the primary key a subquery as to keep the original query only contain fields of interest, but at least it works accurately! Cheers all! I love this community.