Help with Iif calculation

[guinness]

Total Hours: IIf([Time]="All Day",[Work Days]*7.4,[Work Days]*3.7)

So what I am looking to return in English is: If the time entered in the Time field is All day then multiply the figure in the Work Days field by 7.4. If the entry in Time isn't All Day then multiply the figure in the Work Days field by 3.7

Bet I'm missing something brutally obvious.

Been too close to this for too many hours.

Thanks in advance

Guinness

 

[BlueIshDan]

Where are you putting this IIF statement?

 

[guinness]

The result of the Iif statement will create the figure in the new Total Hours column. I'm using the expression builder function.

Does that help?

 

[BlueIshDan]

Nope not really lol.
Report, Query, Form ?

 

[guinness]

Sorry Dan. Got you now. I'm working in a query. Creating a calculated value based on other fields in the same query.

 

[BlueIshDan]

Could you provide the whole query?

 

[guinness]

See if the attached PDF helps

 

[pr2-eugin]

Copy the SQL and provide it here !

 

[guinness]

Here you go:

SELECT Resourcing.Project_Title, Resourcing.Start_Date, Resourcing.End_Date, Resourcing.Time, Resourcing.Trainer_Name, Resourcing.Training_Type, dhCountWorkdaysA([Start_Date],[End_Date]) AS [Work Days], Resourcing.Activity, IIf([Time]="All Day",[Work Days]*7.4,[Work Days]*3.7) AS [Total Hours]
FROM Resourcing;

Thanks all for taking time to look at this

 

[pr2-eugin]

Try this,

SELECT Resourcing.Project_Title, Resourcing.Start_Date, Resourcing.End_Date, Resourcing.[Time], 
Resourcing.Trainer_Name, Resourcing.Training_Type, 
dhCountWorkdaysA(Resourcing.Start_Date, Resourcing.End_Date) AS [Work Days], 
Resourcing.Activity, IIf(Resourcing.[Time] = "All Day", [Work Days]*7.4, [Work Days]*3.7) AS [Total Hours]
FROM Resourcing;

 

[guinness]

Thanks Paul.. I see where you were going with that but unfortunately it still returns the same error.

 

[BlueIshDan]

what is that error?

 

[pr2-eugin]

I have seen you have the habit of using Lookup fields in the tables. So I am guessing that the Time field might also be lookup? In that case you are trying to compare Number to a String, which might be a reason for the error?

 

[guinness]

As in the screenshots from earlier. It runs the query but shows #ERROR instead of a number in the result

 

[namliam]

*brrrr* the column names Time, Work days, etc... It is good practict that one doesnt use reserved words or special characters

Trouble here is you are re-using a column that is a calculation itself, dont think you can re-use a column in the same query
try

Select *, IIf([Time]="All Day",[Work Days]*7.4,[Work Days]*3.7) AS [Total Hours]
from (
SELECT Resourcing.Project_Title, Resourcing.Start_Date, Resourcing.End_Date, Resourcing.Time, Resourcing.Trainer_Name, Resourcing.Training_Type, dhCountWorkdaysA([Start_Date],[End_Date]) AS [Work Days], Resourcing.Activity
FROM Resourcing)

 

[pr2-eugin]

Trouble here is you are re-using a column that is a calculation itself, dont think you can re-use a column in the same query

Think that only applies for TOTALS queries. Where you can still use the calculation in aggregation, it just you have to use the actual calculation again in the TOTALS clause instead of using the alias.

 

[guinness]

Thanks All

Paul gets the prize. I do use lookup tables where possible to ensure the correct entry format but when I took the lookup off the original Iif calculation worked without any amendments. That was driving me nuts.

Thanks again

 

[pr2-eugin]

Time for some medication to cure your amateur DB skills.

I do use lookup tables where possible to ensure the correct entry format

Absolutely NOT a reason to have Lookup in table still. You use Form's to enter data into table, that is where you will be using a ComboBox/Lookups. The table should have only the raw data, barely numbers. Yes it will not be easily understandable; but will make your DB more smooth, without much bloat, efficient, and quicker.

Here is a great article on the Evils of Lookup fields ! Good luck !

 

[CazB]

another option is you 'repeat' the calculation for work days within the Iif, ie

 SELECT Resourcing.Project_Title, Resourcing.Start_Date, Resourcing.End_Date, Resourcing.[Time], 
Resourcing.Trainer_Name, Resourcing.Training_Type, 
dhCountWorkdaysA(Resourcing.Start_Date, Resourcing.End_Date) AS [Work Days], 
Resourcing.Activity, IIf(Resourcing.[Time] = "All Day", dhCountWorkdaysA(Resourcing.Start_Date, Resourcing.End_Date)*7.4, dhCountWorkdaysA(Resourcing.Start_Date, Resourcing.End_Date)*3.7) AS [Total Hours]
FROM Resourcing;

 

[pr2-eugin]

another option is you 'repeat' the calculation for work days within the Iif, ie

A simple phrase comes up to my mind - Overloaded Query