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
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