Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax error in code (Access 2000)

    I have a valid and functioning code that is used for a record source in the form like that:
    Dim StrBasValid As String


    StrBasValid = "SELECT DISTINCTROW Customers.Customerid, Customers.CompanyName, Sum([order details].liters) AS SumOfliters, Customers.kindid " & _
    " FROM Customers RIGHT JOIN (Orders INNER JOIN [order details] ON Orders.orderid = [order details].OrderID) ON Customers.Customerid = Orders.customerid

    " & _
    "WHERE (((Year([InvoiceDate])) = " & CnstYear & ") And ((Orders.paymentid) = True) And ((Customers.afid) = 1)) " & _
    " GROUP BY Customers.Customerid, Customers.CompanyName, Customers.kindid;"
    Me.RecordSource = StrBasValid

    =============================

    I want to replace a part of the above code with another function called GetOffice like that:

    Dim StrBas As String

    StrBas = "SELECT DISTINCTROW Customers.Customerid, Customers.CompanyName, Sum([order details].liters) AS SumOfliters, Customers.kindid " & _
    " FROM Customers RIGHT JOIN (Orders INNER JOIN [order details] ON Orders.orderid = [order details].OrderID) ON Customers.Customerid = Orders.customerid

    " & _
    "WHERE (((Year([InvoiceDate])) = " & CnstYear & ") And ((Orders.paymentid) = True) & GetOffice " & _
    " GROUP BY Customers.Customerid, Customers.CompanyName, Customers.kindid;"

    Me.RecordSource = StrBas


    However i receive the message of a syntax error . Actualy i wanted to replace And ((Customers.afid) = 1)) with the function GetOffice.

    Public Function GetOffice()
    ' in the OnOpen event write Me.RecordSource = strBas & GetSize

    Select Case Forms![FBenchmark]![Office] ' This is an option button
    Case 1
    GetOffice = "AND ((customers.afid)=1))" ' London
    Case 2
    GetOffice = "AND ((customers.afid)=2))" ' Milan
    Case 3
    GetOffice = "AND ((customers.afid)=3))" ' Berlin
    Case 4
    GetOffice = "AND ((customers.afid)=4))" ' Paris

    etc
    etc
    End Select
    End Function

    Can somebody show me where i am wrong ?

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error in code (Access 2000)

    Hi Pilgrim,

    I think I see your problem. You're not closing your string properly... this line is the problem:

    "WHERE (((Year([InvoiceDate])) = " & CnstYear & ") And ((Orders.paymentid) = True) & GetOffice " & _

    Change it to (move the ending double-quote to immediately after the closing paren):

    "WHERE (((Year([InvoiceDate])) = " & CnstYear & ") And ((Orders.paymentid) = True)<font color=red>"</font color=red> & GetOffice & _


    Hope this helps! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Syntax error in code (Access 2000)

    Actually, there's going to be a missing space that will cause a syntax error. Try this instead:

    Change it to (move the ending double-quote to immediately after the closing paren):

    <pre>"WHERE (((Year([InvoiceDate])) = " & CnstYear & ") And ((Orders.paymentid) = True) " & GetOffice & _</pre>

    Charlotte

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Syntax error in code (Access 2000)

    I have noticed that there is a one for one with Customers.afid and Forms![FBenchmark]![Office], so why not replace:
    And ((Customers.afid) = 1))

    with:
    And ((Customers.afid) = Forms![FBenchmark]![Office]))

    That way you won't need a function at all.

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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