HUMOUR - An Elephant On A Slope and B Is The New C

The joy of StubleUpon is finding pages like the following.

The following two images are of answers submitted for exams.

His professor sent him an e-mail the following day:

Dear Michael,

Every year I attempt to boost my students’ final grades by giving them this relatively simple exam consisting of 100 True/False questions from only 3 chapters of material. For the past 20 years that I have taught Intro Communications 101 at this institution I have never once seen someone score below a 65 on this exam. Consequently, your score of a zero is the first in history and ultimately brought the entire class average down a whole 8 points.

There were two possible answer choices: A (True) and B (False). You chose C for all 100 questions in an obvious attempt to get lucky with a least a quarter of the answers. It’s as if you didn’t look at a single question. Unfortunately, this brings your final grade in this class to failing. See you next year!

May God have mercy on your soul.

Sincerely,
Professor William Turner

P.S. If all else fails, go with B from now on.
B is the new C

If you like this particular form of humour take a look at the two sites that I found these on:

http://totallysux.com/photo/20338/

http://funnyexamanswers.com

SQL SERVER - SUBSTRING- The Equivalent of the VBA MID Function

Following on from my previous post regarding the SQL Server CHARINDEX function here’s some information about the SUBSTRING function for those of you, like me, tend to think in VBA!

The SQL Server equivalent of the VBA MID function is SUBSTRING.

We can use it to find a part of a string.

For example:

SUBSTRING(”This is text”,3,2) would return the string “is”.

Microsoft defines the function’s syntax as follows.

Syntax

SUBSTRING ( expression , start , length )

Arguments

expression

Is a character string, binary string, text, image, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.

start

Is an integer that specifies where the substring begins.

length

Is a positive integer that specifies how many characters or bytes of the expression will be returned. If length is negative, an error is returned.

Note Because start and length specify the number of bytes when SUBSTRING is used on text data, DBCS data, such as Kanji, may result in split characters at the beginning or end of the result. This behavior is consistent with the way in which READTEXT handles DBCS. However, because of the occasional strange result, it is advisable to use ntext instead of text for DBCS characters.

Page Loading Image Generator

I found the following page today when I was searching for an image to display whilst waiting for a web page to load.

http://www.ajaxload.info/ (opens in new window)

I wanted something like the nice spinning circle used on Windows Vista.

The page takes a few simple values (foreground/background colour etc.) and then creates a nice spinning circle graphic which you can then download.

It will also allow you to choose from 35 different graphic styles (e.g. circles, bars etc.) for your image.

Screenshot of http://www.ajaxload.info/

This generates a GIF image similar to the following:

SQL SERVER - CHARINDEX - The Equivalent of the VBA InStr Function

Doing 90% of my work in ASP and VBA I often find that I forget about the CHARINDEX function within SQL Server!

In ASP I’d usually do something like:

p = instr(myString, ” “)


to find the first instance of the space character within the string myString.

On SQL Server the equivalent is the CHARINDEX function.

This finds the first occurance of one string within another string.

For example:


CHARINDEX(” “,”My Blog”)


would return the value 3.

---

Microsoft’s full description of this function is as follows:

Syntax

CHARINDEX ( expression1 ,expression2 [ , start_location ] )

Arguments

expression1

Is an expression that contains the sequence of characters to be found. expression1 is an expression of the character string data type category.

expression2

Is an expression, typically a column searched for the specified sequence. expression2 is of the character string data type category.

start_location

Is the character position to start searching for expression1 in expression2. If start_location is not specified, is a negative number, or is zero, the search starts at the beginning of expression2. start_location can be of type bigint.

Return Types

bigint if expression2 is of the varchar(max), nvarchar(max) or varbinary(max) data types, otherwise int.
Remarks

If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type. CHARINDEX cannot be used with text, ntext, and image data types.

If either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70 or higher. If the database compatibility level is 65 or lower, CHARINDEX returns NULL only when both expression1 and expression2 are NULL.

If expression1 is not found within expression2, CHARINDEX returns 0.

CHARINDEX performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.

The starting position returned is 1-based, not 0-based.

ASP - Using the Case Select Statement - When x to y doesn’t work!

Using ASP we often use the Case Select statement to choose an action dependant on a value.

e.g.

Select Case intPrice

Case 100

response.write(”Item Costs £100.00″)

Case 200

response.write(”Item Costs £200.00″)

