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!

Free Online Virus Scanner – using over 30 scanning engines

A colleague has just told me that a file sent from his email address has been returned with a “suspect file” warning.

Our virus scanner (McAfee) said it was clean.

Just to be certain I wanted to run a scan using a different engine and came across the VirusTotal site.

The link to the site is: http://www.virustotal.com (opens in new window)

This site checks a single file, uploaded by you, using over 30 different virus/malware engines including:

• AhnLab (V3)
• Aladdin (eSafe)
• ALWIL (Avast! Antivirus)
• Authentium (Command Antivirus)
• AVG Technologies (AVG)
• Avira (AntiVir)
• Bit9 (FileAdvisor)
• Cat Computer Services (Quick Heal)
• ClamAV (ClamAV)
• CA Inc. (Vet)
• Doctor Web, Ltd. (DrWeb)
• Eset Software (ESET NOD32)
• ewido networks (ewido anti-malware)
• Fortinet (Fortinet)
• FRISK Software (F-Prot)
• F-Secure (F-Secure)
• G DATA Software (GData)
• Hacksoft (The Hacker)
• Ikarus Software (Ikarus)
• Kaspersky Lab (AVP)
• McAfee (VirusScan)
• Microsoft (Malware Protection)
• Norman (Norman Antivirus)
• Panda Security (Panda Platinum)
• Prevx (Prevx1)
• Rising Antivirus (Rising)
• Secure Computing (Webwasher)
• BitDefender GmbH. (BitDefender)
• Sophos (SAV)
• Sunbelt Software (Antivirus)
• Symantec (Norton Antivirus)
• VirusBlokAda (VBA32)
• Trend Micro (TrendMicro)
• VirusBuster (VirusBuster)

You can also upload a file by e-mail.

Microsoft Access – Refreshing Data On A Form or In A Form Control

I’ve just been working on a tabbed form within Microsoft Access where each tab contains a sub form showing subsets of data.

The user can make a change on one form which must then be reflected on another form.

To do this we must refresh (or requery) the data on the form that is to relflect the change.

We can do this using the REQUERY method.

In my case this meant the following line of code:

Forms![frmMainForm]![frmSubFormOnMainForm].Requery

We could even requery a single control by using the format:

Forms![frmMainForm]![frmSubFormOnMainForm].ControlName.Requery

As Microsoft explains at http://msdn.microsoft.com/en-us/library/aa199106(office.10).aspx:

The Requery method does one of the following:

• Reruns the query on which the form or control is based.
• Displays any new or changed records or removes deleted records from the table on which the form or control is based.
• Updates records displayed based on any changes to the Filter property of the form.