Auto-populating a text box

[Gerdagan]

Hi all,

I hope everyone had a good Easter.

I have a very simple addition to make to my database, but I cannot figure out how to do it.

I have a text box (TBDUEDATE) on my form that holds a date.

I have another unbound text box that I need to display the words 'overdue' if the date in TBDUEDATE is before today's date.

Can anyone help me set this up. If I was in Excel I'd do a simple IF statement, but I can't seem to figure this out in Access.

Thanks all.
Ged.

 

[Alansidman]

Try this in your unbound text box.

=IIF(tablename.tbduedate<Date(),"Overdue","")

 

[Gerdagan]

Thanks Alansidman,

But I just can't get this to work. When I view my form, all I get is the '#NAME?' error.

I have tried the following variations:

=IF([ME].[TBDUEDATE]<Date(),"Overdue","")
=IF([TBDUEDATE]<Date(),"Overdue","")
=IF([FORMNAME].[TBDUEDATE]<Date(),"Overdue","")

Any ideas how I can make this work?

 

[missinglinq]

Alansidman gave you

=IIF(tablename.tbduedate<Date(),"Overdue","")

but you're using

=IF([ME].[TBDUEDATE]<Date(),"Overdue","")

with only one I.

There is a difference between If and the function IIf(). As my signature says, "The Devil's in the Details!"

Linq ;0)>

 

[Gerdagan]

Thanks missinglinq,

I did try it with the 'IIf' at first, but i didn't get anything from that either, so I assumed it was a typo.

My current statement is:

=IIf([TBDUEDATE]<Date(),"Overdue","")

I have had to remove the reference to the form '[ME]', as this returned the '#NAME?#' error.

If I use '=IIf([TBDUEDATE]<Date(),"Overdue","")' I get no return.
If I use '=IIf([TBDUEDATE]<Date(),"","Overdue")' I get a return on everything.

Help!

 

[Beetle]

Is this a Continuous form?

If so you should do this as a calculated field in a query, then use the query as the Record Source of the form in place of the table. In a query it would look something like;

Status: IIF([DUEDATE]<Date(), "OverDue", "")

 

[Gerdagan]

Thanks Beetle.

My form isn't continuous no.

 

[Beetle]

So you have a Single form, and as you page through records on the form, the value in the unbound text box always remains the same (either "Overdue" or "", depending on the order of arguments in the expression), even though the value in [TBDATEDUE] is, at least for some records, less than the current date?

Is that correct?

 

[Pat Hartman]

Make sure that the Name property of the calculated control does not duplicate the name of any columns from the form's RecordSource.

 

[Gerdagan]

@Beetle, that's exactly it.

@Pat Hartman, I've checked this also and all is fine.

 

[missinglinq]

Since all the advice you've been given is correct, I suspect that you're going to have to remove any proprietary/confidential data, ZIP your file up and attach it to post so that we can have a look at it, if we're to help you.

Also let us know the version of Access you're working with and the name of the Form in question.

Linq ;0)>

 

[bob fitz]

Some of us are still using A2003, so if you can save it in mdb format for the purpose of posting, you may get more help.