You don’t have to tell Access a value’s data type for Access to get things right. Access is good at typecasting when you don’t explicitly specify a data type. You can see this for yourself by solving a simple math problem in the Immediate window. The expression 1 + 3 returns 4, just as you’d expect. Access correctly assumes you’re adding two numeric values. Its as it should be and there are no surprises.

The same is true with text characters, except Access can’t sum them. Instead, Access concatenates the two string values and in this case, returns 13. Again, Access correctly assumes you’re combining two strings. Of course, the delimiters (” “) help identify the two numeric characters as text.

jan2008blog1fig2r.jpgCombining two different data types might not have the expected results. For instance, the expression 1 + “3” returns 4. That’s because Access typecasts the string 3 as a number, despite the delimiters. Counting on Access to return an error in a case like this, is just asking for trouble down the line.

This type of problem often exposes itself during the testing phase, but it might not. The best way to handle this type of conversion confusion is to prevent it by:

  • Dimming VBA variables appropriately, using the smallest data type to get the job done.
  • Using Debug.Print statements to display the results of TypeName() and VarType() functions, so you always know a variable’s data type. You’ll use these functions while debugging, but it won’t hurt to leave them in your code.
  • Using the & operator to concatenate instead of +, to avoid confusion. The expression 1 & “3” returns 13, not 4.
  • Using an unbound control’s Format property to specify a data type.