Yes/No field in Make Table Query

[bayman]

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.

 

[Elana]

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

 

[bayman]

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

 

[Elana]

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?

 

[bayman]

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.

 

[Jack Cowley]

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).]

 

[bayman]

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.