Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax error in From Clause (Access 2000)

    I need some help in finding out the Syntax error in the Fromclause when i tried to replace the where clause for the option group Ad in the OnOpen event of the report.:
    Private Sub Report_Open(Cancel As Integer)
    Dim bas As String

    Select Case Forms!Main!Ads
    Dim StrWhere As String
    Case 1
    StrWhere = " WHERE (((Customers.trolley) = True)"
    Case 2
    StrWhere = " WHERE (((Customers.signs) = True)"
    End Select


    bas = " SELECT Customers.Customerid, Customers.CompanyName, Customers.address, Customers.city" & _
    " FROM Customers " & _
    " strWhere And ((Customers.afid) = Forms!Main!Office))" & _
    "ORDER BY Customers.CompanyName"

    Me.RecordSource = bas

    End Sub

    Do i replace the where statements in the wrong way ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Syntax error in From Clause (Access 2000)

    You have placed strWhere within the quotes. As a result, the word strWhere will be included in your SQL string instead of the value of the variable strWhere. Try

    bas = " SELECT Customers.Customerid, Customers.CompanyName, Customers.address, Customers.city" & _
    " FROM Customers " & _
    strWhere & " And ((Customers.afid) = Forms!Main!Office))" & _
    " ORDER BY Customers.CompanyName"

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error in From Clause (Access 2000)

    Yes, it works great ! Thank you ! I can choose now between the two option boxes and everything is OK.
    I wanted to refine my Select case statement and put in the Case Else the condition when the second option box is not chosen but only the first.I tried as:
    Case Else
    StrWhere = ""
    But again i got the same error.Is it possible to place in the Select Case statement the condition when the second option group is not chosen?
    And also, how could i build the statement when both option boxes, i.e. ofices and ads are not chosen.it may be also interesting to have this opportunity to show all the cases

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Syntax error in From Clause (Access 2000)

    There is no afid field in your Customers table, so the code will not work.

    In the attached database, I used a different approach: instead of the On Open event of the report, I used the On Click event of the command button on the form. Just to make it work, I added an afid field to the table. The record source of the report is now simply the table.

    This is the code:

    Private Sub Command98_Click()
    Dim strWhere As String

    On Error GoTo Err_Command98_Click

    Select Case Me.Ads
    Case 1
    strWhere = " AND trolley = True"
    Case 2
    strWhere = " AND signs = True"
    End Select

    If Not IsNull(Me.Office) Then
    strWhere = strWhere & " AND afid = " & Me.Office
    End If

    If Not strWhere = "" Then
    ' Remove first " AND "
    strWhere = Mid(strWhere, 6)
    End If

    DoCmd.OpenReport "q1", acViewPreview, , strWhere

    Exit Sub

    Err_Command98_Click:
    If Not Err = 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error in From Clause (Access 2000)

    Fantastic ! Your suggestion is simply marvelous.The code is so robust now that i simply have no further comments. Thank you to you and your great site.
    have a nice weekend !!!!!!

  6. #6
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error in From Clause (Access 2000)

    Dear Hans,

    I tried to apply your code to open another report with the same option box as follows :
    Dim strWhere As String

    If Not IsNull(Me.Office) Then
    strWhere = strWhere & " AND afid = " & Me.Office
    End If
    DoCmd.OpenReport "rptContracts", acViewPreview, , strWhere
    However, i got the error Symtax error.Mising operator, even though i have afid placed in the report.What may be wrong ?

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Syntax error in From Clause (Access 2000)

    In this case it should be just

    If Not IsNull(Me.Office) Then
    strWhere = "afid = " & Me.Office
    End If

    In the previous case you already had some stuff in strWhere and you were adding to it.

    In this you are not adding to strWhere you are just giving it a value.
    Regards
    John



  8. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax error in From Clause (Access 2000)

    Thank you, John ! It works great now !

Posting Permissions

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