Will do. Thanks.

Obviously that could be rewritten to get rid of the YK3 issue, but here's a place where SQL could use a built-in variable for positive infinity, like some languages do.

I'd program it for 12/31/9999, because I'm a very strange man...

I have apparently managed to boot my 400MHZ G4 box to boot on a hard drive with Leopard installed.

To quote that old mainframe song "Do It Yourself:"

...it crashes every hour, and it isn't worth a damn,
But I'm satisfied because it runs just like an IBM!

I have a visual basic/access question. My database has a form which I use to send confirmation emails to students. There are 2 drop down menus, one for student name, and one for class name. There is a different button for each letter, which takes me to a word mail merge document based on selecting either the student or the class.

My issue is that I need two more sub-criteria-- a way to send to just the unpaid and not cancelled people in the class. Currently to remind people to pay, i have to go through each class by hand and pick out the people who haven't apid, write down their names, and then individually send them an email.

Now I am supposed to do at least weekly.

I only need one mail merge sorted this way, so I thought maybe I could add something to the command that happens when you press the button, but I do not understand visual basic at all.

The command is

Private Sub cmdPayNeed_Click() On Error GoTo Err_PayNeed_Click Dim db As DAO.Database Dim qry As QueryDef Set db = CurrentDb If Not Me.cboClassName = "" And Not Me.cboStudentName = "" Then MsgBox "You may only choose Class or Student but not both.", vbCritical Me.cboClassName = "" Me.cboStudentName = "" Exit Sub ElseIf Me.cboClassName "" Then Set qry = db.QueryDefs("qryStudentPayEmailClass") qry.Parameters("Input Class ID") = [Forms]![preLetterType]![cboClassName] qry.Execute ElseIf Me.cboStudentName "" Then Set qry = db.QueryDefs("qryStudentPayEmail") qry.Parameters("Input Student ID") = [Forms]![preLetterType]![cboStudentName] qry.Execute ElseIf Me.cboClassName = "" And Me.cboStudentName = "" Then MsgBox ("You must select a Class or Student before opening Payment Needed."), vbCritical Me.cboClassName.SetFocus Exit Sub End If

Exit_cmdPayNeed_Click: Exit Sub

Err_PayNeed_Click: MsgBox Err.Description Resume Exit_cmdPayNeed_Click

End Sub

Sophia, would you use a different query for this case? (If so, can you create the query?)

My issue is that I need two more sub-criteria-- a way to send to just the unpaid and not cancelled people in the class.

I don't understand the "two criteria" here - isn't this just one more option? Or is "unpaid" and "not cancelled" two different options?

How would you modify the form for the new options?

I have one field for "cancelled" (Yes/No" and then one field for Payment Owed (which if it is zero, I do not want to sent the email.

I tried changing the query to include a ="no" in the cancelled field and =NOT"0" in the PAYOWED field, , but then I recieved an error saying it was to complicated? Possibly because the VBA is telling it the class ID via the drop down menu?

I don't actually want to change the form- I just want the button that currently says "payment needed email" to only choose the people who actually need to pay?

I am not even 100% sure that this makes sense.

I should know how to do this, but for some reason I can't find the right link - Google is defaulting me to google.co.uk, and I want it set to google.com. Where do I go to tell it I'm in the US even though my IP address isn't?

[huh, it seems to have done it automatically. we'll see if it sticks for tomorrow!]

So you will never need to run the query as you currently do, without the extra criteria? And it looks like it calls two different queries depending on if you select student or class, is that right? We might need to look at the query text, and what you tried to change it to that failed.

Thanks Liese- I am not at work now so I can't get more information on the query, but yes, I will never have to run the thing I currently run. What I basically want is a button that, but clicking it, I will be able to send an email to all the students who are in current classes who have not paid, and haven't cancelled their registration. I was trying to use something that already existed ad just tweak it, but maybe it would be simpler to start from scratch. Ad make a new button with a new query for all the students in this semester who are unpaid and not cancelled.

My other solution was creating both a paid and an unpaid version of each class and move the students once they have paid, thereby being able to send emails to only the unpaid people.

No, I think your original solution should work. It`s just a matter of getting the query semantics down right, probably.