Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    18

    Angry Unanswered: Parameters from SQL to VBA

    Hi everybody,

    I'm trying to code some parameters in VBA. I have them though in my queries coded in SQL working as pop ups.

    I was sucessuful coding the Date parameter from SQL to VBA but I was not unfortunately with Text and Number parameters. Here is what I have done with the Date:

    WHERE Product.Date = [Date:]

    in VBA it's working for me like this:

    sWHERE = sWHERE & " AND Product.Date = " & _
    "#" & Format$(txtDate, "mm/dd/yyyy") & "#"

    Is there a way to do the same thing with these from SQL to VBA:

    - WHERE Product.Category Like "*" & [Search by any Category: ] & "*"

    - WHERE Product.Amount Between [Amount > X] And [Amount < Y]

  2. #2
    Join Date
    Aug 2003
    Location
    Bosnia & Hercegovina
    Posts
    57
    of course you can....

    sqlstr = sqlstr & "WHERE Product.Category Like '*" & [Search by any Category: ] & "*'"

    sqlstr = sqlstr & "WHERE Product.Amount > " & [X] & " And Product.Amount < " & [Y]

    sqlstr holds your initial sql select/from without where conditions...

    or you can just paste where part....

    note single quotes when dealing with strings........

  3. #3
    Join Date
    Jul 2003
    Posts
    18
    I'm sorry for not being clear on my explanations. In fact I'm using text boxes in the form where the user would enter his values. Here is my code, may be it's more clear

    Function BuildSQLString(sSQL As String) As Boolean

    Dim sSELECT As String
    Dim sFROM As String
    Dim sWHERE As String

    sSELECT = "s.ProductID, i.Amount "

    sFROM = "Product s INNER JOIN Sales i " & _
    "ON s.ProductID = i.ProductID "

    If chkDate Then

    sWHERE = sWHERE & " AND i.Date = " & _
    "#" & Format$(txtDate, "mm/dd/yyyy") & "#"
    End If


    sSQL = "SELECT " & sSELECT
    sSQL = sSQL & "FROM " & sFROM
    If sWHERE <> "" Then sSQL = sSQL & "WHERE " & Mid$(sWHERE, 6)

    BuildSQLString = True

    End Function


    I would like to do the same thing with:

    If chkCountry Then

    and

    If chkAmount Then

    and for both of them I have text Boxes in my form (txtCountry, txtAmount1 & txtAmount2 ) where I enter my values, As I have it above on txtDate.

    My SQL Statements are:

    - WHERE Product.Category Like "*" & [Search by any Category: ] & "*"

    - WHERE Product.Amount Between [Amount > X] And [Amount < Y]

Posting Permissions

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