Specifying the data type of a calculated column in a SELECT

[luckycharms]

Hi Folks,

I'm doing something like the following:

Select NOT(IsNull(a.ID)) as aMatch, a.*, b.* FROM
TableA a RIGHT JOIN TableB b 
ON a.ID = b.ID

However, the "aMatch" column resulting from the query is either numeric or text. Is there any way I can specify that the resulting column should be a boolean?

thanks!

 

[Taruz]

Hi..

if the ID field will auto number, which must return a boolean result

 

[luckycharms]

taruz, thanks for the reply, but i don't quite catch your drift...

 

[lcook1974]

Can you do that in a query?

Shouldn't it


Select a.Id as aMatch, a.*, b.* FROM
TableA a RIGHT JOIN TableB b
ON a.ID = b.ID
WHERE a.Id is not null

 

[luckycharms]

i'm interested in the isNull(a.ID) column being a boolean. As it stands, it's numeric (or text - i can't tell which).

 

[lcook1974]

Try CBool()

it's in the Build portion of the query

 

[luckycharms]

the following still doesn't seem to give me a boolean in the result set:

Select CBool(NOT(IsNull(a.ID))) as aMatch, a.*, b.*
FROM TableA a RIGHT JOIN TableB b 
ON a.ID = b.ID

 

[lcook1974]

Where are you building the query? in the Query designer or in VBA?

 

[luckycharms]

query designer

 

[spikepl]

How do you determine that the result is "not boolean"?

 

[luckycharms]

the resulting column is composed of "0"s and "-1"s. Were it boolean, it would have checkboxes I think.

 

[spikepl]

The result IS boolean. Boolean values can be formatted in different ways and 0 & -1 is one of them. I managed one time to force the display as checkboxes, but don't quite remember how. In any case, how the data is displayed in the query is not very interesting as such. In a form you can bind a checkbox to the field.

 

[luckycharms]

the *result* of the IsNull() operation is boolean, but i'm not sure if the data type of the column in the result set is boolean. I'm not totally sure how to check that, though. Perhaps I'd have to do a SELECT INTO instead of a SELECT, or something like that. Perhaps there's a better way...

 

[spikepl]

The result is boolean, it is displayed as boolean, and what is your specific worry? You still haven't stated what the probleem is with the column as displayed.

 

[luckycharms]

I wanted the column displayed as checkboxes. I thought that if Access considered the column to be a boolean data type, it would display them with checkboxes.

 

[spikepl]

Displayed where? Queries are not to be looked at. In a form or report you can bind a checkbox to that field and have your checkbox.

 

[luckycharms]

Displayed in the datasheet view. Not sure why you think queries are not to be looked at. In any case, the datatype that Access considers that column to be is important. I will be pulling data out of access using ODBC, into other programs, and it will probably be important that Access consider the column of the result set to be boolean. Again, i'm just not sure how to tell what data type access considers that column to be.

 

[spikepl]

You keep writing the same thing no matter what I write. And I don't know how to rephrase what I write, so this is a last attempt.

FORMAT of data is not the same as CONTENT or TYPE. A 1.0 or 1.00000 are both of TYPE Single, but formatted differently. A -1/0 or YEs/No are both of type Boolean but FORMATTED differently.

You DO have a Boolean variable. Queries /Tables are not meant to be displayed by themselves. User access to those is only via a Form or Report. The FORMAT of data in a query/table is irrelevaant. Only the TYPE is.

When you display the record set bound to a form or report, you can display the data in desired FORMAT by setting the FORMAT of the controls bound to the specific fields. AS for export or ODBC, a boolean is a boolean, no matter whether -1/0, True/False or Yes/no. Its value is not affected by its FORMAT.

 

[luckycharms]

spikepl,

i'm sorry you're feeling frustrated. The truth is that I feel the same way; i.e., you're not getting what i'm saying. I understand that you're trying to distinguish between the display format and data type. I agree with you - a display can show any one data type in many different ways.

My question is: how do i tell what data type that column is? Just because it is diplaying 0's and -1's doesn't mean that Access thinks the column is a boolean. Rather, it just means that the result of the isNull() operation was a boolean. For example, you can write a boolean result into a numeric-type column, and you will get 0's and -1's in that column. In that case, while the result of the operation was a boolean, the data type of the column remains Numeric.

So my question remains - how do i tell what data type Access is assigning to the column in the result set of the query? Or, tell me why I'm just not understanding you. Or, just give up on me. ;-)

 

[spikepl]

You are getting hung up on how the data is displayed. Not much I can add to what I have already said.