Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleUse IIF in a SQL SELECT statement to make a DataReport display a warning if a value is too low
DescriptionThis example shows how to use IIF in a SQL SELECT statement to make a DataReport display a warning if a value is too low in Visual Basic 6.
KeywordsIIF, SQL, SELECT, database, warning, DataReport
CategoriesDatabase
 
IIF takes three arguments: a Boolean expression to evaluate, the result that should be returned if the expression is True, and the result that should be returned if the expression is False. This example uses IIF to display the string WARNING if a test score value is less than 75 or an empty string if the score is not less than 75.

When you click the Show Report button, the program opens a connection to the database. It then builds a query that uses an IIF statement make the calculated Warning field contain an exclamation mark if the Score value is less than 75 and an empty string if Score is at least 75.

The DataReport has a small RptTextBox with its DataField property set to Warning so this control automatically displays whatever is selected into the Warning field.

 
Private Sub cmdShowReport_Click()
Dim db_name As String
Dim conn As ADODB.Connection
Dim warning_query As String
Dim query As String
Dim rs As ADODB.Recordset

    db_name = App.Path
    If Right$(db_name, 1) <> "\" Then db_name = db_name & _
        "\"
    db_name = db_name & "StudentData.mdb"

    ' Open a connection.
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & db_name & ";" & _
        "Persist Security Info=False"
    conn.Open

    warning_query = "IIF(Score<75, '!', '') AS Warning "
    query = "SELECT FirstName, LastName, TestNumber, Score, " & _
        "" & _
        warning_query & _
        "FROM Students, TestScores " & _
        "WHERE Students.StudentId = TestScores.StudentId " _
            & _
        "ORDER BY FirstName, LastName, TestNumber"

    ' Open the Recordset.
    Set rs = conn.Execute(query, , adCmdText)

    ' Attach the DataReport to the Recordset.
    Set DataReport1.DataSource = rs

    ' Display the report.
    DataReport1.Show vbModal

    rs.Close
    conn.Close
End Sub
 
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated