Calculated fields in query problem (1 Viewer)

[Glen Walker]

Hello everyone

I am currently running a database at my company, I have only being using access for a few months and am still learning to use it.

I am using Access 2010

I would appreciate help on this query maybe I am missing something simple but I don't see it.

There are front end databases that each user works with and a backend networked one with the tables in. In the tables it records the date when a task was completed, this must then be reviewed by the 15th of the following month and the date it is reviewed is then automatically added to the table.

Both table columns CompletedDate and ReviewDate are formatted as Date/Time. Table name "Notifications"

I am trying to create a query to look up which were reviewed after the due date. To do this I have created a select query inside it I have these calculated fields

RequiredDate: DateAdd("m",1,[CompletedDate])
Due Date: DateSerial(year([RequiredDate]), month([RequiredDate]), 15)
On Time: [Due Date]<[ReviewDate]

If I run it like this it works but shows all of the entries regardless of entered early or not, and I understand that. When I try filtering it I added the criteria of True to the On time field, but as soon as I enter this criteria then it asks me to enter due date.:banghead:So I then tried removing the On time field and added <[ReviewDate] as a criteria under the due date field and I then get prompted to enter the RequiredDate. :banghead:

Thanks

 

[MarkK]

Glen, welcome to the forum.

To guarantee that a calculation will occur before some other calculation, you need to write queries on your queries, which are also called sub-queries. Consider...

SELECT Review, Completed, Required, Due, Due < Review As IsOnTime 
FROM
   (
   SELECT Review, Completed, Required, DateSerial(year(Required), month(Required), 15) As Due
   FROM
      (
      SELECT Review, Completed, DateAdd("m",1,Completed) As Required
      FROM YourTable
      )
   )
WHERE Not Due < Review

This guarantees that Required is calculated first in the very inner query. Then Due is calculated in the middle query. Finally, in the outer query, we can reliably use Due in an expression.

You can also save the inner query, maybe you would name it Query1. Then SQL would look like...

SELECT Review, Completed, Required, Due, Due < Review As IsOnTime 
FROM
   (
   SELECT Review, Completed, Required, DateSerial(year(Required), month(Required), 15) As Due
   FROM [COLOR="Blue"]Query1[/COLOR]
   )
WHERE Not Due < Review

Then if you saved that inner query as Query2, the SQL would look like...

SELECT Review, Completed, Required, Due, Due < Review As IsOnTime 
FROM [COLOR="Blue"]Query2[/COLOR]
WHERE Not Due < Review

These are called "sub-queries" and are a very common way to process your data for presentation.

hth
Mark

 

[Glen Walker]

Thanks for this,

I was starting to put this in and noticed 1 line in the table where completed date was blank. I did not see this earlier as there are >1700 records, and when I put a date in there my original query worked. but as I don't need problems arising from this file later, I will be using your solution