MS ACCESS binary field and make table query

The MS ACCESS binary field is basically a BLOB field if you familiar with DB2 or DEC terminology. It is simply a field that can hold a chunk of data of some kind of format. It can hold video, audio, text ... etc. Or you can think of it as un-typed field.

When using MS ACCESS (till ver. 2007) through its normal user interface to create a table, ACCESS do not offer users the option of creating fields of type 'binary' even though it does offer types like OLE object that allows users to store picture, video, ... etc. Binary fields, however, can be create via making table queries which I discovered by accident.

For more information about the BLOB and MS ACCESS binary field please see:

Wikipedia on BLOB
MS ACCESS binary equivalence in other DB (MS article).
Binaries in MS SQL server (MS article)
How binary may be used in VB/ACCESS (generation-nt.com)

You can create a binary field by using [Yr(yyyy)=] in a make table query, MS ACCESS will create a binary field for it. It seems that the binary type is the default for make table query unless the ACCESS can derived the type from other information -for example, the type of the data source. In general, the SELECT statement in the make table query will provide these information. So the SELECT Fld1, Fld2 ... FROM SrcTbl INTO TblCreated ... will create fields based on the type of FldN in the SrcTbl. It is when you use the syntax: [Yr(yyyy)=]  in a create table query that the ACCESS will run into troubles on determine the correct field type. You can overcome this by providing additional information in the form of the type of a function. For example, with CLng([Yr(yyyy)=]), ACCESS will make the field a type of long-integer.

Once the field is created, it seems to me, the field will take any kind of value you inserted into it even though the data may not show in the way you expected. For example, if you insert a integer to the binary field, it may look like you inserted an ASCII code and shown as a character.

I do, however, have trouble to understand the failure of some of my queries that create a binary field. One of my queries is a complex query that invoke several other queries as its source. The query contains prompting syntax (i.e. []) for various purposes. Some of them are used to filter the data source and one of them is used to create the binary field. That query successfully create the table with binary field when all records are filtered out. However, when valid records were to inserted into the created table via that same query, error was reported that there are type conversion errors and the binary field was inserted with NULL. The value to be inserted was the value assigned to the prompting syntax in the form of integer. This is what bring me to the investigation of the ACCESS binary field originally. Unfortunately, as mentioned earlier, binary fields seem to be able to take in any type of data except in my particular cases (There are actually two very similar queries that failed).

Life goes on, so I will leave this mystery for someone else to solve.