kg6lfz
asked on
Convert string date and select older than 30 days from today
[Order In] is a string field and records are like 20170301.
I want to convert [Order in] field to date format first, then need to select them when [Order In] are older than 30days from today.
Select * from Order
Where DateDiff("d",CDate([Order In]),Date()) > 30
I get an error saying "Data type mismatch in criteria expression." Not sure how to fix the error. I appreciate your suggestions and comments. Thank you in advance.
I want to convert [Order in] field to date format first, then need to select them when [Order In] are older than 30days from today.
Select * from Order
Where DateDiff("d",CDate([Order In]),Date()) > 30
I get an error saying "Data type mismatch in criteria expression." Not sure how to fix the error. I appreciate your suggestions and comments. Thank you in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
A few tips. If at all possible
A) Rename the [Order In] column to remove the space so that column doesn't need escaping in every query
B) Convert the column data type to datetime. This will improve performance and simplify comparisons. My Access is very rusty, but something like:
WHERE OrderIn < DateAdd("d", -30, Date())