Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    22

    Unhappy Unanswered: Problems with SQL statement and a program

    Hi, I have a question on filter an subform due choice of checkbox and combobox, in the code below I can choice a range of data due a combobox and then filter 1 string due a check box, BUT I can't choice a range of data and filter also by 2 strings (2 check box), and I don't know why because the SQL seems to be fine, the result of this programs is that there filter when I choice the range of dates and select 1 checkbox (from 3) but the results does'nt appear (seems nothing) when I select two or more checkbox.

    here's my code
    Code:
    Option Explicit
    
    Private Sub cmdFiltro_Click()
    
    Dim strSQL As String
    Dim blWhereIsUsed As Boolean
    
    strSQL = "SELECT * FROM tblTarifas"
    blWhereIsUsed = False
    
    If Not Nz(Me.cboFecha, "") = "" Then
      If Me.cboFecha > Me.cboFecha2 Then
        MsgBox ("Favor de poner correctamente las fechas")
      Else
        strSQL = strSQL & " WHERE tblTarifas.fecha BETWEEN #" & Me.cboFecha & " # AND # " & Me.cboFecha2 & " # "
        blWhereIsUsed = True
      End If
    End If
    
    If Me.Check24.Value = True Then
      If Not blWhereIsUsed Then
        strSQL = strSQL & "WHERE tblTarifas.Zona = '" & Me.Label25.Caption & "'"
        blWhereIsUsed = True
      Else
        strSQL = strSQL & "AND tblTarifas.Zona = '" & Me.Label25.Caption & "'"
        strSQL = strSQL & "AND tblTarifas.Zona = '" & Me.Label25.Caption & "'"
      End If
    End If
    
    If Me.Check27.Value = True Then
      If Not blWhereIsUsed Then
        strSQL = strSQL & "WHERE tblTarifas.Zona = '" & Me.Label28.Caption & "'"
        blWhereIsUsed = True
      Else
        strSQL = strSQL & "AND tblTarifas.Zona = '" & Me.Label28.Caption & "'"
        'strSQL = strSQL & "UNION WHERE tblTarifas.Zona = '" & Me.Label28.Caption & "'"
      End If
    End If
     
    If Me.Check29.Value = True Then
      If Not blWhereIsUsed Then
        strSQL = strSQL & "WHERE tblTarifas.Zona = '" & Me.Label30.Caption & "'"
        blWhereIsUsed = True
      Else
        strSQL = strSQL & "AND tblTarifas.Zona = '" & Me.Label30.Caption & "'"
      End If
    End If
    
    Debug.Print strSQL
    
    Me.sfmTarifas.Form.RecordSource = strSQL
    Me.sfmTarifas.Form.Requery
    
    End Sub
    And here's the SQL
    Code:
    SELECT * FROM tblTarifas WHERE tblTarifas.fecha BETWEEN #01/01/1997 # AND # 01/02/1998 # AND tblTarifas.Zona = 'Baja California'AND tblTarifas.Zona = 'Baja California Sur'
    Where you can see the Dates and also the two strings (Baja California and Baja California Sur) due to a checkbox,

    Any comments I'll be aprreciated it

    David García

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you got it right here
    MsgBox ("Favor de poner correctamente las fechas")
    Else
    strSQL = strSQL
    & " WHERE tblTarifas.fecha BETWEEN #" & Me.cboFecha & " # AND # " & Me.cboFecha2 & " # "
    blWhereIsUsed = True


    but you forgot the
    & "SPACEWHERE
    and
    & "SPACEAND
    in the other lines.

    ???

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Aug 2004
    Posts
    22

    Unhappy I try, but doesn't work

    Thnks izyrider, I did what you saying, but the results its the same nothing, but I appreciate your time, thanks a lot

    David Garcia

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    can you post the revised code?

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and you also had a missing
    & "spaceUNION

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Aug 2004
    Posts
    22

    Unhappy

    Hi, I solve the first problem but one remains, the program can filter by zone, but only can filter by range of data when the years change, if year not change they do nothing, only show the firs record of that year, I see that is not a problem concerning de Us Date and Mexican date Standarts (MM-DD-YY) and (DD-MM-YY) Respectibly

    here's my code:

    Code:
    Option Compare Text
    Option Explicit
    
    Private Sub cmdFiltro_Click()
    
    Dim strSQL As String
    Dim blWhereIsUsed As Boolean
    blWhereIsUsed = False
    
    strSQL = " SELECT * FROM tblTarifas"
    
    If Not Nz(Me.cboFecha, "") = "" Then
      If Me.cboFecha > Me.cboFecha2 Then
        MsgBox ("Favor de poner correctamente las fechas")
      Else
        strSQL = strSQL & " WHERE tblTarifas.fecha BETWEEN #" & Me.cboFecha & " # AND # " & Me.cboFecha2 & " # "
        blWhereIsUsed = True
      End If
    End If
    
    If Me.Check24.Value = True Then
      If Not blWhereIsUsed Then
        strSQL = strSQL & " WHERE tblTarifas.Zona = '" & Me.Label25.Caption & "'"
        blWhereIsUsed = True
      Else
        strSQL = strSQL & " AND tblTarifas.Zona = '" & Me.Label25.Caption & "'"
      End If
    End If
    
    If Me.Check27.Value = True Then
      If Not blWhereIsUsed Then
        strSQL = strSQL & " WHERE tblTarifas.Zona = '" & Me.Label28.Caption & "'"
        blWhereIsUsed = True
      Else
        strSQL = strSQL & " OR tblTarifas.Zona = '" & Me.Label28.Caption & "' AND tblTarifas.fecha BETWEEN #" & Me.cboFecha & " # AND # " & Me.cboFecha2 & " #  "
      End If
    End If
     
    If Me.Check29.Value = True Then
      If Not blWhereIsUsed Then
        strSQL = strSQL & " WHERE tblTarifas.Zona = '" & Me.Label30.Caption & "'"
        blWhereIsUsed = True
      Else
        strSQL = strSQL & " OR tblTarifas.Zona = '" & Me.Label30.Caption & "' AND tblTarifas.fecha BETWEEN #" & Me.cboFecha & " # AND # " & Me.cboFecha2 & " #"
      End If
    End If
    
    If Me.Check33.Value = True Then
      If Not blWhereIsUsed Then
        strSQL = strSQL & " WHERE tblTarifas.Zona = '" & Me.Label34.Caption & "'"
        blWhereIsUsed = True
      Else
        strSQL = strSQL & " OR tblTarifas.Zona = '" & Me.Label34.Caption & "' AND tblTarifas.fecha BETWEEN #" & Me.cboFecha & " # AND # " & Me.cboFecha2 & " #  "
      End If
    End If
    
    If Me.Check35.Value = True Then
      If Not blWhereIsUsed Then
        strSQL = strSQL & " WHERE tblTarifas.Zona = '" & Me.Label36.Caption & "'"
        blWhereIsUsed = True
      Else
        strSQL = strSQL & " OR tblTarifas.Zona = '" & Me.Label36.Caption & "' AND tblTarifas.fecha BETWEEN #" & Me.cboFecha & " # AND # " & Me.cboFecha2 & " #  "
      End If
    End If
    
    If Me.Check37.Value = True Then
      If Not blWhereIsUsed Then
        strSQL = strSQL & " WHERE tblTarifas.Zona = '" & Me.Label38.Caption & "'"
        blWhereIsUsed = True
      Else
        strSQL = strSQL & " OR tblTarifas.Zona = '" & Me.Label38.Caption & "' AND tblTarifas.fecha BETWEEN #" & Me.cboFecha & " # AND # " & Me.cboFecha2 & " #  "
      End If
    End If
    
    If Me.Check39.Value = True Then
      If Not blWhereIsUsed Then
        strSQL = strSQL & " WHERE tblTarifas.Zona = '" & Me.Label40.Caption & "'"
        blWhereIsUsed = True
      Else
        strSQL = strSQL & " OR tblTarifas.Zona = '" & Me.Label40.Caption & "' AND tblTarifas.fecha BETWEEN #" & Me.cboFecha & " # AND # " & Me.cboFecha2 & " #  "
      End If
    End If
    
    Debug.Print strSQL
    
    Me.sfmTarifas.Form.RecordSource = strSQL
    Me.sfmTarifas.Form.Requery
    
    End Sub

    And here's my SQL statement
    Code:
    SELECT * FROM tblTarifas WHERE tblTarifas.fecha BETWEEN #01/01/1997 # AND # 01/08/1997 #  AND tblTarifas.Zona = 'Baja California' OR tblTarifas.Zona = 'Baja California Sur' AND tblTarifas.fecha BETWEEN #01/01/1997 # AND # 01/08/1997 #
    if somebody can help me I'll apreciated to much

Posting Permissions

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