Timeframe query criteria

[Emma35]

Hi guys,


Need a little help again here. I have a form which allows you to enter a date for the completion of a task. When you enter this date, another date exactly one year from then, appears in the control below that (This is the review date for that task). What i'd like to do is to set up a query (which will be the basis for a report), where i can see all the tasks which are due for review in the next 3 months (from todays date). I'd like to also do a query which lets me see which reviews are overdue. The fact that the control which contains the review date is a calculated control is what's throwing me

Any help would be greatly appreciated
Thanks
Em

[euanl]

>=Date() And <Date()+93 (against the review date field) would show all records for dates for next 3 months (approx - the 93 is days), <Date() (against the review date field) shows all overdue.

[Emma35]

Thanks for your help equanl. How to i apply your suggestion to my query as the Review Date control on my form is a calculated control and so there isn't actually a field in a table called Review Date

[euanl]

OK. In your query, in the 'Field' row at the top, instead of bringing an item down from the table you make 3 new 'virtual' columns, i.e. these columns are generated by the code and not physically in the database as such. The first one should be to recalculate the review date -

ReviewDate: [Date_Requested]+365

- The bit that says 'ReviewDate' is your field name, it's important you use the colon as this tells the database that this is a field name. The 'Date_requested' bit is your field that you are calculating the due date from, change this to the name of your field, remembering to place it in the square brackets (if your query links 2 or more tables then you may have to also state the table name, again in square brackets and separated from the field name with a . so [Table1].[FieldName])

Now you want to make the 2 calculations. In the 'Field' row add a new virtual column which calculated if the review date falls within the next 3 months then the date will show, if not then it will remain blank -

Due: IIf([ReviewDate]>=Date() And [ReviewDate]<Date()+93,[ReviewDate],"")

Again edit field names to suit. Finally the column that calculates if it is overdue (it returns the word 'Overdue' if the item is overdue, if you want to also state how long overdue this can also be done, but it will require another column) -

Overdue: IIf([ReviewDate]<Date(),"Overdue","")

Easy as pie.

Of course you could always add the review date field to the underlying table for the form and use the first solution.

[Emma35]

That worked perfectly euanl, thanks a lot. I think you need to change that 'Novice' status to something more appropriate !.

There's one other small thing which i've just thought of. There's also a control at the end of the form called 'Reviewed By'. When somebody reviews the task they enter their name in this box. How can i prevent tasks showing up as overdue if this box has been filled....in other words they're not overdue anymore as they've been completed.

Thank again,
Em

[euanl]

Nest an iif statement inside the original one for overdue. This will show as blank if the reviewed by field is populated and return overdue if not, (and it is overdue).

Overdue: IIf([ReviewDate]<Date(),IIf([Reviewed by]>0,"","Overdue"),"")

[Emma35]

The expressions seem to be working. The code i'm using for 'overdue' is below. If a task is actually overdue, then it works fine and it comes up on the report as overdue but if i then enter a name in the 'Reviewed By' control it comes up as #Error ??

Have i made a mess of the code ?

Overdue: IIf([ReviewDate]<Date(),IIf([ReviewedBy]>0,"","Overdue"),"")

[euanl]

Try this:

Overdue: IIf([ReviewDate]<Date(),IIf([ReviewedBy] Is Not Null,"","Overdue"),"")

[Emma35]

Ok i tried changing the code but now the task is not showing up as overdue. I set the ReviewDate for 20/6/2013 and left the ReviewedBy field blank so it should come up in the query.

Overdue: IIf([ReviewDate]<Date(),IIf([ReviewedBy] Is Not Null,"","Overdue"),"")

Again thanks for your help with this


Edit : My apologies i just double checked and the date was wrong. Everything is working perfectly. Thanks for your time and help with this euanl, you've really dug me out of a hole.

Cheers,
Emma

[Emma35]

Ahem ....i've run into a slight problem. The reports are working but how can i get the query to only show the overdue tasks. Either that or have them all grouped together at the top of the report. I've tried putting "Overdue" in the criteria field of the query but it returns nothing.

Thanks,
Em

[Emma35]

No problem, it's sorted out. I've managed to filter the results using the report instead of the query

Thanks