Report Heading

34 + 11 + 35 ----------------- 80

 

Column Headings

10 + 3 + 4 + 3 + 9 + 8 + 4 + 8 + 9 + 12 + 10 ---------------- 80

 

Detail line

10 + 10 + 9 + 20 + 9 + 3 + 5 + 3 + 1 + 10 ------------------ 80

When I code the print statement I take complete control of the spacing (I will not use any commas or semicolons)

(34 spaces) 11 (35 spaces) ABC Company 4 4 12 (10 + 3 spaces) Date(3 + 9 + 8 spaces) Name (8 + 9 spaces) Hours Worked(10 spaces) 10 20 5 (10 spc) Xx/xx/xxxx (9 spc) x---x (9 + 3 spc) xx.xx (3 + 1 + 10 spc)

♦ For report heading

Print space(34) & "ABC Company"

♦ For Column headings

Print Space(13) & "Date" & Space(20) & "Name" & Space (17) & "Hours Worked"

♦ For Detail line

Print space(10) & strFixedDate & space(9) & strName & Space(12) &

strFixedHours

Note: For this problem I don't really have to worry about how many spaces will be used in the right margin since the line will simply end with that last character printed.

♦ The whole premise of this procedure is that you know exactly how much space will be used for each column which means

1) you must use a mono-spaced font 2) you must use fixed length string when printing the column data

