View Single Post
  #3  
Old May 27th, 2010, 09:14 PM posted to microsoft.public.access.forms
Konchetta via AccessMonster.com
external usenet poster
 
Posts: 31
Default Datasheet Subform Query

Thanks Daryl,

Ohh ok I get it. I know I was doing something wrong. I will try this and see
if I can get it to work. Thanks for your help!

Daryl S wrote:
Konchetta -

I suspect you only add records to the table when the fees are received. If
you enter a record and leave the CalendarYear field empty, then the query you
suggest would only kind of work, since you would not know if the NULL was for
2009 or 2010. Instead, if you only enter a record with a CalendarYear
populated (which is the right way to do it), then you are looking for records
that don't exist, rather than records that have a null value. Since you
didn't give any specifics, I will assume you have a table for your main form
(maybe Clients), and the subform comes from the table CalendarYear, which has
the ClientID as well as the CalendarYear field. The query you want is to
show all Clients that have no 2009 or 2010 CalendarYear records. It will
look something like this:

SELECT Clients.* FROM Clients
WHERE Not exists (Select 'X' from [CalendarYear] WHERE Clients.ClientID =
CalendarYear.ClientID AND CalendarYear.CalendarYear in (2009,2010));

You will need to use your table and field names for this. If you have
problems, post your SQL (in Query Design choose SQL View and copy/paste) so
we can help.

I have a Datasheet subform for which I have a field name CalendarYear which
basically lets me know that fees were received for that Calendar Year. I have

[quoted text clipped - 6 lines]

Thanks in advance for your assistance!!


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1