2

I have two tables (each in an Access DB) -- called LinkedTable1 and LinkedTable2 linked to my working Access database. I got them on my computer after downloading them from a SharePoint site (Actions -> Open with Access).

In each of LinkedTable1 and LinkedTable2 there's a Memo field that contains a GUID. This GUID links the two tables.

I'd like to use these GUIDs in a query, but when I try to JOIN them within Query Design, I get told that I cannot join on Memo fields.

I tried creating an expression to convert the field to a Text field:

GUID_Text: CStr( Left$( [LinkedTable1]![GUID_Memo], 255 ) )

I also tried converting the field to a GUID:

GUID: GUIDFromString( [LinkedTable1]![GUID_Memo] )

I did this on each of the linked tables. Both time, when I tried to execute the query, I got a type mismatch error.

Any ways around this?

Oliver Salzburg
  • 89,072
  • 65
  • 269
  • 311
JW.
  • 557

1 Answers1

1

I was able to perform the JOIN by creating a saved query for each table to convert the Memo field to Text, and then JOIN the queries. For test data in [Table1]

ID  GUID_Memo                               Description
--  --------------------------------------  -----------
1   {453EE643-BFE5-4D8B-B9F1-2B66C71E609A}  Table1: foo
2   {80BC2B94-7AC2-4956-959B-E8B624F06502}  Table1: bar

and [Table2]

ID  GUID_Memo                               Description
--  --------------------------------------  -----------
6   {453EE643-BFE5-4D8B-B9F1-2B66C71E609A}  Table2: foo
7   {80BC2B94-7AC2-4956-959B-E8B624F06502}  Table2: bar

where [GUID_Memo] are true Memo fields, the saved queries [Query1]

SELECT Left([GUID_Memo],100) AS GUID_Text, Table1.Description
FROM Table1;

and [Query2]

SELECT Left([GUID_Memo],100) AS GUID_Text, Table2.Description
FROM Table2;

when joined together

SELECT 
    Query1.GUID_Text, 
    Query1.Description AS Description1, 
    Query2.Description AS Description2
FROM 
    Query1 
    INNER JOIN 
    Query2 
        ON Query1.GUID_Text = Query2.GUID_Text;

results in

GUID_Text                               Description1  Description2
--------------------------------------  ------------  ------------
{453EE643-BFE5-4D8B-B9F1-2B66C71E609A}  Table1: foo   Table2: foo
{80BC2B94-7AC2-4956-959B-E8B624F06502}  Table1: bar   Table2: bar
Gord Thompson
  • 291