dlookup using length of a criteria string

Posted on 2014-07-11
Last Modified: 2014-07-12
I have a table tCl..... this table contains products each product is available in differnt pack quantities.


AC Blue 12pk
AC Blue 24PK
AC Blue Lrg 6PK
AC Blue Lrg 12PK

with some entries the pack info is removed for some orders.  I have logic to remove that part not problem.

The issue is that when I need to go back and find the nearest match missing the pack info.

Currently using a Dlookup which is working fine 95% of the time with:
Nz(DLookup("CL", "TCl", "CL Like '" & Me.ODBrand & "*'"), "")

So if a user has a previous order like AC Blue I would like to return AC Blue in any pack.... However sometimes a scenario comes up where its not pulling up what I would expect.  Ex:  AC Blue pulls up AC Blue Lrg 6PK

I know its becuase of the like but I can't user = since I don't have the pack info.

I was trying to implement a check if found based on using something like a dlookup with with left based on the length of ME.ODBrand.  I dont't know if this is possible or the best method.

Question can Dlookup work with a left like:
Nz(DLookup("CL", left("TCl",len(ME.ODBrand), "CL = '" & Me.ODBrand & "'"), "")?

Perhaps a better solution?
Question by:thandel
    1 Comment
    LVL 30

    Accepted Solution

    DLookup returns first value depending on sort order of values.

    To list all matching use a query.

    All depends on how you implement the solution.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    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…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    737 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

    23 Experts available now in Live!

    Get 1:1 Help Now