Entering specific criteria in a query to calculate a future date

[gbleslie029]

I am relatively new to Access and would like some help please. I am ok with the cosmetics but when it comes to criterion/VB etc it is a different story.

I have created a query based on a table and need to enter a criteria to calculate 77 days in advanced based on a field in the query.

The name of the field that I want to look at is Start_Date and I want to enter the criteria in the criteria for a field named Review_Date, which will return the date 77 days in advance.

Maybe I shouldn't be doing this in a query, maybe I should be entering something in a form field. To be truthful I really dont know.

Your help would be appreciated.

G

[ranman256]

yes, use a query.
youd have a form, with a text box,...txtStartDate
(maybe even a text box for #days....txtDays)
the function is DateAdd("d",77,startdate)
the query will have start date,and Target Date

select forms!frmQry!txtStartDate, DateAdd("d",forms!frmQry!txtDays,forms!frmQry!txtS tartDate) as TargetDate

[June7]

Not really sure what you mean by 'enter a criteria'. Is Start_Date a field in table that user inputs value?

If you just want to calculate the Review_Date, try:

SELECT *, [Start_Date] + 77 AS Review_Date FROM tablename;

If you want to apply filter criteria to only show records where current date is => Review_Date, try:

SELECT * FROM tablename WHERE [Start_Date]+77 <= Date();


Users should not interact directly with tables and queries, only forms and reports.

[Micron]

Sounds like G wants to use criteria in the criteria row for a calculated field, which I'm pretty sure you cannot.

I want to enter the criteria in the criteria for a field named Review_Date, which will return the date 77 days in advance.

As noted, one can filter records on the Start_Date value and calculate the corresponding date that is 77 days later. No argument that a criteria input form is less error input prone if done right; the lowly parameter query works too (for some)

which will return the date 77 days in advance

Or maybe this is the calculated field and there's no criteria to be applied to it?

[June7]

Yes, filter criteria can be applied to a Calculated type field.

[Micron]

I thought you'd end up with a sort of 'circular reference' - i.e. the criteria is unknown because it depends on the calculation which depends on the criteria...
Thanks.

[June7]

How would the calculated field depend on the filter criteria? It's really no different than applying filter criteria to a field constructed in query by an expression.

[gbleslie029]

How would the calculated field depend on the filter criteria? It's really no different than applying filter criteria to a field constructed in query by an expression.

Thank you guys for your help.


Sorry if I didn't make myself clear but thank you all very much.


I have used a suggestion from another site and it works perfectly.

this is what I have used in the criteria part of my query in design view: [Start_Date]>=[Review_Date]+77


Thanks guys and keep up the great work.


G