Thread: Retrieve MEMO field in Excel

I'm running a Macro in Excel to retrieve various data fields from an Access db. One of the fields is a MEMO field and each time I add this field into the query, it causes an error.

Is there a way to retrieve this MEMO field?

Code:

Sub GetInvoiceData()
Dim fromDate As String
fromDate = Range("fromDate").Value
Dim toDate As String
toDate = Range("toDate").Value
If (fromDate = "" Or toDate = "") Then
MsgBox ("You need to enter a from and to date")
Exit Sub
End If
fromDate = Year(fromDate) & "/" & Month(fromDate) & "/" & Day(fromDate)
toDate = Year(toDate) & "/" & Month(toDate) & "/" & Day(toDate)
Dim strsql As String
strsql = "SELECT Orders.InvoiceDate, Orders.OrderID, Company.Institution, LEFT(Company.DeliveryAddress,2) AS Address, Company.VATNumber,"
strsql = strsql & " Products.`Pack 1 Cat Num` AS CatNumber , OrderLineItems.UnitPrice, OrderLineItems.Quantity,"
strsql = strsql & " (OrderLineItems.UnitPrice*OrderLineItems.Quantity) AS `Line Price`,"
strsql = strsql & " OrderLineItems.Currency, OrderLineItems.LineDiscount,"
strsql = strsql & " ((OrderLineItems.UnitPrice*OrderLineItems.Quantity)*((100-OrderLineItems.LineDiscount)/100)) AS NetPrice,"
strsql = strsql & " (Orders.VAT*100) AS VAT, (NetPrice*Orders.VAT) AS `VAT Amount`"
strsql = strsql & " FROM ((Orders"
strsql = strsql & " LEFT JOIN"
strsql = strsql & " OrderLineItems ON OrderLineItems.OrderID=Orders.OrderID)"
strsql = strsql & " LEFT JOIN Company ON Company.CompanyID=Orders.CompanyID)"
strsql = strsql & " LEFT JOIN Products ON Products.ProductID=OrderLineItems.ProductID"
strsql = strsql & " WHERE Orders.InvoiceDate BETWEEN #" & fromDate & "# AND #" & toDate & "#"
strsql = strsql & " UNION"
strsql = strsql & " SELECT Orders.InvoiceDate, Orders.OrderID, Company.Institution, Company.VATNumber,"
strsql = strsql & " 'Shipping', 0, 1, Orders.ShipCharge AS `Line Price`, Orders.Currency, 0, Orders.ShipCharge AS NetPrice,"
strsql = strsql & " (Orders.VAT*100) AS VAT, (NetPrice*Orders.VAT) AS `VAT Amount`"
strsql = strsql & " FROM (Orders"
strsql = strsql & " LEFT JOIN Company ON Company.CompanyID=Orders.CompanyID)"
strsql = strsql & " WHERE Orders.InvoiceDate BETWEEN #" & fromDate & "# AND #" & toDate & "#"
strsql = strsql & " ORDER BY Orders.InvoiceDate ASC, Orders.OrderID ASC"
'Range("D1").Value = strsql
'Exit Sub
Application.DisplayAlerts = False
ActiveWorkbook.Sheets("Results").Delete
Application.DisplayAlerts = True
ActiveWorkbook.Sheets.Add after:=Sheets("Main")
ActiveWorkbook.Sheets(2).Name = "Results"
    With ActiveSheet.QueryTables.Add(Connection:=Array( _
        "ODBC;DSN=***;Description=***;DATABASE=***;Trusted_Connection=YES"), Destination:=ActiveWorkbook.Sheets("Results").Range("A1"))
        .CommandText = strsql
        .Name = "Invoice Query from Ascent"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshStyle = xlInsertEntireRows
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery = False
    End With
End Sub

The field in red is the problem. I tried just retrieving a couple of characters to see if it was a carriage return causing the issue, but that didn't help either.

OK. I sorted it. I hadn't added the field into the UNION SQL query so the 2 queries had a different number of fields.

OK. Another slight issue. The MEMO field has carriage returns in it.

How can I alter my SQL query to replace these with a ", "?

I've tried:-

Code:

Replace(Company.DeliveryAddress,Chr(13) & Chr(10),"", "")

and

Code:

Replace(Company.DeliveryAddress,Chr(13) & Chr(10),', ')

but no luck.