make table creates binary field rather than numeric

[Guest]

We are in the process of converting hundreds of applications from Access 97
to Access 2003. In the process, a number of our make table queries are
causing some numeric fields to be created as binary. So far, there has not
been a consistent pattern for when this happens ... some fields are correctly
created as numeric and others are created as binary even though all source
data is numeric. For now, we are getting around this by using the Val()
function in cases where a binary field is created.

I have searched on the knowledge base, but was not successful in finding any
reference to this problem. I would welcome thoughts or comments by other
users.

 

[Guest]

Hi Brian,

By binary, do you mean a boolean bit-type (yes/no) field, or do you mean an
OLE-type binary field?

If boolean, I would guess that this may be a result of the way that Access
uses the range/type of data in a field or expression to guess at the field
data type. Maybe it changed from Access 97 to 2003.

Do the subject field values consist of all 1's and 0's by any chance (or
-1's and 0's)?

In any case, you should be able to use the Access numeric conversion
functions to force the result into the desired field type. For instance,
CLng([YourField]) should output the result to a long integer field.

Hopefully that will help. Post back with more info if it doesn't.

-Ted Allen

 

[Guest]

Ted,

Thanks for the response. Somewhere along the way a field data type of
"binary" has been added (even though it's not in the drop down list of data
types, it is allowed when the table results from a make table query). Make
table queries involving some calculated fields and some fields with input
variables we want capture as part of the table have been having this problem.
In Access 97, we did not usually need to do anything special to get the
software to correctly recognize the field as numeric, text, or date. In
Access 2003, we are finding that to be safe, we need to consistently use the
functions like CDate, CDbl, CLng, CStr, etc. to make sure the fields in the
created table end up in the desired format. I guess this is simply something
we need to make a habit and watch for as we convert our applications to the
current version.

Thanks for your help.

Hi Brian,

By binary, do you mean a boolean bit-type (yes/no) field, or do you mean an
OLE-type binary field?

If boolean, I would guess that this may be a result of the way that Access
uses the range/type of data in a field or expression to guess at the field
data type. Maybe it changed from Access 97 to 2003.

Do the subject field values consist of all 1's and 0's by any chance (or
-1's and 0's)?

In any case, you should be able to use the Access numeric conversion
functions to force the result into the desired field type. For instance,
CLng([YourField]) should output the result to a long integer field.

Hopefully that will help. Post back with more info if it doesn't.

-Ted Allen

We are in the process of converting hundreds of applications from Access 97
to Access 2003. In the process, a number of our make table queries are
causing some numeric fields to be created as binary. So far, there has not
been a consistent pattern for when this happens ... some fields are correctly
created as numeric and others are created as binary even though all source
data is numeric. For now, we are getting around this by using the Val()
function in cases where a binary field is created.

I have searched on the knowledge base, but was not successful in finding any
reference to this problem. I would welcome thoughts or comments by other
users.

 

[Guest]

Interesting, thanks Brian.

The only binary data type that I was aware of in Access is OLE, but I'm
still using A2002 in A2000 compatible format, so maybe there was a change.

I should have mentioned also that another option for making sure that you
get the output field type that you need is to use delete and append queries
rather than make table queries. I almost always use the delete/append method
because all field data types are pre-defined, and the table can also be
indexed or have defined relationships. Of course, this only works for cases
where you know the table structure ahead of time, so it wouldn't work if
fields are being changed on the fly.

-Ted Allen

Ted,

Thanks for the response. Somewhere along the way a field data type of
"binary" has been added (even though it's not in the drop down list of data
types, it is allowed when the table results from a make table query). Make
table queries involving some calculated fields and some fields with input
variables we want capture as part of the table have been having this problem.
In Access 97, we did not usually need to do anything special to get the
software to correctly recognize the field as numeric, text, or date. In
Access 2003, we are finding that to be safe, we need to consistently use the
functions like CDate, CDbl, CLng, CStr, etc. to make sure the fields in the
created table end up in the desired format. I guess this is simply something
we need to make a habit and watch for as we convert our applications to the
current version.

Thanks for your help.

Hi Brian,

By binary, do you mean a boolean bit-type (yes/no) field, or do you mean an
OLE-type binary field?

If boolean, I would guess that this may be a result of the way that Access
uses the range/type of data in a field or expression to guess at the field
data type. Maybe it changed from Access 97 to 2003.

