Calculated Field trouble (1 Viewer)

[Will123]

I've been trying to set up a calculated field as part of a query but have been having problems, usually my syntax is wrong but i have no real idea how to get around that.

Basically i have a field in my query called "Date Overdue" - the date in which an item becomes overdue, the field i'm trying to set up: "Overdue?" is to be calculated by comparing the current date to Date Overdue. If the date overdue has been reached i want the record on a report to show a "Y" or otherwise a "N".

Can anyone help with the statement i need to write in the query?
I was just trying random Overdue?: If Date >= [Date Overdue], Overdue? = "Y" else Overdue? = "N" kinda statements but to no avail...
Many thanks!

 

[Brianwarnock]

In a fresh column in the query
overdue: IIf([date overdue]< Date(),"Y","N")

note its IIF not If, read help

Brian

 

[Will123]

Thanks for the help, but now each time i try to open the form in layout view its starts asking me to enter a parameter query for "Overdue". Why is it doing this?

 

[Brianwarnock]

Which form? This was a question in the queries forum.

Any form bound to that query will need the field overdue in it, but it missing wouldn't necessarily give the problem.
What are you attempting with the field overdue?

Brian

 

[Will123]

Sorry i meant when i open the report in layout view,

Basically i have a report with fields concerning the details of items on loan.
The date the item was loaned is recorded and the "date overdue" field is calculated from this. As said "Overdue" is calculated by comparng the current date to the "date overdue." "Y" or "N" should then be displayed on the report.
I think the parameter is asking for is the date i want to compare "date overdue" with. This date has already been entered on a form but it doesnt seem to fetch it when i access the report.

 

[Brianwarnock]

Now i'm totally confused.

You have a report based on a query which does a calculation, which I thought used Date(), you now say that the comparison is with a date on a Form, and that when you open the Report it prompts for that date?

That date field must be accessed by Forms!formname!fieldname
is it?

Brian