CODEWITHSUNDEEP
sqlms-access
J.H
J.H

Reputation: 181

How to show rows in MS Access that has enddate<startdate?

Basically I want to check if any end dates are earlier than the start date (which would be wrong). Therefore I decided to check the right 4 characters of the string and compare with a greater than. The code works but nothing happens even when I change the dates to be intentionally wrong. The format is mm/dd/yyyy.

SELECT *
FROM [myTable]
WHERE
RIGHT ('EndDate',4)<RIGHT ('StartDate',4)
;

The table given to me wrote the date columns in 'Short Text'. I can change it to date, but then I am not too sure how to use DATEDIFF().

Upvotes: 1

Views: 46

Answers (2)

Sergey S.
Sergey S.

Reputation: 6336

For string comparing use this:

SELECT *
FROM [myTable]
WHERE
RIGHT ([EndDate],4)<RIGHT ([StartDate],4)

But it won't work for dates within one year because you compare only years. It would work for date format yyyy/mm/dd, Right function not needed in this case.

Best solution - convert text to date using CDate and use the same operator for comparing - "<"

Upvotes: 2

HansUp
HansUp

Reputation: 97101

RIGHT('EndDate',4) and RIGHT ('StartDate',4) each return the 4-character string, Date. So your query's WHERE condition was equivalent to 'Date' < 'Date', which was False for every row. Don't put the field names inside quotes.

And date comparisons are less troublesome with Date/Time values instead of dates as strings. Use CDate() to cast your date strings to real Date/Time values. Then the comparison is straightforward ...

SELECT m.*
FROM myTable AS m
WHERE CDate(m.EndDate) < CDate(m.StartDate);

If it's practical to change those fields' datatypes to Date/Time, the query could be simpler and perform faster.

Upvotes: 2

Related Questions