Use Max function in a query

[MilaK]

I would like to use the following formula in a calculated field in a query:

CorrCN: Max(0,(([S1_Raw-CN]-2*(1-[S1_Cellularity]))/[S1_Cellularity])))

I need to use Max function to return 0 if the formula returns a negative value.

When I type this formula into a field, I get the following error when i run the query. "The expression you entered has a function containing the wrong number of arguments.


Could you please explain how I need to enter this formula correctly?

Thank you

 

[stopher]

The purpose of MAX() is to return the max value over a set of records. So it only have one input i.e. the field in which we are looking for the max value.

There is no equivalent to the max function in Excel. So here's three possible options:

- Use IIF(a<0,0,a)

- write your own max function

- reference Excel and use the excel function

 

[MilaK]

Could you please show examples? Thanks

 

[sneuberg]

This site has some examples.

 

[MilaK]

Here is the SQL statement that returns nothing:

SELECT tbl_CNVs.[Chr:Pos:Gene], tbl_CNVs.Run_Name, tbl_CNVs.Sample1_Name, tbl_CNVs.S1_Sample_Type, tbl_CNVs.S1_Cellularity, tbl_CNVs.[S1_5%_CI], tbl_CNVs.[S1_95%_CI], tbl_CNVs.S1_Raw_CN, ([S1_Raw_CN]-2*(1-[S1_Cellularity]))/[S1_Raw_CN] AS CN
FROM tbl_CNVs
WHERE (((([S1_Raw_CN]-2*(1-[S1_Cellularity]))/[S1_Raw_CN])=IIf([CN]<0,0,[CN])));

What is incorrect here?

Thanks

 

[sneuberg]

This appears to select records where [CN] is greater or equal to 0. If that's what you want why not just have [CN] >= 0 in the WHERE clause

 

[MilaK]

this query should work but it doesn't return anything.

SELECT tbl_CNVs.[Chr:Pos:Gene], tbl_CNVs.Run_Name, tbl_CNVs.Sample1_Name, tbl_CNVs.S1_Sample_Type, tbl_CNVs.S1_Cellularity, tbl_CNVs.[S1_5%_CI], tbl_CNVs.[S1_95%_CI], tbl_CNVs.S1_Raw_CN, ([S1_Raw_CN]-2*(1-[S1_Cellularity]))/[S1_Raw_CN] AS CN
FROM tbl_CNVs
WHERE IIf([CN]<0,0,[CN]);

I can't figure out what I am missing, please help.

 

[stopher]

Can you explain what it is you are trying to do in more general terms.

 

[MilaK]

I think I got it: CN: IIf(([S1_Raw_CN]-2*(1-[S1_Cellularity]))/[S1_Raw_CN]<0,0,([S1_Raw_CN]-2*(1-[S1_Cellularity]))/[S1_Raw_CN])

Thanks

 

[stopher]

Nice one, well done.