Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    San Diego
    Posts
    45

    Unanswered: using <> and >= within a combo box for a condition in a SQL statement

    My subject pretty much describes my problem. Within my combo box, I have two conditions which the user can choose from; <>100 and >=0. The problem is that they are being interpreted as strings/text, and I need them to be comparing the data and filtering based on the comparison. Any suggestion on how I can do this?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Combo boxes can't store functions as such for their value. You'll have to create a sub or macro that will evaluate the string in the combo box and perform the relevant calculation.

    Eg:

    Code:
    Private Sub AfterUpdate
    If cmbCombo = "<> 100" Then
       rs.Filter = "field <> 100"
    Else
       rs.Filter = "field >= 0
    End If

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    LarelLee,

    You are going to have to better describe your issue.

    Where are you putting this <>100 and >=0 ?

    You mention a SQL statement but <>100 and >=0 are not SQL statements.

    Show us everything so we don't have to guess at what you are trying to do.

  4. #4
    Join Date
    Mar 2004
    Location
    San Diego
    Posts
    45
    I got it working. I had to use some code in order for the <> or >= to be interpreted as an expression instead of a string. Here is the code I used:

    Code:
    Private Sub cbxPCT_C_AfterUpdate()
    
    Dim db As DAO.Database
    
    Set db = CurrentDb
    db.QueryDefs("qryHolds_With_Filter").SQL = Mid$(db.QueryDefs("qryHolds_With_Filter").SQL, 1, InStr(1, db.QueryDefs("qryHolds_With_Filter").SQL, "WHERE") + 5) & " [PCT_C] " & FORMS![frmSort1]![cbxPCT_C] & ";"
    db.Close
    DoCmd.OpenQuery "qryHolds_With_Filter"
    
    End Sub
    It finally works!!!

Posting Permissions

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