Don't know what to write in criteria

[Sherzodbek]

Hi! It is urgent needs to be solved in 24 hours, please help!!!!
I am asked to built an information management system for the company who sells medical devices. According to legislation each type of product sold by the company should be tested every 12 month. My boss asked to create a report which gives a full of information of outdated products each year. The products which were not tested within the last 12 month are called as outdated products. In the criteria, I wrote:

IIF(DateDiff(m;[tbl_testmarks]![test_Date];Now())>12;"Outdated";"Valid")

However, I am having the products which were tested. What to do?

[Seth Schrock]

Your criteria shouldn't have the IIF in it. Also, you need tbl_testmarks as part of your query (unless the reference in your code is to a form and the naming convention got left behind). So your criteria to just get those products which are out of date would be:

DateDiff("m", test_date, Date()) > 12

[Anas Mosaad]

I don't have the details about your systems but it seems to me that m should be wrapped in double quotes (i.e. "m").

IIF(DateDiff("m",[tbl_testmarks]![test_Date],Now())>12,"Outdated","Valid")
 

[Sherzodbek]

It is MS Office 2013. I tried it, but it is giving me the whole list of products except 2012 dates.

DateDiff function shows the interval between two specified dates. in my table there are almost 800 records with 73 products each tested every 12 month after being sold. In 2012, there are some products which are not tested yet. When I typed the datediff function as you said sir Seth Schrock, i am having the test dates from the date the product sold till current date except products tested in 2012.

The company started selling from May 2006, and the query shows the records from 2006, however I don't need that.

[zmbd]

Sherzodbek:
First here is the syntax for DateDiff Function

Second, we'll need a little more information about the field you're testing against if the records returned using Seths suggestion didn't work as expected.

[NeoPa]

It is required for you to post a proper question. This lacks much of the detail required for this question to make good sense. Please see the sticky threads at the top of the forum for instructions on how to post a basic question then fill in the details you missed out from your question.

[Seth Schrock]

@NeoPa, the sticky threads don't appear at the http://bytes.com/topic/access/ website (which is what going through the Browse Topics > Microsoft Access/VBA takes you to). I can only see the sticky threads if I go to http://bytes.com/topic/access/answers/ website, so I doubt if the OP ever saw those.

[NeoPa]

That's a very good point, and one I was unaware of. I'll speak to the Admins about it (I tried moving it and it didn't show then either).

For reference, the threads are :

• How to ask "good" questions -- READ BEFORE SUBMITTING A QUESTION!
• POSTING_GUIDELINES: Please Read Carefully Before Posting to a Forum

NB. "The forum" would generally refer to the forum within which the current thread is located. That is Access/VBA Answers and not Access/VBA itself, although as many members navigate from the latter it is still important to make those threads unmissable. I always thought they were, so well done for highlighting that problem.

[Rabbit]

You need to run the query against the max date per item to be tested. Also, there's no need to use the DateDiff function on the test date. Just return everything more than one year before the current date.

[NeoPa]

Rabbit:
There's no need to use the DateDiff function on the test date.

Thank you. I had intended to make that point earlier but overlooked it :-(

Actually, using DateDiff() instead of DateAdd() will often lead to errors, as it will give you the nearest count of the specified interval, when you need an exact match (to the day).

[zmbd]

And see there I learned something between the two functions! I had thought they returned essentially the same thing; thus, didn't suggest using dateadd

[Rabbit]

I would use DateAdd on the Now() function. But not on the fields unless necessary. It's more efficient because it still allows for the use of any indexes that might be there.

[NeoPa]

Rabbit:
I would use DateAdd on the Now() function

Essentially very good advice, but I would never use the Now() function when you are working with dates (as opposed to Date/Time values). Instead use the Date() function, but apply the DateAdd() to that as Rabbit says, definitely.