dlookup using length of a criteria string
Posted on 2014-07-11
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?