Casting in Access sql 1

[Rajesh99]

I have a query:

select 'True' as print_flag, cust_id from cust;

How do I cast print_flag to cast to Boolean(true/false or yes/no)? Seems this comes up as text when I base my form on this query.

 

[RickSpr]

It comes up as text because you put 'True' in quotes. Remove the quotes and you won't need to cast it.

To answer the question you asked, though, you could have used:
select CBool('True') ...

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Rajesh99]

Thanks but seems when maketbale is used on the query it returns number as datatype than boolean. I am trying to use check box here so type needs to be yes/no by default for that to work.

 

[RickSpr]

Ah, it's a MakeTable query! I'm afraid you can't get Access to create a Boolean column using a MakeTable query. But there are a couple of alternatives you could consider.

1. Run the MakeTable, and then modify the field definition from Number to Yes/No. (You can only do this in the user interface. DDL doesn't support changing a column data type.)

2. If you need an all-code solution, either
2A. Create the table in code using DDL, and then use an Append query instead of the MakeTable query, or
2B. Use the MakeTable query, then use DDL to add a Boolean column, then run an Update query to copy the Number column to the Boolean column, then use DDL to Drop the Number column.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein