MS Access query assistance needed

Posted on 2007-07-24
Medium Priority
Last Modified: 2010-05-18
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.


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:


Question by:JMO9966
LVL 10

Expert Comment

ID: 19556688
SELECT myField from mytable where mid(myfield, 3, 1) = '1'
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 19556699
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 & ")"


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month14 days, 13 hours left to enroll

839 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