Display records with test dates 90 days or older

[m0aje]

Hello All,



I have a database where my computers and printers are tested every 90 days. An example record lists the computer model, serial number, and date it was tested. It is supposed to be retested again 90 days later. I have records that still exist from 10 years ago, but have been trying to single out the ones that are older than 90 days in THIS calendar year.
In my simple query in the criteria in my DATE field, I have tried. <Date() -90, but that pulls everything from 10 years ago. I tried "Like"*"[Calendar Year]&"*" AND <Date()-90 and no joy. I tried Between[Enter Calendar Year]AND[Enter Todays Date]& Date() <=90.

I can't seem to figure out the right sequence for the criterial for this one. The dates on the test sheets are long format i.e. September 9, 2012.

Any help would be greatly appreciated.

Thank you,

m0aje

[recyan]

Till some one comes along, just check out if below gives some guidelines :

SELECT 
	myTable.TheDates, 
	DateDiff("d",[TheDates],Date()) AS NoOffDays, 
	Year([TheDates]) AS TheYearOfTheDates
FROM 
	myTable
WHERE 
	(
		(
			(DateDiff("d",[TheDates],Date()))>90) 
			AND 
			((Year([TheDates]))=Year(Date())
		)
	);

Thanks

[m0aje]

Hello recyan,

THANK YOU for your reply. I appreciate it. Does this query have to be done in VBA code and incorporated? I am not at all versed in VBA code or how to incorporate it into my existing database. The queries I have created were done in the "query wizard" and then I would edit the criteria line under the DATE field such as "Between[Start Date]AND[End Date] or Like"*"[Enter Last Name]&"*" under the NAME field. I have been feeling my way through this thing and in a lot of cases, I have been able to parallel other examples and get lucky. I am still a novice.
Again, thank you for your reply and your asistance. I really appreciate it.
Best regards,
m0aje

[recyan]

Hi,
You can do it
1) in the Query Builder.
or
2) In the SQL view of the query builder.
You can try copying the above query in the SQL view of the Query Design window after replacing myTable with your table name & TheDates field name with the field at your end which contains the Dates. Then run the query & see how it goes. If it runs fine, open the query in Design Grid Mode. You should be able to see what is happening.

Thanks

[m0aje]

Hello again recyan,

I think I got it!!! THANKS VERY MUCH!!! I put the formulas in the DATE field and when I ran the query then back to the design, it moved the criteria to another place and put Expr1 and Expr2 in front of it. Anyway, it appears to be doing what I want it to do. It is pulling every record >=90 days.
I also have another field called "shipped to". I want to be able to exclude any records where this field has ANY data in it. Data in this field indicates it has been shipped. Can this be done using NULL? Such as in the query box under the "shipped to" field the criteria would be IS NULL? Would that pull the all the records where this field is left blank and exclude the ones where there is an entry in this field?

Again, thanks very much for your help recyan. I really appreciate it.

r/ m0aje

[recyan]

.... put Expr1 and Expr2 in front of it.

The query builder assigns aliases by default such as Expr1. You can replace Expr1, Expr2 with whatever suitable name you want. Avoid giving the same name as your table fields.

........... Can this be done using NULL? Such as in the query box under the "shipped to" field the criteria would be IS NULL? Would that pull the all the records where this field is left blank and exclude the ones where there is an entry in this field?

Try what you are thinking. I think it should work.
Would suggest using default values in Table fields, rather than allowing nulls.
Sometimes, nulls are tricky to handle.

Thanks

[m0aje]

Hello again recyan,

OK. I'll give it a try on Monday. Again, many thanks for the help. I appreciate it.

r/m0aje

[m0aje]

Greetings recyan,

I added the "shipped to" to my query and tried the IS NULL in the criteria and that seemed to do the trick. So far it is working great. Again THANK YOU for helping me with this query. I appreciate it.

Best regards,

m0aje

[recyan]

Glad you got things working.

Thanks