I DON'T like "LIKE" (1 Viewer)

[NauticalGent]

So I am really stumped on this one. Attached is a scaled-down version of a DB I inherited. The main form (not included) is based on a query that is determined by the user's position (EmployeeType, 1-5) and BuyerCode.

If the EmployeeType is 1 or 2, then show only the records that match their BuyerCode
If the EmployeeType is 3, then show all the records that match the first 4 characters of their BuyerCode
If the EmployeeType is 4, then show all the records that match the first 3 characters of their BuyerCode
If the EmployeeType is >= to 5 then show all the records

I made a Standard Module to build the criteria for the "qryGetBuyer" query but it only works for BuyerCode 1 and 2 - everything else returns no records. What is even more baffling (to me anyway!) is that I can copy-n-paste the Debug.Print statement into the query grid and it returns the correct record set.

I am obviously overlooking something simple but I have tried everything short of sacrificing my dogs at midnight.

Can somebody please save my pets?

 

[theDBguy]

Hi. When I did a quick test of your function, this is what I got.

I think what I should have gotten was something more like this:

Like "abc*"

If so, try modifying your code to add the quotation marks.

Hope that helps...

 

[Pat Hartman]

Simple. You cannot change the structure of a QueryDef by using a parameter. The relational operator would be = or Like. They would generate different execution plans since = would use indexes to find a record whereas Like would probably use a full table scan. Your criteria is being interpreted as

Where FLDR_OWNER = "like abc*"

A far better solution is to split the code into it's constituting parts. RULE #1 of relational database design - NEVER mush multiple attributed into a single column.

You're going to need to evaluate the data inside the function and return true or false for select or ignore. OR you can go with an ugly IIf()

If the BE is Jet/ACE a function would probably be OK. However, if the BE is SQL Server, the function couldn't be passed to the server so that would force Access to ask the server to return the full table and then Access would need to apply the function locally one record at a time. For an ODBC BE, I would go with the ugly IIf().

 

[Gasman]

I got it to work with a Tempvar?

Like [TempVars]![Crit]

as your criteria

and

Public Function GetBuyerCode(intType As Integer, strBuyerCode As String) As String
   
    GetBuyerCode = vbNullString
       
    Select Case intType
        Case 1, 2
            GetBuyerCode = strBuyerCode
        Case 3
            GetBuyerCode = Left(strBuyerCode, 4) & "*"
        Case 4
            GetBuyerCode = Left(strBuyerCode, 3) & "*"
        Case Else
            'Show All
    End Select

Debug.Print GetBuyerCode
TempVars("Crit") = GetBuyerCode
Debug.Print TempVars!Crit.Value
End Function

Edit: You will need "*" for the show all section, if you do it this way?
HTH

 

[theDBguy]

Sorry, I think I may have jumped the gun. Just for fun, please see the attached modified copy of your db. Cheers!

 

[Pat Hartman]

Like is an inefficient operation and should be avoided except where necessary. I use it in apps where the user needs to search by parts of names or address but in this case, the BuyerCode is a code and the only reason that Like is required is because the table is not properly normalized.

 

[NauticalGent]

If so, try modifying your code to add the quotation marks.

Be there, done that...

Your criteria is being interpreted as

Where FLDR_OWNER = "like abc*"

Thanks Pat, that makes since. I actually made a where condition for the OpenForm argument that DID work, but it caused other problems.

Like [TempVars]![Crit]

Funny you mentioned TempVars Paul, that is what gets passed to the Function, but I removed then to make this thread less complicated. Your solution is very similar to what DBG sent on his follow-up.

Just for fun, please see the attached modified copy of your db.

And there you have it. For those who do not want to download DBG's solution, similar to what Paul suggested, he placed the "Like" operator BEFORE the Function - now WHY didn't I think of that???! (don't answer, rhetorical question). It works great, once again, I thank you as do Charlie and Jackson - the two Choc Labs who were slated to meet their fate at midnight.

Like is an inefficient operation and should be avoided except where necessary. I use it in apps where the user needs to search by parts of names or address but in this case, the BuyerCode is a code and the only reason that Like is required is because the table is not properly normalized.

Agree wholeheartedly and you have not seen as bad a design as this DB. Unfortunately, I am racing against the clock and will have to come back to that once I kill the closest alligators to the boat.

Awesome job as usual, thanks again!!

 

[Pat Hartman]

and you have not seen as bad a design as this DB.

You have no idea the horrors I have seen I still have nightmares about a couple of them. Others were just too funny for words. One of my all time favorites was what seemed to be a trivial task. I had to add a column to a report. How tough could that be. There were a dozen or so reports and I quickly found one with the correct header. Two minutes. I added the column and ran the report it worked so I saved it and closed it and went to the menu to run it and got the old version?? OK. maybe I really didn't save the change. Went back and did it again my change wasn't in the file. worked fine but when I ran it from the menu I got the old version. Now I have to start examining code. I eventually found the macro that was the culpret. There were actually two versions of the report. But instead of the macro saying if x run reportx else run reporty, it said if x, replace report with reportx. If y replace report with reporty. Run report. I kept changing report and the macro kept clobbering my change. Good thing the change was trivial or I might have gone on a search and destroy mission to find that idiot and shoot him before he could make another database.

 

[CJ_London]

Bit late to the party but can you not use the iif and choose functions?

SELECT FLDR_OWNER, FOLDER_NUMBER
FROM tblWIP
WHERE FLDR_OWNER Like IIf([enter employee type]<5,Choose([enter employee type],[Enter BuyerCode],[Enter BuyerCode],Left([Enter BuyerCode],4) & "*",Left([Enter BuyerCode],3) & "*"),"*")

for buyercode APAB.24

for employee types 1 & 2 returns 8 records
type 3 returns 72 records
type 4 returns 243 records
type 5+ returns all 3180 records

 

[NauticalGent]

Bit late to the party but can you not use the iif and choose functions?

SELECT FLDR_OWNER, FOLDER_NUMBER
FROM tblWIP
WHERE FLDR_OWNER Like IIf([enter employee type]<5,Choose([enter employee type],[Enter BuyerCode],[Enter BuyerCode],Left([Enter BuyerCode],4) & "*",Left([Enter BuyerCode],3) & "*"),"*")

Thanks CJ, that worked as advertised. I tired both ways and even converted your Choose method to Switch - neither had a significant performance advantage over the other so I am going with your solution.

Thanks for weighing in!

 

[CJ_London]

No problem, because it is a criteria with a static result it is only called once, so shouldn’t have any speed impact (nor would the function)

 

[Gasman]

When I walked through the code, the function was only called once?

 

[CJ_London]

Should be - put a debug.print “x” in there and see?