Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Thanked 0 Times in 0 Posts

    Cannot convert a yery into string (Access 2000)

    Cannot convert a query into string

    i want to convert a working query into a string since i need it for further calculations. However i cannot obtain the string,the string shown below is with red letters showing that something is not in order.
    I discovered that if i do not include the format statement, than the sql clause is o.k. but the sql does not want to accept the format i want to give.On the other hand, my yery is all right.The first column
    of my query is with the format functuon i.e.

    Below is the converted string from the query,which fails to execute.

    Dim strBas As String
    strBas = " SELECT DISTINCTROW Format([invoicedate],"yy-mm") AS [Month],

    [Order Details].OrderID, Sum([Order Details].liters) AS Liters FROM

    Products INNER JOIN ((customers INNER JOIN orders ON

    (customers.Customerid = orders.customerid) AND (customers.Customerid =

    orders.customerid)) INNER JOIN [Order Details] ON orders.orderid =

    [Order Details].OrderID) ON Products.Productid = [Order

    Details].ProductID GROUP BY Format([invoicedate],"yy-mm"), [Order

    Details].OrderID ORDER BY [Order Details].OrderID "

    All i want is to have a working strBas.
    Can somebody help me ?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Crystal Beach, FL, Florida, USA
    Thanked 33 Times in 33 Posts

    Re: Cannot convert a yery into string (Access 2000)

    >>strBas = " SELECT DISTINCTROW Format([invoicedate],"yy-mm")..."

    Your problem is the use of double-quotes within double-quotes. Access is seeing the start of the inner double-quote as the end of the first double-quote. Try using an apostrophe, as in 'yy-mm'.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts