MS Access query assistance needed

Hello,
 
I'm trying to write an MS Access query that will search a 3 character text field in a Table and return any rows that have a one (1) as the third character.  This field store three boolean indicators as a String without any seperators.

examples:

Char 1 = Default Main
Char 2 = Default Ship
Char 3 = Default Billing

This is for a tables that holds all Addresses listed for a Customer or Vendor.  There can be only one record with Char 1 in this field set to 1 (true) and same for Ship and Billing, only one record would have Char 2 as 1 (true) for this Customer or Vendor ID.

I want my query to link to this table by Customer ID and then return the row that has Char 1 as a 1(true).

examle values in this field would be:

111
011
001
010
110
etc...

Thanks,
JMO9966
JMO9966Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ClifCommented:
SELECT myField from mytable where mid(myfield, 3, 1) = '1'
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So you want a record that has the LAST (i.e. third) character as a 1?

SELECT tblA.SomeField FROM AddressTable AS tblA INNER JOIN CustomerTable AS tblB ON tblA.CustomerID=tblB.CustomerID WHERE (Right(tblA.YourColumnName,1)='1')

If you also need to limit by CustomerID:

SELECT tblA.SomeField FROM AddressTable AS tblA INNER JOIN CustomerTable AS tblB ON tblA.CustomerID=tblB.CustomerID WHERE ((Right(tblA.YourColumnName,1)='1') AND (tblA.CustomerID=" & YourCustomerIDVariable & ")"



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.