Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Select query help needed

Posted on 2007-10-09
7
Medium Priority
?
229 Views
Last Modified: 2010-03-20
I have a field that I query that is a Text field that stores three value all of which will either be a zero or a one.

111
101
010
001
100

You get the idea.  I need to write a MSAccess and a SQL query to return any records that have the second value set to a 1.  In the example above, this would return two records (the first and third records listed).

Can someone give me a hand on the syntax for this query in MS Access and SQL?

Thanks,
JMO9966
0
Comment
Question by:JMO9966
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 20041042
SELECT *
FROM YourTable
WHERE YourField LIKE "*1*
0
 
LVL 61

Expert Comment

by:mbizup
ID: 20041045
Need a closing quote:
SELECT *
FROM YourTable
WHERE YourField LIKE "*1*"
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20041081


select <column_name> from <table_name> where substring(<column_name>, 2, 1) = 1
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 77

Accepted Solution

by:
peter57r earned 1000 total points
ID: 20041093
Sorry Miriam - you need ? not *

SELECT *
FROM YourTable
WHERE YourField LIKE "?1?"
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20041102
Hello JMO9966,

SELECT *
FROM YourTable
WHERE YourField LIKE '_1%'



Aneesh R
0
 
LVL 61

Expert Comment

by:mbizup
ID: 20041119
>stores three value
True... I'm assuming that the field length is exactly three characters, where the * would work.  If it is longer and a test for numeric characters is needed, this could be used too:

 WHERE YourField LIKE "#1#"

I'm also assuming that the back-end is Access.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 20041996
I don't know if your field is numeric or alphanumeric but the query will work either way. For MS-SQL will be:

SELECT
      *
FROM
      YourTable
WHERE
      substring(cast(YourFiled AS varchar),2,1)='1'
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question