Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    .. instead to use the "IF" condition when the listview items are filled is possible to insert in the sql string the same IF condition?

    [codebox]Sub FILL_LISTVIEW_2()
    'SI
    Dim TOT As Double, TOT1 As Double, CONTA_RECORD As Long, TOT2 As Double
    Dim X

    On Error GoTo errore

    CONTA_REC = Empty

    If Not RSSQLD.State = adStateClosed Then
    RSSQLD.Close
    End If

    RSSQLD.CursorLocation = adUseClient
    RSSQLD.Open "SELECT PROVA2,PROVA1,PROVA3,PROVA9,PROVA11,PROVA12," & _
    "PROVA17,PROVA13,PROVA14,PROVA18 FROM DATI WHERE PROVA16 = '" & _
    Left(Me.COMBO_AREA.Text, 4) & "' ORDER BY PROVA2", _
    CNSQL, adOpenForwardOnly, adLockReadOnly

    While Not RSSQLD.EOF
    CONTA_REC = CONTA_REC + 1
    RSSQLD.MoveNext
    Wend

    RIGA = Empty

    Me.ProgressBar.Visible = True
    Me.ProgressBar.value = Empty

    Me.ListView.ListItems.Clear

    With RSSQLD

    Me.ListView.Refresh

    .MoveFirst

    While Not .EOF

    Set X = ListView.ListItems.Add(, , .Fields!PROVA2)
    X.SubItems(1) = .Fields!PROVA1
    X.SubItems(2) = .Fields!PROVA3
    X.SubItems(3) = .Fields!PROVA9
    X.SubItems(4) = .Fields!PROVA11
    X.SubItems(5) = .Fields!PROVA12

    If IsNull(.Fields!PROVA17) Then
    X.SubItems(6) = Format(0, "#,##0.00")
    Else
    X.SubItems(6) = Format(.Fields!PROVA17, "#,##0.00")
    TOT = TOT + X.SubItems(6)
    End If

    If IsNull(.Fields!PROVA13) Then
    X.SubItems(7) = Format(0, "#,##0.00")
    Else
    X.SubItems(7) = Format(.Fields!PROVA13, "#,##0.00")
    TOT1 = TOT1 + X.SubItems(7)
    End If

    If IsNull(.Fields!PROVA14) Then
    X.SubItems(8) = Format(0, "#,##0.00")
    Else
    X.SubItems(8) = Format(.Fields!PROVA14, "#,##0.00")
    TOT2 = TOT2 + X.SubItems(8)
    End If

    If Not IsNull(.Fields!PROVA18) Then
    X.SubItems(9) = Format(.Fields!PROVA18, "#,##0")
    Else
    X.SubItems(9) = Format(0, "#,##0")
    End If

    RIGA = RIGA + 1

    .MoveNext

    Me.ProgressBar.value = (RIGA / CONTA_REC) * 100

    Wend

    Me.ProgressBar.Visible = False

    End With

    Me.Label4.Caption = Format(TOT1, "#,##0.00")
    Me.Label9.Caption = Format(TOT, "#,##0.00")
    Me.Label14.Caption = Format(TOT2, "#,##0.00")
    Me.Label6.Caption = Format(ListView.ListItems.Count, "#,##0")

    Exit Sub

    errore:
    MsgBox "Errore Numero: " & CStr(Err.Number) & vbCrLf & _
    "Descrizione: " & Err.Description & vbCrLf & _
    "Sorgente dell'Errore: " & Err.Source

    Err.Clear

    End Sub[/codebox]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use

    RSSQLD.Open "SELECT PROVA2,PROVA1,PROVA3,PROVA9,PROVA11,PROVA12," & _
    "IIf(IsNull(PROVA17),0,PROVA17) AS P17,IIf(IsNull(PROVA13),0,PROVA13) AS P13, " & _
    "IIf(IsNull(PROVA14),0,PROVA14) AS P14, IIf(IsNull(PROVA18),0,PROVA18) AS P18 " & _
    "FROM DATI WHERE PROVA16 = '" & _
    Left(Me.COMBO_AREA.Text, 4) & "' ORDER BY PROVA2", _
    CNSQL, adOpenForwardOnly, adLockReadOnly

    and then refer to P17, P13, P14 and P18 instead of to PROVA17 etc. in the rest of the code:

    X.SubItems(6) = Format(.Fields!P17, "#,##0.00")

    (you don't have to check for IsNull here)

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans sorry me but dont uderstand :-(

    and then refer to P17, P13, P14 and P18 instead of to PROVA17 etc. in the rest of the code:
    X.SubItems(6) = Format(.Fields!P17, "#,##0.00")

    can you modify one part of my code for example....

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Please read my previous reply carefully. It contains all you need.

Posting Permissions

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