Can someone please advise what formula I could use to ascertain the following:
[Entered] is one field where the users enter the current date of edit. [FollowUp] is the other field where the user enters a date which is 21 days in excess of the [Entered] date in order to check the matter.
I would like the query to display results where the [FollowUp] date is 21 days or more than the [Entered] date however, the [FollowUp] date must be current to todays date or in arrears.

Hope this makes sense!

Hmmm ...

Let me see if I understand ...
The "Entered" is a date value stored in a field in the table ...
You have another field in the table that is populated with the result of the "Entered" + 21 days ???
If this is True .. then this field should not be in your table ...
The "FollowUp" value can and should be calculated "on the fly" dynamically and not a stored value ...

Lets clear this up first .. before getting into the rest of your problem.

RDH

Apologies for the confusion.
They are two separate fields [Entered] & [FollowUp].
The [FollowUp] field is only used should the user need to follow up on a matter. The user would enter a follow up date which is 21 days from the date the matter was entered [Entered] into the database. If there is no follow up there would be no follow up date.
The query needs to run a report that displays follow ups required to be actioned when the follow up date becomes current. Additional to this the follow ups need to remain in the report until they are completed which is identified in a separte field labled [Complete].

You still have a problem here ...

There should not be a "Followup Date" stored in your table .. this should be calculated ...
If there is to be a followup and if there is .. and it will always be 21 days after the Entered Date ...

What you need here is use a Yes/No (boolean field) in the table ...
Then this field is set using a checkbox on the form (checked if there is to be a followup) ...

Then you can filter all records needing a followup (boolean field) .. FollowUp = True (checked) ...
Also I assume that "Completed" is also a boolean field in the table ... so you need to also filter the records where Completed = False.

Now ... you need to create a Field Expression to return the Followup Date value.
This would be something like this:

FollowUpDate: [EnteredDate] + 21

In the criteria for this field expression use:
<= Date()

This should do what you need .. with no storing of the followup date in your table ...

RDH

Thanks, I will give it a try and will post back results.

I have done what you mentioned. Makes sense however, the query is still displaying entered dates that are in excess of the 21 days despite the criteria being <=Date() and the field titled FollowUp: [Entered]+21.
So that I am clear is the above field expression created in a blank field on the query with no table selected? Unlike the FollowUp & Completed fields that have the reference to the table with the criteria being True & False?

Can you compact the database and zip it up and attach it to a reply (500k limit) ???

Maybe if I see the query in action ... I can locate the problem.

RDH

Thanks. Please find attachment.

Try this:

RDH

Thank you very much Ricky for your time and patience. Much appreciated.

Did you see the problem ??

I have attached a pic showing the problem ...
The criteria circled was in the second line of the section and should have been in the top most line of each field ...

And ... you are very wellcome ...

RDH

OK. Was it also because I did not place the [Entered] field in the query?

No .. that field was placed there by me just to see the value from the table ...
You can remove that field from the query if you wish ...

See the picture I attached to my last reply showing the problem circled in Red ...

RDH

I see, thanks Ricky. Just finally, I have created a form for the query however, when the form opens it will not allow an update. EG: checking the replied box so that the matter is removed from follow up.

In relation to my post above, I have ascertained that a table can not be updated via a query (which is displayed as a form). I have made the query an update query however, when the form opens (in add mode) an error displays : "The query cannot be used as a row source".
Any ideas on what this may mean.
PS: I have not created any relationships as I did not see this as necessary.

Actually "update query" is not what I am after. I simply need the query that displays via a form to allow an update which is referenced back to the relevant table. Hope this is not too confusing.

Not sure I completely understand the setup ...

Can you zip up what you have now that is doing what you say and attach it ???
If yes .. then give me is the name of the form ...

RDH

I'm really confused now ...
An Update query should not be used as the Record Source for a form ..
It should be a "Select Query" ...

RDH

Thanks Ricky. Please find attachment.
When you load form you will see a button on top right that has Follow Ups written next to it. When you click on the button it takes you to the follow up which come from the query. The updateable fields required are replied, action taken & comments. You will notice that they are locked. When it asks for a user just place an "*".

I have read in the forums that this problem has been experienced by others however, it mostly relates to users running a query on multiple tables. I can not seem to work out why a simple select query which is displayed in a form does not allow edits/updates. Help would be appreciated.