I have a Make Table query, in which I'm initiating a column with the value 'No' (no quotes in the query). The column is generated with an Integer data type.

In Design, I defined the format property for the column as Yes/No, yet it still generated the column as Integer.

Is there anything I can do to generate a Yes/No column in the query?

Thanks in advance.

Yes/No fields return the value 0 for no and
-1 for yes. IF you want the query to say YES or NO, then create another field in the query grid:

YESNO: IIf ([ynField]=-1 "YES", "NO")

EB

I'm looking to create a boolean column (Yes/No). Your suggestion creates a text field. Removing the quotes creates another Integer field.

A boolean value is True/False, not Yes/No. You can also use -1 and 0, which mean True or False.

I guess I don't understand why you want to store the words instead of the numeric value which means the same thing...maybe I'm not understanding what you're trying to accomplish?

I'm not looking to store the words. I'm looking to store a boolean value. Yes/No is boolean for Access. I understand the -1 and 0 for Yes and No. But in a Make Table query which includes

SELECT No as YesNoField......

It makes YesNoField a number data type (value = 0), as opposed to YesNo data type field.

Perhaps I wasn't clear in my original post. I'm trying to create a YesNo data type field in a Make Table query.

I don't guarantee that this is 100% correct. It appears to me that unless you have a boolean field in the table that you want to use as the source for your Make Table query that you cannot declare a Data Type in the query and that is why you get either a Text of Integer Data Type. You can make your table using the Make Table query and then use ALTER TABLE to make the fields Data Type a boolean field. You can, of course, make the table using code exclusively.

[This message has been edited by Jack Cowley (edited 04-09-2002).]

Why not just use the numeric value 0 rather than the text value No? Access will at least then create a numeric data type.

Pat,

I could use the numeric field, but it seems like a hack approach. I've found another work-around which is a bit cleaner.

Thanks to all who replied.