Do the subject field values consist of all 1's and 0's by any chance (or
-1's and 0's)?

In any case, you should be able to use the Access numeric conversion
functions to force the result into the desired field type. For instance,
CLng([YourField]) should output the result to a long integer field.

Hopefully that will help. Post back with more info if it doesn't.

-Ted Allen

We are in the process of converting hundreds of applications from Access 97
to Access 2003. In the process, a number of our make table queries are
causing some numeric fields to be created as binary. So far, there has not
been a consistent pattern for when this happens ... some fields are correctly
created as numeric and others are created as binary even though all source
data is numeric. For now, we are getting around this by using the Val()
function in cases where a binary field is created.

I have searched on the knowledge base, but was not successful in finding any
reference to this problem. I would welcome thoughts or comments by other
users.

 

[Guest]

P.s.,

I just did a search of the Microsoft database and found the following link:

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

which does list the binary data type, with a footnote that it is not
available through the designer interface, only through code. I guess A2003
must have made a change that allows it to be set through make table queries
as well though. It is a variable length binary field (as is OLE), but it is
limited to a smaller length.

-Ted Allen

Ted,

Thanks for the response. Somewhere along the way a field data type of
"binary" has been added (even though it's not in the drop down list of data
types, it is allowed when the table results from a make table query). Make
table queries involving some calculated fields and some fields with input
variables we want capture as part of the table have been having this problem.
In Access 97, we did not usually need to do anything special to get the
software to correctly recognize the field as numeric, text, or date. In
Access 2003, we are finding that to be safe, we need to consistently use the
functions like CDate, CDbl, CLng, CStr, etc. to make sure the fields in the
created table end up in the desired format. I guess this is simply something
we need to make a habit and watch for as we convert our applications to the
current version.

Thanks for your help.

Hi Brian,

By binary, do you mean a boolean bit-type (yes/no) field, or do you mean an
OLE-type binary field?

If boolean, I would guess that this may be a result of the way that Access
uses the range/type of data in a field or expression to guess at the field
data type. Maybe it changed from Access 97 to 2003.

Do the subject field values consist of all 1's and 0's by any chance (or
-1's and 0's)?

In any case, you should be able to use the Access numeric conversion
functions to force the result into the desired field type. For instance,
CLng([YourField]) should output the result to a long integer field.

Hopefully that will help. Post back with more info if it doesn't.

-Ted Allen

We are in the process of converting hundreds of applications from Access 97
to Access 2003. In the process, a number of our make table queries are
causing some numeric fields to be created as binary. So far, there has not
been a consistent pattern for when this happens ... some fields are correctly
created as numeric and others are created as binary even though all source
data is numeric. For now, we are getting around this by using the Val()
function in cases where a binary field is created.

I have searched on the knowledge base, but was not successful in finding any
reference to this problem. I would welcome thoughts or comments by other
users.

 

[Guest]

Ted,

We have a number that do the delete/append process as you suggest. When we
encounter this problem in conversation, we get data type conversion errors on
these ... thus, we still have to explictly define the data type in either the
make table or append query. This must be a "feature" to someone, but it's
caused us a fair amount of grief. The sporatic behavior is quite puzzling to
me.

Thanks again.

Interesting, thanks Brian.

The only binary data type that I was aware of in Access is OLE, but I'm
still using A2002 in A2000 compatible format, so maybe there was a change.

I should have mentioned also that another option for making sure that you
get the output field type that you need is to use delete and append queries
rather than make table queries. I almost always use the delete/append method
because all field data types are pre-defined, and the table can also be
indexed or have defined relationships. Of course, this only works for cases
where you know the table structure ahead of time, so it wouldn't work if
fields are being changed on the fly.

-Ted Allen

Ted,

Thanks for the response. Somewhere along the way a field data type of
"binary" has been added (even though it's not in the drop down list of data
types, it is allowed when the table results from a make table query). Make
table queries involving some calculated fields and some fields with input
variables we want capture as part of the table have been having this problem.
In Access 97, we did not usually need to do anything special to get the
software to correctly recognize the field as numeric, text, or date. In
Access 2003, we are finding that to be safe, we need to consistently use the
functions like CDate, CDbl, CLng, CStr, etc. to make sure the fields in the
created table end up in the desired format. I guess this is simply something
we need to make a habit and watch for as we convert our applications to the
current version.

