CODEWITHSUNDEEP
sqlms-accesscase
Ramzendo
Ramzendo

Reputation: 606

MS Access query not working

could anybody explain me what's wrong with this query?

SELECT Attempts, (CASE WHEN LastLogin IS NOT NULL AND DATE_ADD(LastLogin, INTERVAL 1 MINUTE)>NOW() THEN 1 ELSE 0) AS Denied 
FROM tbl_loginLocks 
WHERE IP = "192.168.178.43";

I got an error that a operation is missing. Any help would be appreciated. Thanks!

Upvotes: 2

Views: 128

Answers (3)

SchmitzIT
SchmitzIT

Reputation: 9552

You're missing END at the end of the CASE:

SELECT Attempts, (CASE WHEN LastLogin IS NOT NULL AND DATE_ADD(LastLogin, INTERVAL 1 MINUTE)>NOW() THEN 1 ELSE 0 END) AS Denied 
FROM tbl_loginLocks 
WHERE IP = '192.168.178.43';

Alternatively, use an IIF statement:

SELECT Attempts, IIF(LastLogin IS NOT NULL AND DATEADD("n", 1, LastLogin) >NOW(), 1, 0) AS Denied 
FROM tbl_loginLocks 
WHERE IP = '192.168.178.43';

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91366

MS Access does not have a case statement, use IIF:

SELECT Attempts, 
IIf(Not IsNull(LastLogin) AND DATEADD("n",1,LastLogin)>NOW(),1,0) AS Denied 
FROM tbl_loginLocks 
WHERE IP = "192.168.178.43"

Dateadd
IIF

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Add END at the end of CASE statement

Your Date_Add function is wrong

The Microsoft Access DateAdd function returns a date after which a certain time/date interval has been added.

SYNTAX

The syntax for the Microsoft Access DateAdd function is:

DateAdd ( interval, number, date )

Just Try this

SELECT Attempts, (CASE WHEN LastLogin IS NOT NULL AND DateAdd ("n",1,LastLogin)>NOW() THEN 1 ELSE 0 END) AS Denied 
FROM tbl_loginLocks 
WHERE IP = "192.168.178.43";

Upvotes: 1

Related Questions