[AccessD] FW: VBA Function

Rocky Smolin rockysmolin at bchacc.com
Wed Aug 9 09:29:43 CDT 2017


Paul

Will forward to client and report back. I'm not running SQL here so I can't
test it.

r

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Paul Hartland via AccessD
Sent: Wednesday, August 09, 2017 7:03 AM
To: Access Developers discussion and problem solving
Cc: Paul Hartland
Subject: Re: [AccessD] FW: VBA Function

not sure about this but does VBA allow the use of % in the like statements,
I thought VBA only used ? (single characte) or * (multiple
characters)....that being said have you tried the following approaches....

1
 stEmail = stEMP
rs.Source = "SELECT LName, FName From tClients " & _
        "WHERE [E-Mail] like %'" & stEmail & "'%"

2.
 stEmail = stEMP
 rs.Source = "SELECT LName, FName From tClients " & _  "WHERE [E-Mail] like
*'" & stEmail & "'*"

Paul

<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
paign=sig-email&utm_content=webmail>
Virus-free.
www.avg.com
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam
paign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On 9 August 2017 at 14:51, Fred Hooper <fahooper at gmail.com> wrote:

> 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
>>
>>
>>
>>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



--
Paul Hartland
paul.hartland at googlemail.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list