ACCESS FEED: RE: [MS_AccessPros] Code for Contracts Expiring

There are days (possibly even weeks) when I don't have a CLUE. It's just the way it goes. I'm in the middle of a big clueless period.

Stick around and be patient with us. When you post or we ask questions, please try to provide all the significant and relevant information. This is probably the most difficult thing to learn for newbies. John, Bill, Crystal, and others have a deep understanding of what is significant and relevant.

The problem is that we can't typically see what you can see in your database. We don't know your abilities or understanding of Access. Stuff that is obvious to us isn't to most newbies. I definitely share John's frustration but just had the opportunity to unload at my boss ;-). Be happy that you (and others) weren't in my cross-hairs this afternoon.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: no_reply@yahoogroups.com
Date: Thu, 2 Jun 2011 19:49:37 +0000
Subject: Re: [MS_AccessPros] Code for Contracts Expiring

John -

Thank you for helping me with the joins for the query. As I've stated
before, I'm new to Access, I've never really worked with it other than
with this database, so all of this is new to me. So, to answer your
question....NO I don't have a CLUE when it comes to coding, joining
tables, etc. What I do know is what I've been shown here on this forum
and through other forums I've found online. I'm sorry that you don't
have the patience to help people who don't really know much about Access
out.... I appreciate all the help that this group has given me with this
project, but I'll find another forum or group and direct my questions to
them, perhaps they will be a bit more understanding and patient.

Kat

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas"
<john@...> wrote:
>
> Kat-
>
> Completely and utterly WRONG. Why would you link the ID from employees
to
> Contract ID? Why do you think Vendor ID is related to Contract ID when
there's
> a perfectly good Vendor ID in Contract Information? Do you not have a
clue?
>
> The SQL for the corrected query looks like:
>
> 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]
> FROM [VENDOR INFORMATION] INNER JOIN ([EMPLOYEE INPUT INFORMATION]
INNER JOIN
> [CONTRACT INFORMATION] ON [EMPLOYEE INPUT INFORMATION].ID = [CONTRACT
> INFORMATION].[EMPLOYEE ID]) ON [VENDOR INFORMATION].[VENDOR ID] =
[CONTRACT
> INFORMATION].[VENDOR ID]
> WHERE ((([CONTRACT INFORMATION].[CONTRACT END
DATE])<(Date()+[Forms]![Contracts
> Expiring]![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/
> (Paris, France)
>
> P.S. You deleted the Module1, but you didn't get rid of the references
to the
> GroupWise libraries!
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
ka0t1c_ang3l
> Sent: Thursday, June 02, 2011 7:54 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Code for Contracts Expiring
>
> John -
>
> I uploaded an updated database for you to look at. I took out the
modules so
> you don't get the errors for groupwise.
>
> I added in the joins in the query. Can you look to see if I joined
them
> correctly?
>
> Thanks!
>
> Kat
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" john@
wrote:
> >
> > Kat-
> >
> > Is there anything in the txtNotify control on the Contracts Expiring
form?
> >
> > And where are your joins in the query? You're getting the cartesian
product
> of
> > Contract Information, Vendor Information, and Employee Input
Information.
> > That's why you're getting multiple rows.
> >
> > 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/
> > (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
ka0t1c_ang3l
> > Sent: Thursday, June 02, 2011 4:21 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] Code for Contracts Expiring
> >
> > John -
> >
> > This is the SQL for the query:
> >
> > 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]
> > FROM [CONTRACT INFORMATION], [VENDOR INFORMATION], [EMPLOYEE INPUT
> INFORMATION]
> > WHERE ((([CONTRACT INFORMATION].[CONTRACT END
> DATE])<(Date()+[Forms]![Contracts
> > Expiring]![txtNotify])));
> >
> >
> > Thanks!
> >
> > Kat
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas"
<john@> wrote:
> > >
> > > Kat-
> > >
> > > What is the SQL for your Expired Contracts query?
> > >
> > > You really shouldn't open a table or query directly in an
application. You
> > > should open a filtered form instead.
> > >
> > > 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/
> > > (Paris, France)
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
ka0t1c_ang3l
> > > Sent: Thursday, June 02, 2011 3:53 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Code for Contracts Expiring
> > >
> > > Good Morning!
> > >
> > > I'm having a problem with the code I have set up when I login into
my
> > database.
> > > The code is supposed to let me know if there are any contracts
expiring and
> > then
> > > asks if I would like to view them. When I click yes, it opens up a
query
> with
> > > all the contracts due to expire; however, when the form opens it
shows all
> the
> > > contracts I've entered with the Contract End Date of the contract
that is
> due
> > to
> > > expire. See the following:
> > >
> > > CONTRID CONTRSTARTDATE CONTRENDDATE CONTPERSON VENDOR ID
> > > 4 2/4/2010 5/31/2010 Michelle Locke Art Museum
> > > 4 2/4/2010 5/31/2010 Scott Cordero Art Museum
> > > 4 2/4/2010 5/31/2010 Eduardo Perez Art Museum
> > > 4 2/4/2010 5/31/2010 Capt. John Pasch Art Museum
> > >
> > > This indicates that all these contracts are for the Art Museum and
expired
> on
> > > 5/31/2010, but only Michelle Locke is with the Art Museum and only
this
> > contract
> > > expired on 5/31/2010.
> > >
> > > I don't know why it's doing that. How can I get it to only show
those
> > contracts
> > > that are expiring for example if I enter 60 days, instead of
showing me the
> > > above.
> > >
> > > This is the code I have set up for this query:
> > >
> > > Private Sub Form_Load()
> > > Call ShowHideButtons(Me)
> > > If glngRank >= 100 Then
> > > If Not IsNull(DLookup("[Contract ID]", "[CONTRACT INFORMATION]",
"[CONTRACT
> > END
> > > DATE] <= #" & Date + 30 & "#")) Then
> > > If vbYes = MsgBox("There are contracts expiring. Would you like to
view
> > them?",
> > > vbQuestion + vbYesNo) Then
> > > ' Open the contract expire form
> > > DoCmd.OpenQuery "Expired Contracts"
> > > DoCmd.Close acForm, "MAIN MENU"
> > > End If
> > > End If
> > > End If
> > > End Sub
> > >
> > > Any help is appreciated!
> > >
> > > Thanks!
> > >
> > > Kat
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>