Katrina-
You would need a "temp" table that contains all the columns from your query and
a Yes/No field. Run a Delete query to clear the table, append the records from
your "results" query into that table, then open a form to display the table
contents. User could click the Yes/No field to "exclude" certain rows, then
click a button to send an email to those not excluded. Your code to send emails
would be in that second form.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Waco, TX)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
Sent: Wednesday, February 22, 2012 8:33 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] coding for query
John -
Thanks! I filled in the Legal ID on my Contract Information table and all of
the records came up. I have another question. A little back ground first. On
my main menu I have a "Contracts Expiring" button which opens the "Contracts
Expiring" form. This is the form you fixed the code on. What I do is enter the
number of days and hit run and the system will automatically send each vendor
with a contract expiring an email advising them of such. However, in my system
I also have short term agreements which were for maybe only a few month, and I
don't want emails going to those vendors since we would not be renewing a
contract with them. Is it possible to select which vendors I want to send
emails to? For example, once I get to the "Contracts Expiring" form and put in
the number of days when I hit run instead of instantally having the system send
an email to all vendors with expiring contracts it will prompt me with a list
that includes a checkbox and those vendors with a checked box will be the only
ones who will receive an email. If this is possible what would I have to do to
get this done?
Thanks for your help!
Katrina
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Katrina-
>
> The query runs fine - and returns no rows because there is nothing in the
Legal
> ID field in your sample table. The INNER JOINs will return only rows where
> there are matching values for all the Joins.
>
> But your code is wrong because you failed to include parens for the additional
> join, and you had an extra ON keyword in the wrong place:
>
> Set rst = db.OpenRecordset("SELECT [VENDOR E-MAIL], [CONTRACT END
DATE],
> [E-MAIL], [CONTACT PERSON], [LEGAL EMAIL] " & _
> "FROM (([CONTRACT INFORMATION] INNER JOIN
> [VENDOR INFORMATION] ON " & _
> "[CONTRACT INFORMATION].[VENDOR ID] =
[VENDOR
> INFORMATION].[VENDOR ID]) " & _
> "INNER JOIN [EMPLOYEE INPUT INFORMATION] ON
"
> & _
> "[CONTRACT INFORMATION].[EMPLOYEE ID] =
> [EMPLOYEE INPUT INFORMATION].[ID])) " & _
> "INNER JOIN [Legal tbl] ON " & _
> "[CONTRACT INFORMATION].[LEGAL ID] = [Legal
> tbl].[LEGAL ID] " & _
> "WHERE [CONTRACT END DATE] < #" & (Date +
> Me.txtNotify) & "#")
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Waco, TX)
>
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
> Sent: Tuesday, February 21, 2012 4:44 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] coding for query
>
> John -
>
> If i take out the code regarding the legal id and legal email the query works
> fine, so I know it's something to do with the legal tbl i've added. However,
I
> can't figure out what I'm missing or what I've entered incorrectly.
>
> Thanks for your help!
>
> Katrina
>
> --- In MS_Access_Professionals@yahoogroups.com, ka0t1c_ang3l <no_reply@>
> wrote:
> >
> >
> > John -
> >
> > I apologize for the late reply. I've uploaded the database into the
> 2_AssistanceNeeded folder under contract management system2.zip. The Expired
> Contracts query is in there. It was working fine before, but I had to go in
and
> the legal email. The reason for adding in the email is because I want a
> courtsey copy of the email that will be sent to vendors to also go to our
> attorney.
> >
> > Also, could you please take a look at my contracts expiring form. The
"Run"
> button event procedure to make sure I added in the legal information
correctly.
> I'm still new to this and am learning as I go. I just want to be sure I've
> included everything correctly.
> >
> > Thanks!
> >
> > Katrina
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
wrote:
> > >
> > > Katrina-
> > >
> > > FROM ([VENDOR INFORMATION] INNER JOIN ([EMPLOYEE INPUT INFORMATION]
> > > INNER JOIN [CONTRACT INFORMATION]
> > > INNER JOIN [Legal tbl]
> > > ON [EMPLOYEE INPUT INFORMATION].ID = [CONTRACT INFORMATION].[EMPLOYEE ID])
> > > ON [VENDOR INFORMATION].[VENDOR ID] = [CONTRACT INFORMATION].[VENDOR ID])
> > > ON [CONTRACT INFORMATION].[LEGAL ID] = [Legal tbl].[LEGAL ID]
> > >
> > > I don't see anything wrong with that unless you've misspelled one of the
> table
> > > or ID names. Or perhaps there's a problem with an ID data type somewhere.
> > >
> > > What does this look like in Design view?
> > >
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Waco, TX)
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
> > > Sent: Tuesday, February 21, 2012 8:58 AM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] coding for query
> > >
> > > I have an expired contract query which I use to select vendors who I want
> > > emailed advising that their lease is due to expire. This query works
fine;
> > > however, I want to add our attorney's email to the query so that she can
be
> > > included in the courtesy copy of the email. When I try to add the legal
> email
> > > it keeps giving me a syntax error and it higlights the inner join part of
> the
> > > SQL code. This is what I have.
> > >
> > > ------------
> > > SELECT [CONTRACT INFORMATION].[CONTRACT START DATE], [CONTRACT
> > > INFORMATION].[CONTRACT END DATE], [VENDOR INFORMATION].[VENDOR E-MAIL],
> > > [CONTRACT INFORMATION].[CONTRACT ID], [EMPLOYEE INPUT
INFORMATION].[E-MAIL],
> > > [VENDOR INFORMATION].[CONTACT PERSON], [CONTRACT INFORMATION].[VENDOR ID],
> > > [CONTRACT INFORMATION].[CONTRACT TITLE], [CONTRACT INFORMATION].[LEGAL
ID],
> > > [Legal tbl].[LEGAL EMAIL]
> > > FROM ([VENDOR INFORMATION] INNER JOIN ([EMPLOYEE INPUT INFORMATION] INNER
> JOIN
> > > [CONTRACT INFORMATION] INNER JOIN [Legal tbl] ON [EMPLOYEE INPUT
> INFORMATION].ID
> > > = [CONTRACT INFORMATION].[EMPLOYEE ID]) ON [VENDOR INFORMATION].[VENDOR
ID]
> =
> > > [CONTRACT INFORMATION].[VENDOR ID]) ON [CONTRACT INFORMATION].[LEGAL ID] =
> > > [Legal tbl].[LEGAL ID]
> > > WHERE ((([CONTRACT INFORMATION].[CONTRACT END
> DATE])<(Date()+[Forms]![Contracts
> > > Expiring]![txtNotify])));
> > > -----------------
> > >
> > > If anyone can please help me and let me know what it is I'm doing wrong
I'd
> > > really appreciate it.
> > >
> > > Thanks!
> > >
> > > Katrina
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar