Replies

so, you are only interested in “whole” months, then? The Datediff function simply subtracts the month numbers and returns the result, adding 12 for each additional year. If you want to round down to whole months (i.e. 364 days old = 11 months), then you are going to have to adjust the results to compensate for the difference in days and subtract one month. Try this

AGE: iif(datediff(“d”,[DOB],now())<365,datediff("m",[DOB],now()) – iif(datepart("d",now())-datepart("d",[DOB])<0,1,0) & " Months",datediff("YYYY",[DOB],now()))

notice that I trigger the "months" result based on 365 days old rather than "1 year", since datediff will return "1" year for the entire birth month. If the day of the month is greater than the day of the dob, I subtract 1 month to remove the incomplete month from the result. This sounds to me like what you are after.

Is there a reason that you need two tables? If every tblDone record should correspond to a tblToBeDone record, it would make more sense to have these in the same table. This would prevent problems with miskeying the unique ID field, prevent duplicates, and make your queries easier in the long run by avoiding an extra join.

The datediff paramaters are in a different order than the datedif example from excel. Try this:

AGE: iif(datediff(“YYYY”,[DOB],now())=0,datediff(“m”,[DOB],now()) & ” Months”,datediff(“YYYY”,[DOB],now()))

Definately a Data problem. There are two primary problems that you need to address. First, your “Unique IDs” aren’t unique. There are duplicates in both tables. This will throw off your totals since each record in tblDone can “match” multiple records in tblToBeDone. Run a “Find Duplicates” query on each table to identify and fix these. Next, there are numerous records in tblDone that don’t have a corresponding record in tblToBeDone. Therefore your total records in the tblDone table is more than the total records in the tblToBeDone table with a corresponding tblDone record.

You can run a few of queries to see what I mean.

First, join all records from tblDone to the corresponding records in tblToBeDone. You should get 1269 matches (this includes dupes).

Next, change the join to show all records from tblDone and only the records from tblToBeDone that match. Filter for NULL [ID]s on the tblToBeDone side to display tblDone records with no corresponding tblToBeDone record. There are 916 tblDone records with no corresponding “to be done”, which is why you have more “undone” records than you expect.

Finally, change the join to show all records from tblToBeDone and only the tblDone records that match. Filter for NULL [ID]s on the tblDone side to display tblToBeDone records with no corresponding tblDone record. This is the total number of records which are not “done”. I get 1185 records that are not “Done”.

Hope this helps.