Thanks for your help.

Hi Brian,

By binary, do you mean a boolean bit-type (yes/no) field, or do you mean an
OLE-type binary field?

If boolean, I would guess that this may be a result of the way that Access
uses the range/type of data in a field or expression to guess at the field
data type. Maybe it changed from Access 97 to 2003.

Do the subject field values consist of all 1's and 0's by any chance (or
-1's and 0's)?

In any case, you should be able to use the Access numeric conversion
functions to force the result into the desired field type. For instance,
CLng([YourField]) should output the result to a long integer field.

Hopefully that will help. Post back with more info if it doesn't.

-Ted Allen



:

We are in the process of converting hundreds of applications from Access 97
to Access 2003. In the process, a number of our make table queries are
causing some numeric fields to be created as binary. So far, there has not
been a consistent pattern for when this happens ... some fields are correctly
created as numeric and others are created as binary even though all source
data is numeric. For now, we are getting around this by using the Val()
function in cases where a binary field is created.

I have searched on the knowledge base, but was not successful in finding any
reference to this problem. I would welcome thoughts or comments by other
users.

 

[Guest]

Hi Brian,

I did a google search on the subject and I did see a post from Allen Browne
mentioning that Jet 4 (which A2K and on use) is not as good at guessing data
types as Jet 3.5 (which 97 uses), so I'm guessing that's what you are running
into.

You may find some useful threads under:

http://groups-beta.google.com/group...ccess.*+data+type+conversion+97+2003&start=0&

or a similar search. I didn't really see anything that offered much beyond
the suggestion of explicitly casting the results as you have already begun,
but there may be some ideas that I missed.

-Ted Allen

Ted,

We have a number that do the delete/append process as you suggest. When we
encounter this problem in conversation, we get data type conversion errors on
these ... thus, we still have to explictly define the data type in either the
make table or append query. This must be a "feature" to someone, but it's
caused us a fair amount of grief. The sporatic behavior is quite puzzling to
me.

Thanks again.

Interesting, thanks Brian.

The only binary data type that I was aware of in Access is OLE, but I'm
still using A2002 in A2000 compatible format, so maybe there was a change.

I should have mentioned also that another option for making sure that you
get the output field type that you need is to use delete and append queries
rather than make table queries. I almost always use the delete/append method
because all field data types are pre-defined, and the table can also be
indexed or have defined relationships. Of course, this only works for cases
where you know the table structure ahead of time, so it wouldn't work if
fields are being changed on the fly.

-Ted Allen

Ted,

Thanks for the response. Somewhere along the way a field data type of
"binary" has been added (even though it's not in the drop down list of data
types, it is allowed when the table results from a make table query). Make
table queries involving some calculated fields and some fields with input
variables we want capture as part of the table have been having this problem.
In Access 97, we did not usually need to do anything special to get the
software to correctly recognize the field as numeric, text, or date. In
Access 2003, we are finding that to be safe, we need to consistently use the
functions like CDate, CDbl, CLng, CStr, etc. to make sure the fields in the
created table end up in the desired format. I guess this is simply something
we need to make a habit and watch for as we convert our applications to the
current version.

Thanks for your help.

:

Hi Brian,

By binary, do you mean a boolean bit-type (yes/no) field, or do you mean an
OLE-type binary field?

If boolean, I would guess that this may be a result of the way that Access
uses the range/type of data in a field or expression to guess at the field
data type. Maybe it changed from Access 97 to 2003.

Do the subject field values consist of all 1's and 0's by any chance (or
-1's and 0's)?

In any case, you should be able to use the Access numeric conversion
functions to force the result into the desired field type. For instance,
CLng([YourField]) should output the result to a long integer field.

Hopefully that will help. Post back with more info if it doesn't.

-Ted Allen



:

We are in the process of converting hundreds of applications from Access 97
to Access 2003. In the process, a number of our make table queries are
causing some numeric fields to be created as binary. So far, there has not
been a consistent pattern for when this happens ... some fields are correctly
created as numeric and others are created as binary even though all source
data is numeric. For now, we are getting around this by using the Val()
function in cases where a binary field is created.

I have searched on the knowledge base, but was not successful in finding any
reference to this problem. I would welcome thoughts or comments by other
users.