[AccessD] FW: VBA Function

[Ryan W]

Paul since the recordset is using ADO it doesn't go through the JET
engine/translation, so '%' is quite appropriate here for wildcards since
that's what SQL Server uses.


On Wed, Aug 9, 2017 at 9:02 AM, Paul Hartland via AccessD <
accessd at databaseadvisors.com> wrote:

> 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_campaign=sig-email&utm_content=webmail>
> Virus-free.
> www.avg.com
> <http://www.avg.com/email-signature?utm_medium=email&
> utm_source=link&utm_campaign=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
>