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

    Talking Unanswered: Friday Lazy Code Party!

    Hi, I think that we need to put more "move on" this forum, so... I think, put in this Thread, all code that you think is usefull, it doesn't matter that this code concern to databox, combobox, SQL, put whatever you want, and show us a little explain, this kind of thinks serve as a reference... plz come and participate!

    David

  2. #2
    Join Date
    Aug 2004
    Posts
    22

    Talking I start

    I start, this is the code that I'm current working on it, this code filter a subform due combobox, checkbox, choice, also export this query to an excel file...

    happy code

    David

    Code:
    Private Sub cmdAyuda_Click()
    ChDir App.Path
    Shell "NOTEPAD.EXE ayuda.txt", vbMaximizedFocus
    End Sub
    
    Private Sub cmdExportar_Click()
    Dim strSQL As String
    Dim strSQL2 As String
    Dim blWhereIsUsed As Boolean
    blWhereIsUsed = False
    Dim strDate1 As String
    Dim strDate2 As String
    
    strDate1 = ChangeFomat(Me.cboFecha)
    strDate2 = ChangeFomat(Me.cboFecha2)
    
    
    strSQL = " SELECT * FROM tblTarifas"
    strSQL2 = "Select * into [Excel 8.0;Database=" & Me.cboArchivo.Value & "].[" & Me.cboHoja.Value & "] 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 #" & strDate1 & "# AND #" & strDate2 & "# "
        strSQL2 = strSQL2 & " WHERE tblTarifas.fecha BETWEEN #" & strDate1 & "# AND #" & strDate2 & "# "
        blWhereIsUsed = True
      End If
    End If
    
    ' Este es el Dummie!
    Me.Check49.Value = True
    If Me.Check49.Value = True Then
      If Not blWhereIsUsed Then
        strSQL = strSQL & " WHERE tblTarifas.Zona = '" & Me.Label50.Caption & "'"
        blWhereIsUsed = True
      Else
        strSQL = strSQL & " AND tblTarifas.Zona = '" & Me.Label50.Caption & "'"
        strSQL2 = strSQL2 & " AND tblTarifas.Zona = '" & Me.Label50.Caption & "'"
      End If
    End If
    ' Fin del dumie
    
    If Me.Check24.Value = True Then
      If Not blWhereIsUsed Then
        strSQL = strSQL & " WHERE tblTarifas.Zona = '" & Me.Label25.Caption & "'"
        blWhereIsUsed = True
      Else
        strSQL = strSQL & " OR tblTarifas.Zona = '" & Me.Label25.Caption & "' AND tblTarifas.fecha BETWEEN #" & strDate1 & "# AND #" & strDate2 & "# "
        strSQL2 = strSQL2 & " OR tblTarifas.Zona = '" & Me.Label25.Caption & "' AND tblTarifas.fecha BETWEEN #" & strDate1 & "# AND #" & strDate2 & "# "
      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 #" & strDate1 & "# AND #" & strDate2 & "# "
        strSQL2 = strSQL2 & " OR tblTarifas.Zona = '" & Me.Label28.Caption & "' AND tblTarifas.fecha BETWEEN #" & strDate1 & "# AND #" & strDate2 & "# "
      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 #" & strDate1 & "# AND #" & strDate2 & "# "
        strSQL2 = strSQL2 & " OR tblTarifas.Zona = '" & Me.Label30.Caption & "' AND tblTarifas.fecha BETWEEN #" & strDate1 & "# AND #" & strDate2 & "# "
      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 #" & strDate1 & "# AND #" & strDate2 & "# "
      strSQL2 = strSQL2 & " OR tblTarifas.Zona = '" & Me.Label34.Caption & "' AND tblTarifas.fecha BETWEEN #" & strDate1 & "# AND #" & strDate2 & "# "
      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 #" & strDate1 & "# AND #" & strDate2 & "# "
        strSQL2 = strSQL2 & " OR tblTarifas.Zona = '" & Me.Label36.Caption & "' AND tblTarifas.fecha BETWEEN #" & strDate1 & "# AND #" & strDate2 & "# "
      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 #" & strDate1 & "# AND #" & strDate2 & "# "
        strSQL2 = strSQL2 & " OR tblTarifas.Zona = '" & Me.Label38.Caption & "' AND tblTarifas.fecha BETWEEN #" & strDate1 & "# AND #" & strDate2 & "# "
      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 #" & strDate1 & "# AND #" & strDate2 & "# "
        strSQL2 = strSQL2 & " OR tblTarifas.Zona = '" & Me.Label40.Caption & "' AND tblTarifas.fecha BETWEEN #" & strDate1 & "# AND #" & strDate2 & "# "
      End If
    End If
    
    
    Debug.Print strSQL
    
    'Aqui inicia la exportación a excel
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL2
    DoCmd.SetWarnings True
    'Aqui termina
    
    
    Me.sfmTarifas.Form.RecordSource = strSQL
    Me.sfmTarifas.Form.Requery
    
    
    
    End Sub
    
    Public Function ChangeFomat(strDate As String) As String
        Dim strDay As String
        Dim strMonth As String
        Dim strYear As String
        
            
        strDay = Mid$(strDate, 4, 2)
        strMonth = Mid$(strDate, 1, 2)
        strYear = Mid$(strDate, 7, 4)
        ChangeFomat = strDay & "/" & strMonth & "/" & strYear
    
    End Function
    
    
    Private Sub cmdFiltro_Click()
    Dim strSQL As String
    Dim blWhereIsUsed As Boolean
    blWhereIsUsed = False
    Dim strDate1 As String
    Dim strDate2 As String
    
    strDate1 = ChangeFomat(Me.cboFecha)
    strDate2 = ChangeFomat(Me.cboFecha2)
    
    
    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 #" & strDate1 & "# AND #" & strDate2 & "# "
        blWhereIsUsed = True
      End If
    End If
    
    ' Este es el Dummie!
    Me.Check49.Value = True
    If Me.Check49.Value = True Then
      If Not blWhereIsUsed Then
        strSQL = strSQL & " WHERE tblTarifas.Zona = '" & Me.Label50.Caption & "'"
        blWhereIsUsed = True
      Else
        strSQL = strSQL & " AND tblTarifas.Zona = '" & Me.Label50.Caption & "'"
      End If
    End If
    ' Fin del dumie
    
    If Me.Check24.Value = True Then
      If Not blWhereIsUsed Then
        strSQL = strSQL & " WHERE tblTarifas.Zona = '" & Me.Label25.Caption & "'"
        blWhereIsUsed = True
      Else
        strSQL = strSQL & " OR tblTarifas.Zona = '" & Me.Label25.Caption & "' AND tblTarifas.fecha BETWEEN #" & strDate1 & "# AND #" & strDate2 & "# "
      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 #" & strDate1 & "# AND #" & strDate2 & "# "
      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 #" & strDate1 & "# AND #" & strDate2 & "# "
      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 #" & strDate1 & "# AND #" & strDate2 & "# "
      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 #" & strDate1 & "# AND #" & strDate2 & "# "
      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 #" & strDate1 & "# AND #" & strDate2 & "# "
      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 #" & strDate1 & "# AND #" & strDate2 & "# "
      End If
    End If
    
    
    Debug.Print strSQL
    
    Me.sfmTarifas.Form.RecordSource = strSQL
    Me.sfmTarifas.Form.Requery
    End Sub

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Just a point of better practise...If you want people to combine a mega listing of helpful code to generalize it. I mean if somebody is new and just tries to paste that heap somewhere it obvisously won't work, or more seasoned guys may find it somewhat annoying to use, because they have to go through it all and edit to their process...
    Ryan
    My Blog

Posting Permissions

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