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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.