I DON'T like "LIKE"

[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...

 

[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!

 

[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!!

 

[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?