strsql (1 Viewer)

M

mousemat

Completely Self Taught
Local time
Today, 13:40
Joined
Nov 25, 2002
Messages
233
Have used this string successfully now to send emails out to participants who haven't paid.

Basically, the table hold Event Bookings for each participant. In order to class a Booking Confirmed, the Participant needs to have paid AND sent in their Public Liability Insurance Certificate.

What i am trying to do now is to send out reminder emails to Participants who have not confirmed their booking, as described above.I guess an IIF statement needs to be put after the where clause but can't seem to get my head round it. I can do the IIF statement in a normal query.

This send the email to those who have not paid.
strSQL = "SELECT tblbookings.EventID, tblParticipants.PEmail, tblBookings.AmountPaid, tblEvents.EventName, tblEvents.EventDate, tblParticipants.PName, tblBookings.PLReceived, tblbookings.ReminderEmailSent, tblbookings.ReminderEmailSentDate FROM (tblParticipants INNER JOIN tblBookings ON tblParticipants.ParticipantID = tblBookings.ParticipantID) INNER JOIN tblEvents ON tblBookings.EventID = tblEvents.EventID WHERE (((tblBookings.EventID)= " & [Forms]![frmBookingReport]![EventID] & ") AND (Nz(tblBookings.AmountPaid,0) =0));"
Click to expand...

This is an IFF Statement from a query which does what I need it to do.
Confirmed: IIf([tblbookings.amountpaid] Is Not Null And [tblbookings.plreceived]="Yes","Confirmed","Not Confirmed")
Click to expand...

I'm just struggling to marry the two up so to speak.

Any help would be gratefully received.
 
B

Beetle

Duly Registered Boozer
Local time
Today, 06:40
Joined
Apr 30, 2011
Messages
1,808
This might work;

strSQL = "SELECT tblbookings.EventID, tblParticipants.PEmail, tblBookings.AmountPaid, tblEvents.EventName, tblEvents.EventDate, tblParticipants.PName, tblBookings.PLReceived, tblbookings.ReminderEmailSent, tblbookings.ReminderEmailSentDate FROM (tblParticipants INNER JOIN tblBookings ON tblParticipants.ParticipantID = tblBookings.ParticipantID) INNER JOIN tblEvents ON tblBookings.EventID = tblEvents.EventID WHERE (((tblBookings.EventID)= " & [Forms]![frmBookingReport]![EventID] & ") AND (Nz(tblBookings.AmountPaid,0) =0)
OR tblBookings.EventID= " & [Forms]![frmBookingReport]![EventID] & " AND tblBookings.AmountPaid>0 AND tblBookings.PLReceived = 'No';"
 
M

mousemat

Completely Self Taught
Local time
Today, 13:40
Joined
Nov 25, 2002
Messages
233
Beetle said:
This might work;

strSQL = "SELECT tblbookings.EventID, tblParticipants.PEmail, tblBookings.AmountPaid, tblEvents.EventName, tblEvents.EventDate, tblParticipants.PName, tblBookings.PLReceived, tblbookings.ReminderEmailSent, tblbookings.ReminderEmailSentDate FROM (tblParticipants INNER JOIN tblBookings ON tblParticipants.ParticipantID = tblBookings.ParticipantID) INNER JOIN tblEvents ON tblBookings.EventID = tblEvents.EventID WHERE (((tblBookings.EventID)= " & [Forms]![frmBookingReport]![EventID] & ") AND (Nz(tblBookings.AmountPaid,0) =0)
OR tblBookings.EventID= " & [Forms]![frmBookingReport]![EventID] & " AND tblBookings.AmountPaid>0 AND tblBookings.PLReceived = 'No';"
Click to expand...


Thanks for that. I now get a runtime error 3075 detailing this