Discussed later or here -> Printing variable length fields in fixed length columns Once you have the general spacing it is just a matter of adding a space here and removing a space there to make it "pretty" Sat Feb 5 07:28:23 CST 2011 Printer Object ⊕ Printer Object ♦ To control the default printer we use the Printer object. ♦ The print method is used to send output to the printer. Printer.Print "My Phone List" ⊕ Positioning Output on the Printer There are several options to choose from when controlling the spacing of printed output. For our purposes the first two methods are the best choices. ♦ 1) You can control the position of the output by using the Space() function. The Space() function returns a string of spaces the length of the integer passed to it Both Printer.Print Space(25) & "My Phone List" & vbCrLf _ Space(15) & "Name" & Space(10) & "Phone" And Printer.Print Space(25) & "My Phone List" Printer.Print Space(15) & "Name" & Space(10) & "Phone" Would produce the same output - My Phone List Name Phone ♦ 2) You can control the position of the output using the CurrentX and CurrentY properties of the printer object. The CurrentX property setting is 0 at an object's left edge, and the CurrentY property setting is 0 at its top edge. Coordinates are expressed in twips or the current unit of measurement. A twip is a unit of measurement equal to 1/20 of a printer's point. There are approximately 1440 twips to a inch. For example Printer.CurrentY = 14400 Printer.CurrentX = 6120 Printer.Print Printer.Page 'to print current page # at the bottom of an 8 ? by 11 page ♦ 3) Another way you can control the position of the output is by using semicolons and commas. position. Code Output produced strName = "Joe Smith" Printer.Print "Name "; strName 'Note – there is a space after the 'e’ in the string constant Name Joe Smith When you use commas the next item to print will print in the next print zone. Print zones begin every 14 columns. The width of each column is an average of the width of all characters in the point size for the selected font. Code Output produced Printer.Print "Name",,"Phone Number" Name Phone Number I suggest using commas and/or semicolons to begin with just to get the information out to view. After successfully displaying the information you could then determine the correct spacing and change the statements to use the Space() function and concatenation. ⊕ Controlling Font and FontSize When attempting to design the layout of a report the font size and font t ype you choose will have a significant affect. Notice how the different fonts require different amounts of horizontal space even when the font size is the same in the following samples. This is a sample of Courier New This is a sample of Times New Roman This is a sample of Arial Narrow This is a sample of Arial You can control the font type and size by setting the Font and FontSize property of the printer object at run time. Printer.Font = "courier new" Printer.FontSize = 12 Font and FontSize will remain in effect until changed in code or the program ends. ♦ When controlling the way a report is formatted based on columns and print positions you must use a monospaced font. A monospaced font uses the same amount of horizontal space for every letter and character. Courier New is a monospaced font. This is a sample of Courier New Wwwwwwwwwwwwwwwwwwwwwwwwwwwwwww iiiiiiiiiiiiiiiiiiiiiiiiiiiiiii In a font that is not monospaced the letter i requires less space than the letter w. This is a sample of Times New Roman Wwwwwwwwwwwwwwwwwwwwwwwwwwwwwww iiiiiiiiiiiiiiiiiiiiiiiiiiiiiii ⊕ Advancing to a New Page ♦ The NewPage method will advance to the top of the next page. If Printer.CurrentY > 12960 Then 'to check for current position on page. Printer.NewPage Call NewPage End If 12960 will be about 9 inches from the top of the page. To release the print file to the printer you use the EndDoc method Printer.EndDoc it will not print until the program ends. Sat Feb 5 07:28:24 CST 2011 Printing Variable Length Data When sending data to a fixed length output fields you must ensure that the length of the data is appropriate. For instance, if you design your report around a 20 character name field when you print the name it must use all 20 characters or the information that follows it will not be aligned properly. Also if you allow for a number to be up to 99,999.99 and reserve 9 print positions you must use all 9 positions. The following sample code illustrates what happens when you print variable length data. Do While Not EOF(1) Input #1, strName, sngAmount Print Space(10); strName; Space(5); FormatCurrency(sngAmount) Loop When you declare a variable length string with the following statement Dim strName as string. You may store any length of string in strName. If you assign "Max" to strName, strName will require 3 bytes of memory, if you assign "Maxwell" to strName, strName will require 7 bytes of memory. To ensure that the length of string data is always the same you can use a fixed length string. You declare a fixed length string by specifying the amount of memory required in the Dim statement. Dim strName as string * 20 This strName variable reserve 20 bytes of memory regardless what is stored in it. Dim strName As String * 20 : Do While Not EOF(1) Input #1, strName, sngAmount Print Space(10); strName; Space(5); FormatCurrency(sngAmount) Loop   This is an improvement but there is more to do. ♦ If there were more fields to print following the dollar amounts we would have the same problem as before We can control the length of numeric output in a similar way as we did the string data. The data type of the formatted number printed is actually string because the Format functions return string data types. We can store the result of the Format in a fixed length string and then print the string. Dim strName As String * 15 Dim strFormatedNumber As String * 9 : Do While Not EOF(1) Input #1, strName, sngAmount, strDepartment strFormatedNumber = FormatCurrency(sngAmount) Print Space(10); strName; Space(5); strFormatedNumber; _ Space(5); strDepartment Loop   Notice the dollar amounts are left justified. ⊕ Right Justify a number ♦ Numbers should be right justified use RSet statement. The RSet statement Right aligns a string within a string. Rset strFormatedNumber = FormatCurrency(sngAmount) Sat Feb 5 07:28:24 CST 2011 Variable Arrays Each time we read or input data into a variable the previous value read or inputted is destroyed. ♦ A variable can only hold one value at a time. If we need to retain access to several previously inputted values we could read/input them into separate variables Dim strName1 as string Dim strName2 as string Dim strName3 as string To use each of these variables we would need unique code for each strName1 = txtName.text print strName1 strName2 = txtName.text print strName2 strName3 = txtName.text print strName3 This might work if we only had 3 names but what about 25 or 50? When we need to store several occurrences of the same type of data we use Variable Arrays A variable array is a group of variables of the same data type with the same name. You can use the same instructions to manipulate all the variables in the array by varying a subscript or index to the array. Whenever we refer to a variable array we must qualify it with a subscript or Index Print strName(1) Print strName(3) If we use constants for the subscript we are no better off than when we used different variable names. We usually use an integer variable for a subscript Print strName(intSub) The subscript can be any whole number. It can be in the form of a numeric constant, a variable (integer or long), or an expression that resolves to a whole number. The value of the subscript indicates which strName we want to access. ⊕ Declare an Array We must declare arrays before any reference can be made to it in code. ♦ To define a fixed length array Dim variablename(n to n) as datatype Or Dim variablename(n) as datatype For Example Dim strPhoneNum(1 to 50) as string Dim strPhoneNum(0 to 49) as string Dim strPhoneNum(49) as string Each of the previous Dim statements reserves memory for 50 string variables called strPhoneNum. When you use the strPhoneNum(n to n) form of the declaration you are identifying the lowest and highest subscript value that will be used to access the array. The standard way to declare arrays is. Dim strPhoneNum(49) as string When working with computers we often start counting with zero. The first element in the preceding array is referred to as strPhoneNum(0) The second element is strPhoneNum(1) The fiftieth is strPhoneNum(49) The subscript indicates a displacement from the beginning of the arrays. ♦ IMPORTANT You are responsible for staying within the bounds of your array. If your subscript becomes too large, greater than the number of array entries or too small, less than 0 you will get an error message Subscript out of range And your program dies. ♦ You must always keep track of your subscript. ⊕ There are four basic things we do with arrays 1. Initialize or load values into an array from using constants, user input or from a file. 2. Dump or printout the contents of the array. 3. Lookup or search an array for a particular element. 4. Change an array element ♦ To load values into an array from an InputBox Dim strNames(24) As String Dim intIndex As Integer intIndex = 0 strNames(intIndex) = InputBox("enter a name, 'quit' to quit") Do While LCase(strNames(intIndex)) <> "quit" intIndex = intIndex + 1 strNames(intIndex) = InputBox("enter a name, 'quit' to quit") Loop Print "names in array "; intIndex ♦ To dump the values stored in the previous code - intIndex = 0 Do While LCase(strNames(intIndex)) <> "quit" Print strNames(intIndex) intIndex = intIndex + 1 Loop Sat Feb 5 07:28:24 CST 2011 Searching an Array ♦ There are four basic things we do with arrays 1. Initialize or load values into an array from using constants, user input or from a file. (Discussed on previous web page.) 2. Dump or printout the contents of the array. (Discussed on previous web page.) 3. Lookup or search an array for a particular element. 4. Change an array element When writing code to lookup or search an array for a particular element you can use a loop to work your way through the array, beginning with the first element and then moving on to the second element, and then the third, etc. ♦ The loop will be satisfied when you find what you are looking for OR when you know that you will not find what you are looking for. If the array IS in order by the field you are looking for you know the value you are searching for does not exist in the array once you reach an array value > than the value you are looking for. If the array IS NOT in order by the field you are looking for you know the value you are searching for does not exist in the array once you reach either The end of the array The last value stored in the array (the array may be declared to hold up to 25 elements but only 15 elements have actually been loaded into the array. The following code scrap will search an unordered array for the value stored in strSearchString. It compares the search string to each element in the array until § it finds a match OR § it finds an empty string (which we will assume means there are no other entries in the array) OR § it has looked at all 25 elements. Dim strNames(24) As String : : Do While strSearchString <> strNames(intindex) And strNames(intindex) <> “” And intindex < 24 intindex = intindex + 1 Loop If strSearchString = strNames(intindex) Then MsgBox strSearchString & " was found at location " & intindex Else MsgBox strSearchString & " not found." End If Sat Feb 5 07:28:24 CST 2011 VB Message Box Function ♦ A MsgBox can by used to communicate with the user. It can be used to Indicate invalid data entry Confirm an action Warn of possible consequence of the user action Keep the user informed ♦ The MsgBox can be used as a statement or a function The MsgBox function allows the user to indicate a preferred action by clicking a button. The format of the MsgBox function intAnswer = MsgBox (message, [buttons], [title]) The buttons argument is optional and when used indicates which of the seven different buttons than can be displayed in the message box will be used. (ok, cancel, abort, retry, ignore, yes, or no). OK alone is the default. It also tells what icon should be displayed. (None, red X, balloon ?, !, or balloon I ) The buttons argument is the sum of the desired properties. For example if you want the yes and no buttons to show (4) and the ? in a balloon (32), the buttons argument = 36. The title argument is optional and if used will be displayed in the title bar of the message box. ♦ All message boxes are modal. That is, the user must respond to the box to continue. ♦ You may use built in Visual Basic constants for the buttons argument The buttons argument settings are: Constant Value Description vbOKOnly 0 Display OK button only. vbOKCancel 1 Display OK and Cancel buttons. vbAbortRetryIgnore 2 Display Abort, Retry, and Ignore buttons. vbYesNoCancel 3 Display Yes, No, and Cancel buttons. vbYesNo 4 Display Yes and No buttons. vbRetryCancel 5 Display Retry and Cancel buttons. vbCritical 16 Display Critical Message icon. vbQuestion 32 Display Warning Query icon. vbExclamation 48 Display Warning Message icon. vbInformation 64 Display Information Message icon. vbDefaultButton1 0 First button is default. vbDefaultButton2 256 Second button is default. vbDefaultButton3 512 Third button is default. vbDefaultButton4 768 Fourth button is default. vbApplicationModal 0 Application modal; the user must respond to the message box before continuing work in the current application. vbSystemModal 4096 System modal; all applications are suspended until the user responds to the message box. vbMsgBoxHelpButton 16384 Adds Help button to the message box VbMsgBoxSetForeground 65536 Specifies the message box window as the foreground window vbMsgBoxRight 524288 Text is right aligned vbMsgBoxRtlReading 1048576 Specifies text should appear as right-to-left reading on Hebrew and Arabic systems The first group of values (0–5) describes the number and type of buttons displayed in the dialog box; the second group (16, 32, 48, 64) describes the icon style; the third group (0, 256, 512) determines which button is the default; and the fourth group (0, 4096) determines the modality of the message box. When adding numbers to create a final value for the buttons argument, use only one number from each group. The MsgBox function returns a value to indicate which button the user clicked. If you use any button other than the OK button you must provide an integer variable to place the return value in. An Example: Dim intAnswer as integer intAnswer = msgbox(“Are you sure”, vbYesNo + vbQuestion) If intAnswer = 7 then Return Values Constant Value vbOK 1 vbCancel 2 vbAbort 3 vbRetry 4 vbIgnore 5 vbYes 6 vbNo 7 If intAnswer = vbNo then Sat Feb 5 07:28:25 CST 2011 Programmer Defined Functions A function is a procedure which returns a single value. We have used many built-in functions lblAmount.Caption = FormatCurrency(sngAmount,0) sngAmount = Val(txtAmount.text) if IsNumeric(txtAmount.text) We (programmers) can write our own Programmer Defined functions. In Visual Basic the header for a function looks like Public Function FunctionName([arguments]) [As type] : : FunctionName = (some value) End Function For example Public Function Square(ByVal intNum as Integer) as long ‘ return data type ‘To return a value you make the name of the function = the return value Square = intNum ^ 2 End Function The statement that calls the preceding function might look like lngAnswer = Square(intNum) Sat Feb 5 07:28:25 CST 2011 Control Break Logic Detail lines contain data from the records in a file...one line per record. The detail lines can be grouped by a key field and summary information about the group may be printed when ever a Control Break occurs. What is a “Control break” ? When detail lines are reported grouped in order by a key field (salesperson number, department number, date) and there is a change in the key field a control break occurs. For instance, a file containing records that represent individual sales might be sorted by date. A report lists all the sales made on a particular date and then displays a total for all sales on that date. Such a report is said to break on transaction date. This means that there is a “break” in the listing when the date changes. ⊕ To handle control break logic you must do the following ♦ Establish a working variable to store the value of the key field for the current group. ♦ After inputting each record compare the record’s key field value to the stored key field value to see if a “break” has occurred. If a “break” has occurred Report summary information for the group just completed. If the key field value is to be used in the summary information you must use the value stored in the working variable. Total for 7/23/2003 320.99 You might add any intermediate accumulators or counters to grand totals. Reinitialize any accumulators or counters associated with the group Save new key field value in the working variable ⊕ More things to consider. ♦ You must store the value stored in the key field of the first record in the working variable to prevent a break from happening at the beginning of the report. You could use a Boolean variable to indicate it is the first record If blnFirstRecord = True then Save key field value in working variable Turn Boolean variable off End if Note: remember that the default value for a Boolean variable in Visual Basic is False ♦ A“control break” happens at EOF because there are no more records for the last group. Since you will be reporting summary information in two places (when the key value changes and at EOF) you might consider putting the instructions that handle the control break in a general procedure and calling if from those two places. Sat Feb 5 07:28:25 CST 2011