Share this Page URL
Help

Chapter 17. Using Visual Basic to Manage... > Creating a Custom Report Feature - Pg. 590

Using Visual Basic to Manage Data strSelect = strSelect & " " & strWhere Case Is = 2 strSelect = "SELECT TaskName, StartDate, Priority, DueDate, " & _ "Status FROM qryTasks" strCountry = lstCountry.Value strStartDate = txtStartDate.Value strWhere = "WHERE Country = " & "'" & strCountry & "'" & _ " AND StartDate > " & "#" & strStartDate & "#;" strSelect = strSelect & " " & strWhere End Select DoCmd.Echo False DoCmd.OpenReport "TasksByPriority", acViewDesign With Reports!TasksByPriority .RecordSource = strSelect !lblTitle.Caption = _ "Tasks for " & strCountry End With DoCmd.Close , , acSaveYes DoCmd.OpenReport "TasksByPriority", acViewPreview DoCmd.Echo True End Sub 590 The procedure begins by declaring a variable ( intPriority ) to hold the value of the Priority option group as well as four string variables that the procedure puts together to create the SQL statement that becomes the report's record source. After assigning the value of the option group to intPrior- ity, the procedure uses a Select Case statement to build the SQL statement depending on whether High or All is selected in the option group. If the High option is selected, that value is added to the SQL statement's WHERE clause. If All is selected, the WHERE clause criteria expression doesn't