Filter form based on value not in subform

[MiataDiablo]

Maybe I'm going at this from the wrong angle so I would appreciate
anyone's input or suggestions.

I have a form [frmCorp], that contains a subform [subPayments] which
contains the field txtYear. How do I filter the form to show records
that don't yet have payments posted for the current year so that I can
generate second notice letters?

Thanks in advance

 

[Tom van Stiphout]

On Mon, 22 Jun 2009 13:38:54 -0700 (PDT), MiataDiablo

I would bind the form to a query that excludes paid-up accounts.
Something like this:
select * from Corp
where CorpID not in (
select CorpID from Payments where PaymentYear = Year(Date))

-Tom.
Microsoft Access MVP

 

[MiataDiablo]

On Mon, 22 Jun 2009 13:38:54 -0700 (PDT), MiataDiablo


I would bind the form to a query that excludes paid-up accounts.
Something like this:
select * from Corp
  where CorpID not in (
    select CorpID from Payments where PaymentYear = Year(Date))

-Tom.
Microsoft Access MVP

Maybe I'm going at this from the wrong angle so I would appreciate
anyone's input or suggestions.

I have a form [frmCorp], that contains a subform [subPayments] which
contains the field txtYear.  How do I filter the form to show records
that don't yet have payments posted for the current year so that I can
generate second notice letters?

Thanks in advance- Hide quoted text -

- Show quoted text -

Unfortunately, I'm still hopelessly lost. My SQL skills are
essentially non-existent. At the risk of sounding like a complete
idiot: My CorpID field from tblCorp is referred to as copiedCorpID in
my payments table. Do I need to change that field's name in my
payments table?

 

[Tom van Stiphout]

On Tue, 23 Jun 2009 07:37:50 -0700 (PDT), MiataDiablo

No; you just need to adjust my sqlstatement:
select * from Corp
  where CorpID not in (
    select copiedCorpID from Payments where PaymentYear = Year(Date))

-Tom.
Microsoft Access MVP

On Mon, 22 Jun 2009 13:38:54 -0700 (PDT), MiataDiablo


I would bind the form to a query that excludes paid-up accounts.
Something like this:
select * from Corp
  where CorpID not in (
    select CorpID from Payments where PaymentYear = Year(Date))

-Tom.
Microsoft Access MVP

Maybe I'm going at this from the wrong angle so I would appreciate
anyone's input or suggestions.

I have a form [frmCorp], that contains a subform [subPayments] which
contains the field txtYear.  How do I filter the form to show records
that don't yet have payments posted for the current year so that I can
generate second notice letters?

Thanks in advance- Hide quoted text -

- Show quoted text -

Unfortunately, I'm still hopelessly lost. My SQL skills are
essentially non-existent. At the risk of sounding like a complete
idiot: My CorpID field from tblCorp is referred to as copiedCorpID in
my payments table. Do I need to change that field's name in my
payments table?

 

[MiataDiablo]

On Tue, 23 Jun 2009 07:37:50 -0700 (PDT), MiataDiablo


No; you just need to adjust my sqlstatement:
select * from Corp
  where CorpID not in (
    select copiedCorpID from Payments where PaymentYear = Year(Date))

-Tom.
Microsoft Access MVP

On Mon, 22 Jun 2009 13:38:54 -0700 (PDT), MiataDiablo
I would bind the form to a query that excludes paid-up accounts.
Something like this:
select * from Corp
  where CorpID not in (
    select CorpID from Payments where PaymentYear = Year(Date))
-Tom.
Microsoft Access MVP
Maybe I'm going at this from the wrong angle so I would appreciate
anyone's input or suggestions.
I have a form [frmCorp], that contains a subform [subPayments] which
contains the field txtYear.  How do I filter the form to show records
that don't yet have payments posted for the current year so that I can
generate second notice letters?
Thanks in advance- Hide quoted text -
- Show quoted text -

Unfortunately, I'm still hopelessly lost.  My SQL skills are
essentially non-existent.  At the risk of sounding like a complete
idiot:  My CorpID field from tblCorp is referred to as copiedCorpID in
my payments table.  Do I need to change that field's name in my
payments table?- Hide quoted text -

- Show quoted text -

Thank you so much, seeing it in a query and sql finally makes sense in
my head. I appreciate your help and your patience!