Where errors are concerned, null values are an equal-opportunity menace. If an unhandled null value doesn't generate a runtime error, it'll show up in erroneous data. Neither problem is your run of the mill "oops, there's a bug" error. In fact, an unhandled null value is the sign of a lazy or inexperienced developer. When null values are acceptable values, and they often are, you must handle them upfront and aggressively.

#1: Knowing null

 

You can't handle a value properly if you don't understand its nature. A common misconception is that a null value is simply an empty field or no value at all. That's not true. A null value indicates that the data is missing or unknown. Occasionally, a null value does mean that the data doesn't exist or isn't valid for that particular record, but the concepts aren't interchangeable.

#2: Dealing with null

 

Since Access allows null values, it's your job to determine whether you want to store them. Generally, the data will be your best guide. If the nature of the data requires that all data be present to save the record, you can handle null values at the table level. Simply set the field's Required property to Yes and bypass the problem. Be prepared for the rules to change.

Few applications are so tight that nulls aren't present. If users need the flexibility to create records without entering all of the data at the time they create the record, you have a choice. Allow the table to store a null value or use a default expression that stores an appropriate text message, such as "NA" or "Pending."

Unfortunately, this solution works only for text fields. For numeric fields, you could use a default value of 0, but that might cause trouble in the long run because functions handle Null and 0 differently (see #7). In addition, the Default property works only for new records. That means that you can't apply this solution to existing records. The truth is, it's usually easier to handle null values than it is to usurp them in this fashion.

#3: Not equating null

 

Don't try to find null values by equating them to anything else. The following expressions return an error, regardless of anything's value:

anything = Null
anything <> Null

As far as Access is concerned, Null doesn't equal anything. You can't use the Equals operator (=) to find null values. Nor can you use the Inequality operator (<>) to exclude them. (This isn't always true outside Access.)

#4: Finding or excluding null values

 

Once you decide that null values are acceptable, it's your job to accommodate them throughout the application. To find or exclude null values, use Is Null and Not Is Null, respectively, in criteria expressions and SQL WHERE clauses. For instance, to find null values in a query, you'd enter Is Null in the appropriate field's Criteria cell. When building a WHERE clause, use Is Null and Not Is Null as follows:

WHERE source.field Is Null
WHERE NOT(source.field) Is Null

Protect VBA expressions from errors by using IsNull()and Not IsNull().For instance, the use of IsNull() in the following If statement handles a potential runtime error when null values exist:

If Not IsNull(field) Then ...

Although Is Null and IsNull() have similar functions, they're not interchangeable.

#5: Working around null

 

Access won't always work with null values as you might expect. If you allow them, be prepared for surprises. For instance, a simple expression such as

GrandTotal = Subtotal + Shipping

becomes a problem if Shipping contains null values. Instead of returning just the Subtotal, as you might expect, the expression returns Null. That's because any equation that encounters a null value will always return Null. Although it's a nuisance, it makes sense. You can't evaluate an unknown value.

If your data contains null values, use the Nz() function to protect your expressions from this error. Specifically, Nz() returns a value other than Null when it encounters Null as follows:

GrandTotal = Subtotal + Nz(Shipping)

In this case, Nz() returns 0 when Shipping equals Null. Use Nz() in criteria and VBA expressions. Access projects don't support Nz(). Instead, use Transact SQL's IsNull function.

#6: Finding null values using ADO

 

In # 3, you learned that Null doesn't equal anything. That's true, as long as you're using native functions and VBA. It isn't true if you're manipulating data via the ActiveX Data Object (ADO) library. For instance, the following statement executed against an ADO Recordset object returns an error:

rst.Find "FaxNumber Is Null"

That's because ADO doesn't recognize the Is operator in this context. The ADO library supports the Equals and Inequality operators when searching for or excluding null values. Fortunately, the correction is as simple as replacing the Is operator with the Equals operator:

rst.Find "FaxNumber = Null"

To exclude null values using ADO, use the Inequality operator:

rst.Find "FaxNumber <> Null"

You'll find Access a bit of an oddball on this issue. Many libraries use the Equals and Inequality operators instead of Is. If a non-native library returns an error when working with null values, this switch will probably do the trick.

#7: Understanding the inconsistency of SQL aggregates

 

Not all aggregate functions consider null values. The good news is, there's a bit of reason to the inconsistency. An aggregate function that evaluates a field does not evaluate null values in its result. However, Count(), First(), and Last() do evaluate null values. It makes sense that they would--just because one field contains a null value doesn't negate the row's purpose within the context of the domain. For instance, Count(*) counts the total number of rows in a recordset even if some of those rows contain null values. If you want to exclude null values in a count, specify the field in the form Count(field). The result of both forms may or may not be the same. The point is, the field-specific form won't consider null values in its count.

#8: Including null values in a conditional search

 

When using a WHERE clause to find or restrict data, you must explicitly specify null values. Otherwise, Jet excludes the row from the results. This behavior is inherent in the equality issue discussed in #3. Because Null doesn't equal anything, it can't satisfy a condition other than Is Null. For instance, the simple expression

WHERE field < 5

will return all the records where field is less than 5--except for those records where field is Null. Now, that might be what you want, but it might not. If you want to include null values, include Is Null in the condition as follows:

WHERE field < 5 OR field Is Null

#9: Excluding null values in a group

 

Jet SQL's GROUP BY clause doesn't eliminate null values from the grouped results. Instead, Jet sorts null values to the top or the bottom of the result set, depending on the sort order. For instance, the following query includes records where the Region field is Null:

SELECT FirstName, LastName, Region
FROM Employees
GROUP BY Region

The result isn't right or wrong, it just might not be what you want. You must explicitly exclude null values. In this case, you'd add a HAVING clause as follows:

SELECT FirstName, LastName, Region
FROM Employees
GROUP BY Region
HAVING Not (Region) Is Null

There's no specific method for explicitly excluding null values. The statement's purpose will dictate the solution.

#10: Using null to spot normalization problems

 

A null value is an acceptable value. However, too many null values often point to an unnormalized table. For instance, if you store customer phone and fax numbers, you might end up with a lot of empty fax number fields. (Even if you have no null values, your table's still not normalized properly, in this case.)

To normalize the phone data, you'd add a table that includes three fields: the foreign key column that relates the phone record to its corresponding customer, the phone number type, and the phone number. The phone number type would identify the phone number as an office, fax, home, cell, and so on. Then, you'd enter phone number records only when appropriate, eliminating null values. If the customer has no fax, there'd be no record for a fax number.

Since you're all up to speed on Vista's SideShow feature, it may be time to check out Vista's other new introductions—SuperFetch and ReadyBoost.

SuperFetch tracks what kind of apps you use and loads them into RAM so they can be launched faster. ReadyBoost uses a USB 2.0 flash drive as cache in order to speed up some virtual memory transactions.

But how does this help you?

SuperFetch is a sort of super-cache. If you start and close a certain application multiple times a day, the regular Windows XP cache will make that faster upon subsequent launches. However, if you reboot and if you launch other apps, this caching data is lost. SuperFetch analyzes your behavior over multiple sessions, and if you open, say, your email, browser, and chat in the morning, SuperFetch will load those apps into memory as soon as you start up your PC.

Sure, loading more data into the faster RAM from the slower hard drive helps a lot, but the problem with this is that you'll need more "free" RAM in order to take advantage of SuperFetch. Tom's found that 2GB and up gave the best results in caching.

ReadyBoost, on the other hand, puts seeks to help out the slow hard drive by caching some data into USB 2.0 flash. By using a ReadyBoost-capable memory stick, you can take advantage of the near-instantaneous seek times on flash drives to get you your data quicker.

In Tom's Hardware tests, ReadyBoost decreased application launch times dramatically on a low-RAM system when partnered with SuperFetch. SuperFetch knew which apps to load onto the faster USB 2.0 flash drive, which then provided quicker access.

The gains when you have a machine with 1GB of RAM diminish, and diminish further when you have 2GB of RAM. However, if you've still got a ReadyBoost-capable drive, you'll still see benefits even on a 2GB machine.

Both of these features tell you that Vista works a lot better when you throw more RAM at it, and Tom's recommends at least 2GB if you're going to take advantage of the caching benefits of SuperFetch

 

 

 

YouTube is a video-sharing website that is changing American culture. It seems that every day, some short video clip on YouTube makes headline news. Here's the story behind the phenomenal success of YouTube, and some tips on how you can join the fun...

Returns the number of date and time boundaries crossed between two dates

SQL DATEDIFF Syntax
DATEDIFF ( DatePart , StartDate , EndDate )

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-05'

SELECT DATEDIFF(Year, @StartDate, @EndDate) AS NewDate
Return Value = 0 Year


SELECT DATEDIFF(quarter, @StartDate, @EndDate) AS NewDate
Return Value = 1 quarter


SELECT DATEDIFF(Month, @StartDate, @EndDate) AS NewDate
Return Value = 2 Month


SELECT DATEDIFF(dayofyear,@StartDate, @EndDate) AS NewDate
Return Value = 61 day


SELECT DATEDIFF(Day, @StartDate, @EndDate) AS NewDate
Return Value = 61 Day


SELECT DATEDIFF(Week, @StartDate, @EndDate) AS NewDate
Return Value = 9 Week


SELECT DATEDIFF(Hour, @StartDate, @EndDate) AS NewDate
Return Value = 1464 Hour


SELECT DATEDIFF(minute, @StartDate, @EndDate) AS NewDate
Return Value = 87840 minute


SELECT DATEDIFF(second, @StartDate, @EndDate) AS NewDate
Return Value = 5270400 second


DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-06-06'

SELECT DATEDIFF(millisecond, @StartDate, @EndDate) AS NewDate
Return Value = 86400000 millisecond

Booting a computer from your USB flash drive may seem like a daunting task, but it is actually quite easy. With the right equipment and some basic knowledge, this very useful technique can be taken advantage of in all sorts of different circumstances.

The first thing you will need to do this is a compatible USB flash drive. Most drives are bootable but some are not, so it pays to ask before making a purchase or to do a bit of research online before picking your drive. This is not something the average salesperson will know nor do most companies make it clear on the packaging, so the internet is your best source here. Try to find a drive which has been used successfully in the past, like Corsair's Flash Voyager. The size of the drive is going to be an issue depending on your requirements. If you need to place an entire operating system on the drive, for example, you may need something a bit larger than what you have lying around.

The next step is to make sure that the motherboard which you are working with supports USB booting. To do this simply enter the BIOS (this can usually be done by press the Delete key while the computer is posting) and go into the menu selection titled something like, "Advanced Features". This process is a bit different for every BIOS so you may have to search a bit. Once here look for the boot devices, which will be placed in order: 1st, 2nd, 3rd, and so on. Normally the computer will attempt to boot from the CD-ROM or a specific hard drive first, but you want to change this to the USB drive. The proper selection to do this varies depending on your BIOS version but it be USB RMD-FDD, USB ZIP, USD HDD, USB CD-ROM, or something close to these. Once these is chosen as the 1st boot device you can move your hard drive and/or optical drive down the line (so they will be used if a USB device is not present) or remove everything (so that the computer will only boot from USB). A little trial and error may be needed here to make sure you have chosen the right boot device.

With the BIOS properly configured all that is left to be done is to save the settings and exit. A quick restart and you will be ready to boot from USB. Of course, this is supposing that your USB flash drive already contains the appropriate data.

This part is either very easy or a bit tricky depending on what you need to do. To properly format the USB flash drive you can either use a utility program or use a specialized tool within the software with which you will be booting. Many USB drives will include some sort of software utility which will allow you to format the drive and make it bootable. If your drive did not your drive did include any software HP has a program available which is quite popular. You can find it here. When formatting make sure to use the FAT file system.

If you looking into booting Linux from a USB flash drive there are a few different options, but I have had the highest success rate with Syslinux. This is a lighweight Linux bootloader mainly used for floppy media. This can be useful for distributions of Linux which are tough to get to boot from USB, but some do not need this. Puppy Linux is extremely easy to get to boot from USB and it actually has a built in tool for formatting a USB drive to do this. Other Linux distributions, like Feather Linux and even Fedora Core 3, have been known to work as well.

The quickest way to install Puppy Linux onto a USB drive is to first use it as a Live CD. With is running as a Live CD all you have to do is:

Setup > Install onto a USB stick > (Answer the questions)

After this is over your USB flash drive will be ready to boot Linux onto practically any system. Puppy Linux is the easiest, but the a similar process can be applied to many other distributions.

Booting from your USB stick is quite simple once you know the basics. When you want to start your computer in DOS mode at the A: prompt, boot into Linux, or something more complex, it just takes a little bit of hardware, a few programs, and some patience.

DISCLAIMER: This process is a one-way process and all steps must be taken to ensure that data is backed up before attempting. The author is in no way responsible for loss of data, damage to disk, data and the computer as a whole.

Before you begin, please make sure that you know the following terms :

1. FAT : File Allocation Table is a computer file system architecture developed by Bill Gates and Marc McDonald in the late 1960s and 1970s. It is the main file system in use for MS-DOS and the earlier versions of Windows up to but not inclusive of Windows NT.
2. NTFS : New Technology File System is a relatively newer file system which is used by Windows NT, server 2003/2008, XP, and Vista. It is more powerful than the FAT system and employs newer security features and enhancements.

Now onto the process itself :

1. Backup all important files, folders to an external hard drive or DVD.
2. The conversion process must be initiated in the command prompt so :
   • Click Start -> Programs -> Accessories -> Command Prompt.

     OR

   • Click Start -> Run -> Type cmd in the box -> Press enter.
3. By now the command prompt should have opened up. In case you didn't know, the command prompt is the black screen with white text.
4. Now type vol followed by the name of the drive you wish to convert
   • Example : For D drive type vol d:
5. A name and a serial number will be displayed on the screen. Note down the name as you will need it later.
6. Now type convert (drive letter) /fs:ntfs.
   • Example : To convert D drive to ntfs type convert d: /f:ntfs
7. In the case that your operating system is on the drive you want to convert, the program will ask whether you would like to schedule the conversion for when the system is restarting. If you would like to do so, Click Yes.
8. Next, the system will prompt you for the label name of the drive you wish to convert. It will appear as follows :

   The type of file system is FAT.

   Enter the current volume label for drive d: (or whichever drive you chose)

9. Enter the volume name you noted down earlier and press ENTER.
10. Once the conversion process is complete you can quit command prompt by typing exit and pressing enter.
11. Congrats on your new improved NTFS drive!!