Syntax error (missing operator) in query expression

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • jodyblau
    #1

    Syntax error (missing operator) in query expression

    I'm new at this stuff, so bare with me.

    I have a main form which displays a client record.
    The main form has 2 subforms. The first subform has a list of Cases.
    The Second subform is a list of Tasks.

    My intent is this: When I click on a button next to one of the Case
    records (in the first subform), I want the list of Tasks (the second
    subform) to display only those records (tasks) associated with the Case
    which was just selected.

    Here is what I have attempted:

    Private Sub View_Tasks_Clic k()
    On Error GoTo Err_View_Tasks_ Click

    GBL_String = Case_or_Issue

    strSQL = "Select * from Task_List where"
    strSQL = strSQL & " " & Case_or_Issue & " =" & GBL_String
    Forms!Case_List _By_Clients!Tas k_List.Form.Rec ordSource = strSQL

    Exit_View_Tasks _Click:
    Exit Sub

    Err_View_Tasks_ Click:
    MsgBox Err.Description
    Resume Exit_View_Tasks _Click

    End Sub

    Note: The Case_or_Issue that is used to assign a value to GBL_String
    identifies the record selected from the first subform. My Task_List
    (second subform) also has a field called Case_or_Issue. So I am trying
    to compare the two strings and filter the form accordingly.

    When I try this, I get "Syntax error (missing operator) in query
    expression"
    While trouble shooting, I removed the second part of strSQL, and
    removed "where" from the first part, so as to just select all records.
    This produces no errors.

    Can someone help me understand how to solve this error?


    Thank You,

    Jody Blau

    Tags: None
    • MGFoster
      #2
      Re: Syntax error (missing operator) in query expression

      If the Tasks is a datasheet form, why don't you make the Tasks a subform
      of the Cases subform? Then everytime you move to a new case the tasks
      subform will automatically display the appropriate Tasks.

      See the Help articles on Master and Child link fields.
      --
      MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
      Oakland, CA (USA)

      jodyblau wrote:[color=blue]
      > I'm new at this stuff, so bare with me.
      >
      > I have a main form which displays a client record.
      > The main form has 2 subforms. The first subform has a list of Cases.
      > The Second subform is a list of Tasks.
      >
      > My intent is this: When I click on a button next to one of the Case
      > records (in the first subform), I want the list of Tasks (the second
      > subform) to display only those records (tasks) associated with the Case
      > which was just selected.
      >
      > Here is what I have attempted:
      >
      > Private Sub View_Tasks_Clic k()
      > On Error GoTo Err_View_Tasks_ Click
      >
      > GBL_String = Case_or_Issue
      >
      > strSQL = "Select * from Task_List where"
      > strSQL = strSQL & " " & Case_or_Issue & " =" & GBL_String
      > Forms!Case_List _By_Clients!Tas k_List.Form.Rec ordSource = strSQL
      >
      > Exit_View_Tasks _Click:
      > Exit Sub
      >
      > Err_View_Tasks_ Click:
      > MsgBox Err.Description
      > Resume Exit_View_Tasks _Click
      >
      > End Sub
      >
      > Note: The Case_or_Issue that is used to assign a value to GBL_String
      > identifies the record selected from the first subform. My Task_List
      > (second subform) also has a field called Case_or_Issue. So I am trying
      > to compare the two strings and filter the form accordingly.
      >
      > When I try this, I get "Syntax error (missing operator) in query
      > expression"
      > While trouble shooting, I removed the second part of strSQL, and
      > removed "where" from the first part, so as to just select all records.
      > This produces no errors.
      >
      > Can someone help me understand how to solve this error?[/color]

        Comment

        • Lyle Fairfield
          #3
          Re: Syntax error (missing operator) in query expression

          Air Script (assumes GBL_String's type is String)

          strSQL = "SELECT * FROM Task_List"
          strSQL = strSQL & vbNewLine & "WHERE Case_or_Issue = '" & GBL_String
          & "'"

          '" is one of these ' and one of these "

          "'" is one of these ", then one of these ', and another one of these"

            Comment

            • Bob Quintal
              #4
              Re: Syntax error (missing operator) in query expression

              "jodyblau" <jodyblau@gmail .com> wrote in
              news:1138482644 .595700.9610@g4 9g2000cwa.googl egroups.com:
              [color=blue]
              > I'm new at this stuff, so bare with me.
              >[/color]
              I don't bare with anybody unless they are really cute. I will
              bear with you, as in help bear your problem on my shoulders.
              [color=blue]
              > I have a main form which displays a client record.
              > The main form has 2 subforms. The first subform has a list of
              > Cases. The Second subform is a list of Tasks.
              >
              > My intent is this: When I click on a button next to one of
              > the Case records (in the first subform), I want the list of
              > Tasks (the second subform) to display only those records
              > (tasks) associated with the Case which was just selected.
              >
              > Here is what I have attempted:
              >
              > GBL_String = Case_or_Issue
              > strSQL = "Select * from Task_List where"
              > strSQL = strSQL & " " & Case_or_Issue & " =" & GBL_String
              > Forms!Case_List _By_Clients!Tas k_List.Form.Rec ordSource =
              > strSQL
              > Can someone help me understand how to solve this error?[/color]

              Case_or_issue is a field in the table Task_List?
              It miust be put into the SQL as a literal.

              strSQL = strSQL & " Case_or_Issue = " & GBL_String

              GBL_String is a string, right? The3 above would work if it's a
              number, but it must be surrounded by quotes in the SQL. You
              insert quotes in a variable by putting two quotes side by side.:

              strSQL = strSQL & " Case_or_Issue =""" & GBL_String & """"

              --
              Bob Quintal

              PA is y I've altered my email address.

                Comment

                • Bob Quintal
                  #5
                  Re: Syntax error (missing operator) in query expression

                  "Lyle Fairfield" <lylefairfield@ aim.com> wrote in
                  news:1138492644 .852615.7390@g4 7g2000cwa.googl egroups.com:
                  [color=blue]
                  > Air Script (assumes GBL_String's type is String)
                  >
                  > strSQL = "SELECT * FROM Task_List"
                  > strSQL = strSQL & vbNewLine & "WHERE Case_or_Issue = '" &
                  > GBL_String & "'"
                  >
                  > '" is one of these ' and one of these "
                  >
                  > "'" is one of these ", then one of these ', and another one of
                  > these"
                  >[/color]
                  Interesting use of vbNewLine where a space would work. I could see3
                  a benefit for readability if saving the string to a querydef, but
                  what advantage would it offer in this scenario?

                  --
                  Bob Quintal

                  PA is y I've altered my email address.

                    Comment

                    • jodyblau
                      #6
                      Re: Syntax error (missing operator) in query expression

                      I have it in a continous form, rather than a datasheet, so it won't let
                      me do the subform in the subform.

                        Comment

                        • jodyblau
                          #7
                          Re: Syntax error (missing operator) in query expression

                          Thank you.

                            Comment

                            • Bob Quintal
                              #8
                              Re: Syntax error (missing operator) in query expression

                              "jodyblau" <jodyblau@gmail .com> wrote in
                              news:1138511644 .962961.322250@ g44g2000cwa.goo glegroups.com:
                              [color=blue]
                              > I have it in a continous form, rather than a datasheet, so it
                              > won't let me do the subform in the subform.
                              >[/color]
                              A technique I've used successfully is to put a hidden textbox on
                              the mainform that contains the primary key for subform_1. the
                              control is unbound, but gets updated in the Current event of
                              subform_1. Subform_2's link parent fields property is set to thae
                              textbox. Just clicking on a record in subform_1 resynchronises the
                              subform_2.



                              --
                              Bob Quintal

                              PA is y I've altered my email address.

                                Comment

                                Previous template Next
                                Working...