Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    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.

    Format([invoicedate],"yy-mm"
    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
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 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
  •