Using LIKE and internal String UpperCase in two code examples 1

[Isadore]

I spent quite a bit of time looking up two relatively simple solutions for using LIKE in a vba query code statement and internalizing the Uppercase of a single letter within a string and provide this post for someone searching the dbase for a similar problem.

Note: I am not an professional programmer so there is probably a better way to do this.

Problem 1: Apply UCase to internal string member

[txtA] = XP_123456
[txtB] = exampleB
[txtC] = myexampleC

What I need: " >exaMye_123456 " <-- minus the " " (quotes)

Note that "M" is uppercase, "exa" is the first 3 letters of txtB and "Mye" is the first 3 letters of txtC and "123456" the last 6 characters of txtA.

Solution :

Dim strTest As String

strTest = ">" & Left([txtB], 3) & Left([txtC], 3) & "_" & Right([txtA],6]

This yields >examye_123456

Now need the internal "M" uppercase:

strTest = Left(strTest, 4) & UCase(mid(strTest, 5, 1)) & Right(strTest, 9)

This yields:  >exaMye_123456   <== result

The second problem is the application of a simple "LIKE" string used in VBA code on an event handler:

Dim intCt As Integer
Dim strspp As String

Generally I first determine if there is a hit using intCt and DCount, and if so, look up the string (avoids NULL returns).

Search string is entered on the form into [txtSearch]

intCt = DCount ("myName", "tblA", "myField1 Like '*" & [txtSearch] & "*'")  

If intCt > 0 Then   
  [txtEnterHere] = DLookup("myName", "tblA", "myField1 Like '*" & [txtSearch] & "*'")  
  Exit Sub
End If

Simple enough; hopefully it may help someone down the road - and if there is a simpler way that too may be provided.

cheers!

 

[Isadore]

Dim strspp As String wasn't required in the 2d example; my mistake; I replaced it with a text box on the form instead.

 

[PHV]

Why not simply this ?
1)

strTest = ">" & Left([txtB], 3) & UCase(Left([txtC], 1) & Mid([txtC], 2, 2) & "_" & Right([txtA],6)

2)

[txtEnterHere] = Nz(DLookup("myName", "tblA", "myField1 Like '*" & [txtSearch] & "*'"), "?")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Isadore]

PHV - Thanks for the update. Really appreciate your time on this - will give it a try!