MS Access query assistance needed

Posted on 2007-07-24
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 11

    Expert Comment

    SELECT myField from mytable where mid(myfield, 3, 1) = '1'
    LVL 84

    Accepted Solution

    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 & ")"


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Introduction While answering a recent question ( 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…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    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…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now