SQL Query 1

[carpetbelly]

I'm trying to create a query. I have a field called 'HalfDay' that's a Yes/No. I also have another field that works out how many working days between dates there are. What I am trying to do is when the Yes/No field is set to true how I can default the working day field to 0.5.

Below is the SQL I thought would work and the code I seem to be getting an error with...

SELECT 
  tblStaff.StaffName
, tblHoliday.StartDate
, tblHoliday.EndDate
, CASE 
    when tblHoliday.HalfDay=  1 then "0.5"
    when tblHoliday.HalfDay =  0 then myCalcHolDays([StartDate],[EndDate])
  END as days
FROM 
  tblStaff 
    INNER JOIN 
  tblHoliday 
    ON tblStaff.StaffID = tblHoliday.StaffID
;

 

[carpetbelly]

Now I feel like a wally just realising you cannot use the CASE statement from what I've read and now it works...

Doh!

 

[Remou]

Do you wish to create this query for Microsoft: Access Queries and JET SQL or SQL Server, or some other SQL?

 

[carpetbelly]

I was doing this in access... Hence why I couldnt use the CASE statement.

 

[CaptainD]

If I uderstand you correctly, you got it to work?

If not, would this work:

Note: I'm not sure if you can call a function from inside an "IIF" but it might be worth a try.

SELECT 
  tblStaff.StaffName
, tblHoliday.StartDate
, tblHoliday.EndDate
, IIF(tblHoliday.HalfDay = 1, "0.5", myCalcHolDays([StartDate], [EndDate])) AS [Days]
FROM 
  tblStaff 
    INNER JOIN 
  tblHoliday 
    ON tblStaff.StaffID = tblHoliday.StaffID
;

 

[carpetbelly]

CaptainD, yep, I was able to call my function within the IIF which got it working.

Spot on suggestion you've given there though, bang on the same as the code I ended up with. Which makes me happy as I know I got something right at least