In need of a formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeannie - 09 Feb 2008 16:51 GMT

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

Reply to this Message

fredg - 09 Feb 2008 17:34 GMT

> Hello,
> This is probably very simple, but I just can't seem to figure it out. I
[quoted text clipped - 3 lines]
> Your help is greatly appreciated
> Jeannie

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

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Jeannie - 09 Feb 2008 17:53 GMT

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
[quoted text clipped - 6 lines]
> Using an unbound text control on your form:
> =IIf(Year([ChecqueDate]) >=2007,[ChecqueAmount],"")

Reply to this Message

John W. Vinson - 09 Feb 2008 19:08 GMT

>Hello,
>This is probably very simple, but I just can't seem to figure it out. I
[quoted text clipped - 3 lines]
>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.

Signature

John W. Vinson [MVP]

Reply to this Message