Problem solve Get help with specific problems with your technologies, process and projects.

Boolean columns

I have a VB-Access program. In the database there is a table called Products that has, among others, three columns...

named Product_price, Product_Onsale (this is a boolean column) and Product_OnsalePrice. If the Product is on sale then the boolean is set to true. Now I want to pull out the Onsale price when the boolean is true, but how do I do that?

Microsoft Access has a Yes/No datatype which is very similar to a boolean datatype, but falls short on a couple of points. For one thing, you cannot use the SQL standard IS TRUE or IS FALSE predicates in Access. Querying and updating are a challenge too, since you have to use values that aren't very intuitive.

In your particular situation, to test for Yes, use:

 where Product_Onsale <> 0

Note that you could also use:

 where Product_Onsale = -1

Makes no sense to me, but there you go.

Since few other databases implement a boolean datatype (despite this being a standard), it might be better to use something else when designing new tables. A more portable solution is to define your column as CHAR(1) and populate it with values 'T' and 'F', or SMALLINT with values 1 and 0.

For More Information

This was last published in December 2002

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.