[AccessD] Field data type Binary and creation of specific field data type

Hi all

Did you know that a Binary field type is available with a length of 510 bytes?
It cannot be created in the table designer, only via code - or by a make table query:

  SELECT DISTINCT 
    Country, 
    Null As BinaryField 
  INTO 
    Countries 
  FROM 
    Customers;

Explanation here:

  http://support.microsoft.com/default.aspx?scid=kb;en-us;320435

You might get the idea that it will hold strings of length beyond 255 chars but no, it will not. They will be truncated, 255 is still the maximum.
It mostly looks like a fixed length "Tiny OLE Object" as it seems to hold a fixed length byte array.
So what's the purpose if this field type? Or how to use it in a way not offered by other data types? I don't know. 
Any suggestions?

Note that if you wish to create a table with a field of a certain data type, IIf() comes in handy:

  SELECT DISTINCT 
    Country, 
    IIf(True, Null, CCur(0)) As CurrencyField 
  INTO 
    Countries 
  FROM 
    Customers;

Note, too, that the above doesn't work for Access 97. Not even

  SELECT DISTINCT 
    Country, 
    IIf(False, Null, CCur(0)) As CurrencyField 
  INTO 
    Countries 
  FROM 
    Customers;

will create CurrencyField of type Currency but as Text(!). Only

    CCur(0) As CurrencyField 

will do, where you afterwards will have to update CurrencyField to Null.

In any case, if you wish to create a Date field, this method is easier:

  SELECT DISTINCT 
    Country, 
    CVDate(Null) As DateField 
  INTO 
    Countries 
  FROM 
    Customers;

as CVDate() accepts Null.

/gustav