Newbie Question (1 Viewer)

[LinData]

Hi, the following is a small part of a much larger query in Access.
I'm trying to convert the query to an SQL view, but the syntax has me stumped!

There are 2 problems.
1) [Completed]=False AS OJ (should return 0 if false or 1 if true)
2) IIf([BRef]='B001',0,1) AS BR (should return 0 if field BRef='B001' or 1 if it is not)

I know that the syntax will be different but I can't work out what it should be.

Here is the query I need.

SELECT [Completed]=False AS OJ, IIf([BRef]='B001',0,1) AS BR
FROM [HDM]

Any help would be greatly appreciated!

Thanks Richard

 

[pdx_man]

Statement 1 will not actually assign a value to [Completed] (in Access or TSQL, you need to do an UPDATE query) is this what you want to do?

CASE statements are the IIF equivalent:

SELECT CASE WHEN Completed = 0 THEN 0 ELSE 1 END AS OJ,
CASE WHEN BRef = 0 THEN 0 ELSE 1 END AS BR
FROM HDM

 

[LinData]

Thanks pdx man, I'll give it a try and let you know how I get on.
FYI statement 1 does work in Access, it returns true/false based upon the validity of the test ie "is Completed True? if so return 0", but I see where you're going with the Select Case
Regards Richard

 

[pdx_man]

It returns the value, yes, but it does not update the value within [Completed].

 

[LinData]

Thanks

Thanks pdx man, that works just fine!
Now all I have to do is convert the big query....