[AccessD] FW: VBA Function

[Fred Hooper]

Hi Rocky,

He might try the CHARINDEX (equivalent to InStr, note reversed parameter 
order) approach:

  rs.Source = "SELECT LName, FName From tClients "&  _

         "WHERE CHARINDEX('"&  stEmail&  "',[E-Mail])>  0"

Best,
Fred

> Rocky Smolin <mailto:rockysmolin at bchacc.com>
> Wednesday, August 09, 2017 9:41 AM
> Dear Lists:
>
>
>
> I don't do much with ADO and less with SQL. The back end here is SQL.
>
>
>
> He says if he puts the code into the QBE it retrieves all the clients with
> the string in their email address. But in the code it only retrieves one.
>
>
>
> Does anyone see why the code below doesn't work for him?
>
>
>
> MTIA
>
>
>
>
>
> Rocky Smolin
>
> Beach Access Software
>
> 760-683-5777
>
> www.bchacc.com
>
> www.e-z-mrp.com
>
> Skype: rocky.smolin
>
>
>
>
>
>
>
> From: James Charlton [mailto:James at charltonweeks.com]
> Sent: Monday, August 07, 2017 11:17 AM
> To: Rocky Smolin
> Subject: VBA Function
>
>
>
> I am using the following function to find a client by an email address:
>
> It works ONLY IF a client is found that only has one email and this is it.
>
> Some clients have multiple emails listed and there are multiple 
> clients that
> have the search item in their emails.
>
> I want all the clients that have the search item in their email field.
>
> How do I fix this?
>
> jc
>
>
>
>
>
> Public Function FetchNameFrmEMailPart(ByVal stEMP) As String
>
> Dim conn As ADODB.Connection, rs As ADODB.Recordset
>
> Dim stAns As String, stEmail As String
>
> Set conn = New ADODB.Connection
>
> conn.ConnectionString = Bwq
>
> conn.Open
>
> Set rs = New ADODB.Recordset
>
> stEMP = Nz(stEMP, "")
>
> stEmail = "%" & stEMP & "%"
>
>
>
> rs.Source = "SELECT LName, FName From tClients " & _
>
> "WHERE [E-Mail] like '" & stEmail & "'"
>
> rs.ActiveConnection = conn
>
> rs.Open
>
> stAns = ""
>
> Do While Not rs.EOF
>
> stAns = stAns & rs(0) & ", " & rs(1) & vbCrLf
>
> rs.MoveNext
>
> Loop
>
>
>
> FetchNameFrmEMailPart = stAns
>
> rs.Close
>
> conn.Close
>
>
>
> End Function
>
>
>
> James Charlton
>
> Charlton Weeks LLP
>
> 1031 W Ave M14, Suite A
>
> Palmdale, CA 93551
>
> 661-265-0969
>
>
>