SQL Not working (1 Viewer)

Ravi Kumar

Registered User.
Local time
Today, 22:44
Joined
Aug 22, 2019
Messages
162
Dear all,
I have a query with the below SQL , which needs to show me the calibrated equipment for this month only.
SQL:
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

Enthusiastic Amateur
Local time
Today, 17:14
Joined
Sep 21, 2011
Messages
14,044
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

Registered User.
Local time
Today, 22:44
Joined
Aug 22, 2019
Messages
162
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:
SQL:
WHERE (((Year([Calibrated on   mm/dd/yy]))=Year(Now())) AND ((Month([Calibrated on   mm/dd/yy]))=Month(Now())))
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:14
Joined
Sep 21, 2011
Messages
14,044
You could have also perhaps used the Format() function and "yyyymm"? as you never indicated you wanted the year as well.
 

namliam

The Mailman - AWF VIP
Local time
Today, 18:14
Joined
Aug 11, 2003
Messages
11,696
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)
 

Users who are viewing this thread

Top Bottom