Can you have an array as multiple criteria. e.g. HAVING (TableX.Email=""" & EmailSearch & """);" EmailSearch has more than 1 value (1 Viewer)

bignose2

Registered User.
Local time
Today, 12:37
Joined
May 2, 2010
Messages
219
Hi, Hope title is not too confusing.

EmailPasteTable is a list of emails I have been sent.

I call this function from a form & elsewhere

LatestEmailUse("JoeBloggs@hotmail.com")
It shows that lastest time e.g. joebloggs@hotmail.com emailed. also how many times but not so important.

I would like to be able to use LatestEmailUse("JoeBloggs@hotmail.com","JoeBloggs@btinternet.com") also perhaps Joebloggs@yahoo.com so could be 1 or 2 or or poss 4
Basically I think an array somehow.

I read something about using IN and mention of arrays but no examples I could really use.
I know I could use OR but again if different numbers of criteria.

Public Function LatestEmailUse(RegIDSearch)
Dim strSQL As String
Dim R

' this just matches on email address

strSQL = "SELECT EmailPasteTable.Email, Count(EmailPasteTable.Received) AS CountOfReceived, Max(EmailPasteTable.Received) AS MaxOfReceived " & vbCrLf & _
"FROM EmailPasteTable " & vbCrLf & _
"GROUP BY EmailPasteTable.Email " & vbCrLf & _
"HAVING (EmailPasteTable.Email=""" & RegIDSearch & """);"

Set R = CurrentDb.OpenRecordset(strSQL)

If R.RecordCount <> 0 Then

LatestEmailUse = "(" & R!CountOfReceived & ") " & Format(R!MaxOfReceived, "dd-mmm-yy")
Else
LatestEmailUse = ""
End If

R.Close

End Function
 

June7

AWF VIP
Local time
Today, 04:37
Joined
Mar 9, 2014
Messages
5,423
vbCrLF is not necessary when concatenating SQL string.

A query can use string within IN() function like:

SELECT * FROM table where ID IN(1,2,3);

or

SELECT * FROM table WHERE sometextfield IN('abc','def','ghi');

An array or CSV string can be passed to function.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:37
Joined
May 7, 2009
Messages
19,169
this will return a Collection:
Code:
Public Function LatestEmailUse(ParamArray RegIDSearch() As Variant) As Collection
Dim strSQL As String
Dim R, C As New Collection
DIM sEmails As String, i As Integer

For i = LBound(RegIDSearch) To UBound(RegIDSearch)
    sEmails = sEMails & Chr(34) & RegIDSearch(i) & Chr(34) & ","
Next
sEMails = Left(sEmails, Len(sEmails)-1)
' this just matches on email address

strSQL = "SELECT EmailPasteTable.Email, Count(EmailPasteTable.Received) AS CountOfReceived, " & _
"Max(EmailPasteTable.Received) AS MaxOfReceived " &  _
"FROM EmailPasteTable "  & _
"WHERE EmailPasteTable.Email IN (" & sEMails & ") "
"GROUP BY EmailPasteTable.Email;"

Set R = CurrentDb.OpenRecordset(strSQL)

With R
    If Not (.BOF And .EOF) Then
        .MoveFirst
    End If
    While Not .EOF
        C.Add !EMail.Value & " (" & !CountOfReceived.Value & ") " & Format(!MaxOfReceived.Value, "dd-mmm-yy")
        .MoveNext
    Wend
    .Close
End With
Set R = Nothing
LatestEmailUse = C

End Function

you call it like this:
Code:
Dim c As Collection
Dim i As Integer

' can be 1 up to 99 or more email
Set c = LatestEmailUse("email1@gmail.com", "name2@yahoo.com")
For i = 1 To c.Count
    Debug.Print c.Item(i)
Next
 

Users who are viewing this thread

Top Bottom