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?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor 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
 
ClifCommented:
SELECT myField from mytable where mid(myfield, 3, 1) = '1'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.