In need of a formula

[Jeannie]

Hello,
This is probably very simple, but I just can't seem to figure it out. I
have an amount field on a form which is based on a table with a date field
(ChequeDate) for the amount field (ChequeAmount). What I need is to have the
amount appear in the control only if the date is later than 31/12/2006
Your help is greatly appreciated
Jeannie

 

[fredg]

Hello,
This is probably very simple, but I just can't seem to figure it out. I
have an amount field on a form which is based on a table with a date field
(ChequeDate) for the amount field (ChequeAmount). What I need is to have the
amount appear in the control only if the date is later than 31/12/2006
Your help is greatly appreciated
Jeannie

Using an unbound text control on your form:
=IIf(Year([ChecqueDate]) >=2007,[ChecqueAmount],"")

 

[Jeannie]

Sorry, I copied your formula and replaced it with my actual field names and
it gives me an empty box even though I know I have dates ending with 2007
Any other ideas?

Hello,
This is probably very simple, but I just can't seem to figure it out. I
have an amount field on a form which is based on a table with a date field
(ChequeDate) for the amount field (ChequeAmount). What I need is to have the
amount appear in the control only if the date is later than 31/12/2006
Your help is greatly appreciated
Jeannie

Using an unbound text control on your form:
=IIf(Year([ChecqueDate]) >=2007,[ChecqueAmount],"")

 

[John W. Vinson]

Hello,
This is probably very simple, but I just can't seem to figure it out. I
have an amount field on a form which is based on a table with a date field
(ChequeDate) for the amount field (ChequeAmount). What I need is to have the
amount appear in the control only if the date is later than 31/12/2006
Your help is greatly appreciated
Jeannie

Is the ChequeDate actually a date/time field? If so do you want this to always
display based on this specific date, or do you want it to display the previous
year to date (i.e. do you want the cutoff date to change to December 31 2007
once we get into 2009)?

If so, try using

=IIF(ChequeDate>= DateSerial(Year(Date()) - 1, 1, 1), [ChequeAmount], "")

as the control source of the textbox. All older cheques will be included in
the form but the amount will be blanked.

If you want to simply not display the older checks at all, simply base the
form on a Query using a criterion

= DateSerial(Year(Date()) - 1, 1, 1)

on the ChequeDate field.