Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

dlookup using length of a criteria string

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?
1 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.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now