Building SQL in Visual Basic

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

    Building SQL in Visual Basic

    Hello,

    I am trying to build an SQL in Visual Basic and need some help with
    quotation mark(s).

    I have two text fields on a form where the user enters a sales group
    range to view records between those sales groups. For example:
    Salesgroup 1 through Salesgroup 10. I have written the following piece
    of code to get the records between me.salesgroup1 and me.salesgroup2 but
    it gives me a compile error and says expected end of statement.

    strWhere = strWhere & "([SGRP] = " BETWEEN """" & Me.SalesGroup1 & "'
    AND '" Me.SalesGroup2 "') And "

    Does anyone see what it is that is wrong with the above statement?

    Thanks in advance for any help.



    *** Sent via Developersdex http://www.developersdex.com ***
    Tags: None
    • Bruce
      #2
      Re: Building SQL in Visual Basic

      On Feb 28, 10:17 am, Faraz Meghani <farazal...@yah oo.comwrote:
      Hello,
      >
      I am trying to build an SQL in Visual Basic and need some help with
      quotation mark(s).
      >
      I have two text fields on a form where the user enters a sales group
      range to view records between those sales groups. For example:
      Salesgroup 1 through Salesgroup 10. I have written the following piece
      of code to get the records between me.salesgroup1 and me.salesgroup2 but
      it gives me a compile error and says expected end of statement.
      >
      strWhere = strWhere & "([SGRP] = " BETWEEN """" & Me.SalesGroup1 & "'
      AND '" Me.SalesGroup2 "') And "
      >
      Does anyone see what it is that is wrong with the above statement?
      >
      Thanks in advance for any help.
      >
      *** Sent via Developersdexht tp://www.developersd ex.com***
      If SalesGroup1 and SalesGroup2 are string values then I think you want
      the following:

      strWhere = strWhere & "([SGRP] BETWEEN '" & Me.SalesGroup1 & "' AND
      '" & Me.SalesGroup2 & "') And "

      If SalesGroup1 and SalesGroup2 are numeric values then simply remove
      the single quotes.

      Bruce

        Comment

        • Larry Linson
          #3
          Re: Building SQL in Visual Basic

          You do understand that, due to the sort characteristics of alphanumeric
          data, if your are using the text "Salesgroup 1", "Salesgroup 2", ...
          "Salesgroup 10", that Salesgroups 2, 3, 4, 5, 6, 7, and 8 will not be
          returned by a WHERE clause of

          BETWEEN "Salesgroup 1" and "Salesgroup 10"

          That is because the sorted values, in Ascending order, are:

          Salesgroup 1
          Salesgroup 10
          Salesgroup 2
          Salesgroup 3
          Salesgroup 4
          .. . .

          This might be contributing to the problem that you are experiencing. That
          is, even if you get the quotes corrected on the "BETWEEN", you may not get
          the results you expect.

          Larry Linson
          Microsoft Access MVP



            Comment

            Previous template Next
            Working...