|
|
|
| Title | Use IIF in a SQL SELECT statement to make a DataReport display a warning if a value is too low |
|---|
| Description | This 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. |
|---|
| Keywords | IIF, SQL, SELECT, database, warning, DataReport |
|---|
| Categories | Database |
|---|
|
|
|
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
|
| |
|
| |
|
|
