update query

Do you mean where the date is between 30 days ago and 30 days from now?

Update ADS
      set AdsStatus = 5
      where AdsDate BETWEEN (CURRENT_DATE()- 30)  AND (CURRENT_DATE() +30) AND 
   (MainCategory = 4 or MainCategory = 5 or MainCategory = 6) 

Select allOpen in new window

For Access:

Update ADS
      set AdsStatus = 5
      where AdsDate BETWEEN (DATE()- 30)  AND (DATE() +30) AND 
   (MainCategory = 4 or MainCategory = 5 or MainCategory = 6) 

Select allOpen in new window

I want to calculate the date difference between

today's date and AdsDate (than say update the record when the difference is more than 30 days)

Update ADS
      set AdsStatus = 5
      where CURRENT_DATE()-AdsDate > 30 and (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)

I would just replace CURRENT_DATE()-AdsDate with DATEDIFF(day,AdsDate,CURRENT_DATE()). SQL sometimes does weird things with direct date calculations.

Try this

Update ADS
      set AdsStatus = 5
      where datediff(day,AdsDate,getdate()) > 30 AND 
   (MainCategory = 4 or MainCategory = 5 or MainCategory = 6) 

Select allOpen in new window

This will work for a difference of more than thirty days in either direction (before or after)

Update ADS
      set AdsStatus = 5
      where (CURRENT_DATE()-AdsDate > 30  OR AdsDate - CURRENT_DATE() > 30) and (MainCategory = 4 or MainCategory = 5 or MainCategory = 6) 

Select allOpen in new window

In Access:

Update ADS
      set AdsStatus = 5
      where (DATE()-AdsDate > 30  OR AdsDate - DATE() > 30) and (MainCategory = 4 or MainCategory = 5 or MainCategory = 6) 

Select allOpen in new window

Update ADS
      set AdsStatus = 5
      where datediff(day,AdsDate,getdate()) > 30 AND
   (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)  



Error Executing Database Query.  
Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'getdate()) > 30 AND (MainCategory = 4 or MainCategory = 5 or MainCategory =' at line 3  

MySQL uses NOW() instead of GETDATE()

Update ADS
      set AdsStatus = 5
      where datediff(day,AdsDate,NOW()) > 30 AND
   (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)  



Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW()) > 30 AND (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)' at line 3

MySQL datediff is also different. Try this:
Update ADS
      set AdsStatus = 5
      where datediff(AdsDate,NOW()) > 30 AND
   (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)  

Thank you