Case 300

response.write(”Item Costs £300.00″)

Case Else

response.write(”Invalid Price”)

End Select

However, what happens if we want to choose an action when a value is between two other values?

If you’ve used VB then you’ll have used something like Case 0 To 100.

But, this doesn’t work in ASP.

There is, however, a workaround to this. In our example above we might want to show a message in one of 4 cases (with a value being stored in a variable called myValue):

1. When the value is between £0 and £100;
2. When the value is between £101 and £200;
3. When the value is between £201 and £300;
4. When the value is greater than £300;

We can do this using the following:

Select Case True

Case (myValue > 0 AND myValue <101)
response.write(”Item Cost Is Low”)

Case (myValue > 100 AND myValue <201)
response.write(”Item Cost Is Medium”)

Case (myValue > 200 AND myValue <301)
response.write(”Item Cost Is High”)

Case Else
response.write(”Invalid Item Cost”)

End Select

Here the Select Case True makes sure that we will consider all the possible Case statements and then the comparison within each statement dictates which will be TRUE.

I’m not sure if this is legitimate ASP or if it’s just a hack but it works!

ASP - Writing Out Information During Debugging

Like many users of ASP I often use the Response Object Write Method to show the value of a variable during debugging.

e.g.

response.write(strSQL) ' write out value of an SQL string

However, on a “busy” page it’s not always easy to see the result of this action as the value written out gets lost in the rest of the text on the page.

Also, I’ll often write out an SQL string in this way which I then want to copy into a query to mess around with the results.

To get around this I wrote the following two functions.

The first (DW - short for DocumentWrite) relies on a variable called sysDebug being set to “TRUE” (as a string). I’ll often place the sysDebug declaration at the top of a main configuration page. When I want to debug my application I’ll set this variable to “TRUE” and the DW function will write out the values throughout my pages.

This then displays something similar to the following:

As an extension to this I also created the (imaginitively named) DW2 function.

This is similar to DW but does not use the sysDebug variable. This function is simply entered as and when required. I tend to use this to replace a standard response.write.

The DW2 function also provides a Copy To Clipboard button that will copy the contents of the debug box to the user’s clipboard.

This results in something like this:

The code for the two function is as follows:

' DW requires the sysDebug variable to be set as "TRUE" before output is shown
Function DW(myText)
dim BuildDebugLine
if sysDebug="TRUE" then
BuildDebugLine = "
<blockquote>"
BuildDebugLine= BuildDebugLine & "
<div style='border:1px solid #000000; bgcolor:#FFFFCC border-style: solid; padding-left: 4px; padding-right: 4px; padding-top: 4px; padding-bottom: 4px; background-color:#FFFF99; width:100%'><font size='2'><code>" & myText & "</code></font></div></blockquote>
"
response.write(BuildDebugLine)
end if
End Function

' DW2 does not use sysDebug variable and writes out value whenever called
Function DW2(myText)
dim BuildDebugLine
if myText="" then myText="[Empty String]"
if isnull(myText)="" then myText="[NULL]"
BuildDebugLine = "
<blockquote>"
BuildDebugLine= BuildDebugLine & "
<div style='border:1px solid #000000; bgcolor:#FFFFCC border-style: solid; padding-left: 4px; padding-right: 4px; padding-top: 4px; padding-bottom: 4px; background-color:#99FF99; width:100%'><font size='2'><code><span id='copytext'>" & myText & "</span></code>
"
BuildDebugLine= BuildDebugLine & "<TEXTAREA ID='holdtext' STYLE='display:none;'>"
BuildDebugLine= BuildDebugLine & "</TEXTAREA>"
BuildDebugLine= BuildDebugLine & "
<BUTTON onClick='ClipBoard();' style='font-family: Tahoma; font-size: 8pt'>Copy to Clipboard</BUTTON></font></div></blockquote>
"
response.write(BuildDebugLine)
End Function

Sorry about the layout of the code above. I’ll sort this out when I figure out how WordPress handles code!

Enabling Mouse Scrolling In VBA

I’ve just had to set up a new PC and, for a few minutes there, was confused as to why the scroll wheel did not work in the VBA code window.

Then I remembered that VBA is based on the Microsoft Visual Basic 6 IDE which doesn’t include built-in support for the scroll wheel.

After a little searching I found the following article on the Microsoft site: http://office.microsoft.com/en-us/access/HA101175901033.aspx (opens in new window)