(((tblBookings.EventID)= " & [Forms]![frmBookingReport]![EventID] & ") AND (Nz(tblBookings.AmountPaid,0) =0) OR tblBookings.EventID= " & [Forms]![frmBookingReport]![EventID] & " AND tblBookings.AmountPaid>0 AND tblBookings.PLReceived = 'No';"
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:40
Joined
May 7, 2009
Messages
19,231
Confirmed: IIf(nz([tblbookings].[amountpaid],0)<>0 And [tblbookings].[plreceived]="Yes","Confirmed","Not Confirmed")
 
M

mousemat

Completely Self Taught
Local time
Today, 13:40
Joined
Nov 25, 2002
Messages
233
arnelgp said:
Confirmed: IIf(nz([tblbookings].[amountpaid],0)<>0 And [tblbookings].[plreceived]="Yes","Confirmed","Not Confirmed")
Click to expand...

Thanks... Where are you putting that, as Im still getting sytanx errors etc
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:40
Joined
May 7, 2009
Messages
19,231
That was your original expression. Chk your fieldname if they are correct.
 
M

mousemat

Completely Self Taught
Local time
Today, 13:40
Joined
Nov 25, 2002
Messages
233
Confirmed: IIf([tblbookings.amountpaid] Is Not Null And [tblbookings.plreceived]="Yes","Confirmed","Not Confirmed")

Yes the field names are correct, this works in the standard query, returns what I need it to do.
 
B

Beetle

Duly Registered Boozer
Local time
Today, 06:40
Joined
Apr 30, 2011
Messages
1,808
Corrected for VBA;

Code: 
strSQL = "SELECT tblbookings.EventID, tblParticipants.PEmail, tblBookings.AmountPaid, tblEvents.EventName, tblEvents.EventDate, tblParticipants.PName, tblBookings.PLReceived, tblbookings.ReminderEmailSent, tblbookings.ReminderEmailSentDate FROM (tblParticipants INNER JOIN tblBookings ON tblParticipants.ParticipantID = tblBookings.ParticipantID) INNER JOIN tblEvents ON tblBookings.EventID = tblEvents.EventID WHERE tblBookings.EventID= " & [Forms]![frmBookingReport]![EventID] & " AND Nz(tblBookings.AmountPaid,0) =0 
OR tblBookings.EventID= " & [Forms]![frmBookingReport]![EventID] & " AND tblBookings.AmountPaid>0 AND tblBookings.PLReceived = 'No';"

Removed some of the unnecessary parentheses (the Access query builder is very fond of parentheses) and tried to correct some possible spacing issues. Also, this assumes that plreceived will either be 'Yes' or 'No'. If not, replace 'No' with whatever the negative response is to plreceived.

When copying SQL from the query builder to VBA you often have to correct for use of quotes and spacing. I find it much easier to read and break down the SQL statement in VBA if you use continuation characters, so the above would look like;

Code: 
strSQL = "SELECT tblbookings.EventID, tblParticipants.PEmail, tblBookings.AmountPaid, " _
       & "tblEvents.EventName, tblEvents.EventDate, tblParticipants.PName, tblBookings.PLReceived, " _
       & "tblbookings.ReminderEmailSent, tblbookings.ReminderEmailSentDate " _
       & "FROM (tblParticipants INNER JOIN tblBookings ON tblParticipants.ParticipantID = tblBookings.ParticipantID) " _
       & "INNER JOIN tblEvents ON tblBookings.EventID = tblEvents.EventID " _
       & "WHERE tblBookings.EventID= " & [Forms]![frmBookingReport]![EventID] _
       & " AND Nz(tblBookings.AmountPaid,0) =0 " _
       & "OR tblBookings.EventID= " & [Forms]![frmBookingReport]![EventID] _
       & " AND tblBookings.AmountPaid>0 AND tblBookings.PLReceived = 'No';"

If it still doesn't work, go back to the query builder and add the extra criteria there, get it working, then copy back to your module.
 
M

mousemat

Completely Self Taught
Local time
Today, 13:40
Joined
Nov 25, 2002
Messages
233
Beetle said:
Corrected for VBA;

Code: 
strSQL = "SELECT tblbookings.EventID, tblParticipants.PEmail, tblBookings.AmountPaid, tblEvents.EventName, tblEvents.EventDate, tblParticipants.PName, tblBookings.PLReceived, tblbookings.ReminderEmailSent, tblbookings.ReminderEmailSentDate FROM (tblParticipants INNER JOIN tblBookings ON tblParticipants.ParticipantID = tblBookings.ParticipantID) INNER JOIN tblEvents ON tblBookings.EventID = tblEvents.EventID WHERE tblBookings.EventID= " & [Forms]![frmBookingReport]![EventID] & " AND Nz(tblBookings.AmountPaid,0) =0 
OR tblBookings.EventID= " & [Forms]![frmBookingReport]![EventID] & " AND tblBookings.AmountPaid>0 AND tblBookings.PLReceived = 'No';"

Removed some of the unnecessary parentheses (the Access query builder is very fond of parentheses) and tried to correct some possible spacing issues. Also, this assumes that plreceived will either be 'Yes' or 'No'. If not, replace 'No' with whatever the negative response is to plreceived.

When copying SQL from the query builder to VBA you often have to correct for use of quotes and spacing. I find it much easier to read and break down the SQL statement in VBA if you use continuation characters, so the above would look like;

Code: 
strSQL = "SELECT tblbookings.EventID, tblParticipants.PEmail, tblBookings.AmountPaid, " _
       & "tblEvents.EventName, tblEvents.EventDate, tblParticipants.PName, tblBookings.PLReceived, " _
       & "tblbookings.ReminderEmailSent, tblbookings.ReminderEmailSentDate " _
       & "FROM (tblParticipants INNER JOIN tblBookings ON tblParticipants.ParticipantID = tblBookings.ParticipantID) " _
       & "INNER JOIN tblEvents ON tblBookings.EventID = tblEvents.EventID " _
       & "WHERE tblBookings.EventID= " & [Forms]![frmBookingReport]![EventID] _
       & " AND Nz(tblBookings.AmountPaid,0) =0 " _
       & "OR tblBookings.EventID= " & [Forms]![frmBookingReport]![EventID] _
       & " AND tblBookings.AmountPaid>0 AND tblBookings.PLReceived = 'No';"

If it still doesn't work, go back to the query builder and add the extra criteria there, get it working, then copy back to your module.
Click to expand...

That's perfect, many thanks for that. Thanks too fr the heads up on the code writing
 
Cronk

Cronk

Registered User.
Local time
Today, 22:40
Joined
Jul 4, 2013
Messages
2,771
(the Access query builder is very fond of parentheses)
Click to expand...
The query generator also has deficiencies with handling AND/OR criteria.

The code could be simplified and any ambiguity eliminated by including a couple of parentheses.

Code: 
....."WHERE tblBookings.EventID= " & [Forms]![frmBookingReport]![EventID]
 _  & " AND (Nz(tblBookings.AmountPaid,0)=0 OR tblBookings.PLReceived = 'No');"
 
You must log in or register to reply here.

Similar threads

M
VBA not emailing wrong people
Replies
5
Views
356
mousemat
M
M
CDO Mail sending attachment via location field
2
Replies
29
Views
2,455
cheekybuddha
cheekybuddha
M
Runtime 3061 Too Few Parameters
Replies
7
Views
565
isladogs
isladogs
iankerry
why does my code miss out a record?
Replies
11
Views
705
MajP
MajP
imperator
Send current record to Outlook
Replies
12
Views
3,180
JANR
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom