How to ignore if its repeated (1 Viewer)

aattas

Registered User.
Local time
Tomorrow, 02:21
Joined
Dec 24, 2014
Messages
74
Gents,

Hi, I have the following code:
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(MySQL)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
If IsNull(rs!Email) Then
rs.MoveNext

My problem that sometimes the record of the Email is repeated on the second line, the system seemed to think as a different email. How can i tell the system that if it is the same skip to another.

Thanks
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 10:21
Joined
Oct 20, 2008
Messages
512
In your recordset only select unique email addresses.

Select Distinct tblName.EmailField from tblName;

More here:
 

aattas

Registered User.
Local time
Tomorrow, 02:21
Joined
Dec 24, 2014
Messages
74
Hi easytee,

Thanks for the response , i really appreciate it. The distinct works if the fields are all similar (duplicated). But i have cases where the email and address are the same, but when it comes to findings, each is different. So the distinct cannot eliminate any one of them due to its differences. But for me, I only concern about the email addresses. Can the system look into one field (email) only without the comparison to other fields.?

Appreciate your assistant.
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 10:21
Joined
Oct 20, 2008
Messages
512
Hi easytee,

Thanks for the response , i really appreciate it. The distinct works if the fields are all similar (duplicated). But i have cases where the email and address are the same, but when it comes to findings, each is different. So the distinct cannot eliminate any one of them due to its differences. But for me, I only concern about the email addresses. Can the system look into one field (email) only without the comparison to other fields.?

Appreciate your assistant.

In your query, only retrieve the email address. Do not start with "Select * FROM ....."
 

aattas

Registered User.
Local time
Tomorrow, 02:21
Joined
Dec 24, 2014
Messages
74
Hi again,

Can i ask if you could be more specific, you mean one query with one field?

If so, i cant do that, because my report depends on the other fields for an attachment.

If not, please explain how to do it?

Muchas Gracias
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:21
Joined
May 21, 2018
Messages
8,463
It sounds like you want to only return the first record per group. Which there are several ways to do
http://www.cryer.co.uk/brian/sql/sql_first_record_per_group.htm

Can you explain your table/s and what you mean by "sometimes the record of the Email is repeated on the second line"? Is a second line another record?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:21
Joined
May 7, 2009
Messages
19,169
if you are using recordset, you need to save the email to a Collection and check each record if it is already exists in that collection. if it does, ignore this record and skip to next record:
Code:
Dim colEmail As New Collection
Dim bolContinue As Boolean
Dim v As Variant
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(MySQL)
If rs.RecordCount > 0 Then
    rs.MoveFirst
    Do Until rs.EOF
        '* set the flag to false
        '* if the email is blank it will skip
        '* to next record.
        '*
        '* if the email is repeated on another records
        '* those records will be skipped.
        '*
        '* only the first occurrence of the email
        '* will be processed.
        bolContinue = False
        If IsNull(rs!names) Then
            '* do nothing
        Else
            If colEmail.Count = 0 Then
                '* collection has no element yet, safe to continue
                bolContinue = True
                colEmail.Add rs!EMail, rs!EMail
            Else
                '* else check if the email already saved in collection
                On Error Resume Next
                v = colEmail.Item(rs!names)
                If Err.Number <> 0 Then
                    '* email not in collection, add it and continue
                    bolContinue = True
                    colEmail.Add rs!EMail, rs!EMail
                End If
                Err.Clear
                On Error GoTo 0
            End If
        End If
        If bolContinue Then
            Debug.Print rs!rsEmail
            '* do what you need here!
        End If
        
        rs.MoveNext
    Loop
End If
End Sub
 

Mark_

Longboard on the internet
Local time
Today, 16:21
Joined
Sep 12, 2017
Messages
2,111
While the others have answered the question you have posed, I'm wondering if this is the actual behavior you want? If you have two records with the same Email, do you want to send your email once OR do you need to total both records first then send an Email?
 

Users who are viewing this thread

Top Bottom