In short, to enable the mouse wheel scrolling behaviour in the VBA window, you must download a package from Microsoft at http://office.microsoft.com/search/redir.aspx?AssetID=XT101665991033&CTT=5&Origin=HA101175901033 (opens in new window) and then do the following:

• Run the downloaded executable to extract the files
• Add the included DLL to your machine
• Register the DLL using RegSvr32
• Run the included registry file to add the required elements to your machine’s registry (remembering to take a backup of the registry first of course)
• Add the MouseWheel Fix to your VBA project using the Add-In manager

Now the scroll wheel should work in your VBA code!

Microsoft Access - Handling NULL values with the NZ() function

In Microsoft Access VBA, as in many other implementations, if we try to evaluate an expression containing a NULL value the whole expression will return NULL.

For example:

Dim varMyValue as Variant
varMyValue = NULL
MsgBox IsNull(varMyValue + 2)


This will return TRUE.

However, it is often the case that we would like this expression to “ignore” the NULL value and return the value 2.

We can achieve this using the NZ function.

The syntax of this function is as follow:

Nz(variant, [valueifnull])

Thus we could ignore the NULL value and substitute it with 0.

e.g.

Dim varMyValue as Variant
varMyValue = NULL
MsgBox NZ(varMyValue,0) + 2


Here the NULL value is replaced with the value zero and our message box displays the value 2.

We could also use the NZ function in other ways.

If we have a variable containing a string , for example, the country of a purchaser (e.g. UK, US, PL) and we use this to calculate a postage rate for goods sent to this purchase we could use the NZ function in the following way to replace a NULL value with a suitable message or calculation.

e.g.

strPostage = NZ(strCountry,0) ‘ if we wanted to set the postage value to zero if it is NULL


or

strPostage = NZ(strCountry,”No Country Code Given”) ‘ to maybe use in a MsgBox if strCountry is NULL


We could also use the NZ function in an Access SQL query.


SELECT [Surname], [FirstName], [DOB], NZ([Address],”No Address Available”)


Note, however, that this function is only available within Access and not within any of the rest of the Office suite. Also, other variants of SQL may not support this function.

Microsoft Access - Parse OpenArgs - Seperating Each OpenArg Value

I haven’t used the OpenArgs property in Microsoft Access for ages.

However, today I needed to pass a variable number of values from a form to another form.

In doing so I needed to seperate out each value passed in the OpenArgs property of the calling form which have been seperated using the | character (accessed using shift + \ on my UK keyboard).

So how did I do it?

Here’s the code:

Dim Hold_OpenArgValues() as String
Hold_OpenArgValues()=Split(Me.OpenArgs, “|”)

We can then do something with the values returned to the array.

As an example we could write them to the Immediate window using the UBound function to decide how many items have been passed (this was going to be variable remember).

For counter = 0 to UBound(Hold_OpenArgValues)
  Debug.Print “Value ” & counter, Hold_OpenArgValues(counter)
Next counter


Microsoft Access - Highlight the Current Active Form Control

I’ve just been working on a form with several controls and wanted to highlight which control was currently active. I wanted to do this by setting the control’s background colour to yellow when they had focus and setting them back to white when focus was lost.

With a few form controls this would have been as simple as adding a single line to the GotFocus and LostFocus events for each item on the form setting their back colour.

e.g.  Me.txtBoxName.BackColor=11596799 (where 11596799 is yellow)

However, with lots of controls on a form this can get a little tedious and I may want to change the behaviour of this action later (maybe changing yellow to green for example).

As I don’t want to have to do this several times I created two subroutines that do the job of changing the control’s background colours when they get focus or lose focus.

These are:

Sub SetGotFocus()
Set ctl = Screen.ActiveControl
ctl.BackColor = 11596799 ‘ set background to yellow
End Sub

and

Sub SetLostFocus()
Set ctl = Screen.ActiveControl
ctl.BackColor = -2147483643 ‘ set background to white
End Sub

Then, for each control that I want to change the background, I call the subroutines from their GotFocus and LostFocus events.

Private Sub txtBoxName_GotFocus()
SetGotFocus
End Sub

Private Sub txtBoxName_LostFocus()
SetLostFocus
End Sub

This isn’t rocket science but at least allows a consistent approach to highlighting a control and allows changes to be made more easily. Also it means I don’t have to type “-2147483643″ more than once!