yes/no vs. 0 & -1

[parkfairfax]

I am using a sql statement to create a table at runtime in my access database.

strSQL2 = "create table test_new ([ID Number] Integer, Title text(255), [Report Number] text(50), [Date] date, [On CD] logical, Collection text(10));"

The field [On CD] is my logical field. This works fine; however; I am stuck with 0/-1 as my true false choices, and I really want a yes/no format. I cannot find the format code to add to my sql string.

Does anyone have the answer for me?

 

[SteveR77]

Try embedding an IIF statement for the [On CD]

IIF([On CD]=True,"True","False&quot

HTH,

Steve

 

[parkfairfax]

I guess you could do something like that, but that's not what I'm really looking for. Just like you can define your text size for a text type field, ie. [title] text(255), I believe that there has to be a format to set up yourlogical fields as yes/no for example.. [On CD] logical(yes/no), except I know that doesn't work.

If anyone can think of anything please let me know.

 

[KornGeek]

I wouldn't worry about how it is stored in the table. You can use formatting in forms, reports, etc. to display it in the format you want.

No matter how you define the field, I believe (although I might be mistaken) that Access still stores the values as 0 or -1.

 

[parkfairfax]

Thanks, your suggestion gave me an idea. Rather than fix it at runtime, I changed the formatting in the form that the table draws its data from.

 

[JeremyNYC]

Why are you making a table instead of just doing a select table?

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.