We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

correct sql statement

Medium Priority
476 Views
Last Modified: 2011-08-18
I am trying to select all records from a column in Microsoft Access.  The column is checkbox column with Yes/No as the data type.  Is this the correct way to query a checked box in Access using sql statements?

        esql = "Select * from " & c & " Where Rearrested = 1"
Comment
Watch Question

hes
CERTIFIED EXPERT
Commented:
Just for readability I like to use
esql = "Select * from " & c & " Where Rearrested =" & vbTrue

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
The query should be like this:

SELECT <columns|*> FROM <table> WHERE <column> = <smth> ...
Hi hes,

My experience (with access 2003) is that yes/no fields will compare correctly against TRUE and FALSE, but not 1 and 0.  So, to work correctly, change your query to
esql = "SELECT * FROM " & c & "WHERE Rearrested = TRUE".

If you're doing it from VBA, this is equivalent to hes's solution.  But this method will also work if, say, used from Java via JDBC.  Or Access's builtin SQL editor.

Cheers!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.