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

Dig Deeper on Oracle and SQL

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.

Please create a username to comment.