VBA not emailing wrong people (1 Viewer)

mousemat

Completely Self Taught
Local time
Today, 23:46
Joined
Nov 25, 2002
Messages
233
I am trying to send an email to participants who have Confirmed Booking. A Confirmed Booking is a Booking whereby the Participant has PAID and sent in the PL Documents. The email should only go to those people who have not been emailed already.

Code:
strSQL = "SELECT tblParticipants.PName, tblParticipants.PEmail, tblBookings.PLReceived, tblBookings.EventID, tblEvents.EventName," & _
"tblEvents.Location, tblEvents.EventDate, tblBookings.AmountPaid, tblBookings.EmailSent, tblBookings.EmailSentDate " & _
"FROM (tblBookings INNER JOIN tblEvents ON tblBookings.EventID = tblEvents.EventID) INNER JOIN " & _
"tblParticipants ON tblBookings.ParticipantID = tblParticipants.ParticipantID WHERE (((tblBookings.EventID)= " & [Forms]![frmbookingreport]![EventID] & ") " & _
"AND ((tblBookings.AmountPaid)is not null ) AND ((tblBookings.PLReceived)= Yes AND ((tblBookings.EmailSent)=0)));"

The trouble is, its emailing both people who have only Paid and not sent their PL Documents.

What am I missing
 

isladogs

MVP / VIP
Local time
Today, 23:46
Joined
Jan 14, 2017
Messages
18,186
Assuming PLReceived is a Yes/No (boolean) field, try changing to ...
...AND ((tblBookings.PLReceived) = True...

If its a text field then use quotes : "Yes"
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:46
Joined
Feb 19, 2013
Messages
16,553
looks to me like your bracketing is wrong - highlighting bracket pairs reveals the PL Received criteria is missing a bracket and emailsent has an additional one - so probably messing up your logic

Code:
WHERE (((tblBookings.EventID)= " & [Forms]![frmbookingreport]![EventID] & ") " & _
"AND ((tblBookings.AmountPaid)is not null ) AND ((tblBookings.PLReceived)= Yes AND ((tblBookings.EmailSent)=0)));"
 

mousemat

Completely Self Taught
Local time
Today, 23:46
Joined
Nov 25, 2002
Messages
233
Excellent.

Both options seemed to work. Bloody brackets and quotes!!!!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:46
Joined
Feb 19, 2013
Messages
16,553
Bloody brackets
you don't need them most of the time - access applies them on a 'better safe than sorry basis'. Only need them if you are using AND's and OR's in the criteria to dictate order of processing

this would have worked just as well

Code:
WHERE tblBookings.EventID= " & [Forms]![frmbookingreport]![EventID] & _
" AND tblBookings.AmountPaid is not null AND tblBookings.PLReceived= Yes AND tblBookings.EmailSent=0"
You also don't need the ; at the end and if your field names are not duplicated across the tables, the table names either (including in the SELECT section) - probably the EventID and ParticipantID in your case. One of the reasons I use PK and FK suffixes for relationship fields - saves an awful lot of typing and makes the code much easier to read.

your whole code could be reduced to

Code:
strSQL = "SELECT PName, PEmail, PLReceived, tblBookings.EventID, EventName, Location, EventDate, AmountPaid, EmailSent, EmailSentDate" & _
" FROM (tblBookings INNER JOIN tblEvents ON tblBookings.EventID = tblEvents.EventID) INNER JOIN tblParticipants ON tblBookings.ParticipantID = tblParticipants.ParticipantID" & _
" WHERE tblBookings.EventID= " & [Forms]![frmbookingreport]![EventID] & " AND AmountPaid is not null AND PLReceived= Yes AND EmailSent=0"
 

mousemat

Completely Self Taught
Local time
Today, 23:46
Joined
Nov 25, 2002
Messages
233
Thanks CJ for the information.

The way you have described makes the whole process so much easier to read.

I'll bear that in mind next time I'm doing something similar
 

Users who are viewing this thread

Top Bottom