Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL error (Access 2000)

    I have a valid query, but trying to write it down as an SQL i receive red letters,pointing that something is not
    in order with my sql. Especially it points for error in format function, that is the "mmmm" perhaps
    What is the cause of this error and can i write down a proper SQL clause ?

    My code is the following :

    Dim strSQL As String
    strSQL = "SELECT Format([InvoiceDate],"mmmm") AS MonthName, DatePart("m",[invoicedate]) AS MonthNumber,

    qrySales.CompanyName, Sum(qrySales.liters) AS SumOfliters1, qrySales.afid, qrySales.Customerid " & _
    " FROM qrySales " & _
    " GROUP BY Format([InvoiceDate],"mmmm"), DatePart("m",[invoicedate]), qrySales.CompanyName, qrySales.afid, qrySales.Customerid

    " & _
    " ORDER BY DatePart("m",[invoicedate]), qrySales.CompanyName;

  2. #2
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL error (Access 2000)

    I used single quotes instead and it worked for me: Format([InvoiceDate],'mmmm')

    hth,
    Jack

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: SQL error (Access 2000)

    Hi,
    Jack is right - because you're building the string in code, you get problems if you need quotes to be contained within your actual string. The compiler will treat them as closing the previous quotes and then opening a new set of quotes. I would use:
    Dim strSQL As String
    strSQL = "SELECT Format([InvoiceDate],'mmmm') AS MonthName, DatePart('m',[invoicedate]) AS MonthNumber,

    qrySales.CompanyName, Sum(qrySales.liters) AS SumOfliters1, qrySales.afid, qrySales.Customerid " & _
    " FROM qrySales " & _
    " GROUP BY Format([InvoiceDate],'mmmm'), DatePart('m',[invoicedate]), qrySales.CompanyName, qrySales.afid, qrySales.Customerid " & _
    " ORDER BY DatePart('m',[invoicedate]), qrySales.CompanyName;"
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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