Is there a way to convert a memo field to a text field in Access 2010 in an expression?

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?

1 Answer

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