SQL Not working

[Ravi Kumar]

Dear all,
I have a query with the below SQL , which needs to show me the calibrated equipment for this month only.

SELECT [Instruments list].[EQUIPMENT number], [Instruments list].[Calibrated on   mm/dd/yy] as [Calibrated on] , [Instruments list].[Next Calibration      mm/dd/yy] as [Calibration due]
FROM [Instruments list]
where(( [Instruments list].[Calibrated on   mm/dd/yy])=month(now()));

but this is not working(no records shown) , but if I take out the month criteria all records are displayed even with the empty one.
could you please help , how can I filter the same.

 

[Gasman]

You have to match like with like?
Use the Month() function on that really badly named field [Calibrated on mm/dd/yy] in the WHERE clause.

 

[Ravi Kumar]

You have to match like with like?
Use the Month() function on that really badly named field [Calibrated on mm/dd/yy] in the WHERE clause.

Yep it was named before I could take on this database for editing & I left it as it is(sorry) :
And after your suggestion I finally made it worked with this below SQL:

WHERE (((Year([Calibrated on   mm/dd/yy]))=Year(Now())) AND ((Month([Calibrated on   mm/dd/yy]))=Month(Now())))

 

[Gasman]

You could have also perhaps used the Format() function and "yyyymm"? as you never indicated you wanted the year as well.

 

[namliam]

Be casefull with these where clauses, as you are breaking any possibility for the database to use an index... not using indexes can cause serious performance issues over time

You will be better of using something like:
Where Yourdate between dateserial(year(now()), month(now()), 1) and dateserial(year(now()), month(now())+1, 0)