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
Tidak ada komentar:
Posting Komentar