Calculate field with IIF

[pgordon]

Hi folks,

I have a table of lab results, and I need to either add a calculated field in the table or else generate a query which includes a calculated field.

The lab results contain numbers (to 2 decimal places), and values which could not be accurately measured as they are below detection limit. The latter are all recorded as just being below the detection limit, so the record reads <0.05. For example I could have something like this:

Sample1 0.5
Sample2 0.4
Sample3 <0.05
Sample4 0.08
In this case, all samples that are below detection limit will be recorded as <0.05.

I need to output this table as a csv, to be used in another programme. I want to replace all of the values given as <0.05 as 0.025 (i.e. half of the detection limit - it is standard practice to do this as the programme I need to use the data in cannot cope with non-numeric values).

I don't want to export to excel and carry out a find/replace as I will be getting new results all the time, and there is always a danger of a mistake or data being transposed.

What I need is to create a new field (field B), whereby if the original field (field A) contains <0.05, it is replaced with 0.025, otherwise it simply returns the value contained in field A. As I said, the field could be a new field in the table, or could be a field created in a query.

I don't have any experience with writing code, so I'm afraid you will need to treat me as a complete novice.

Many thanks for your help (I hope).

 

[arnelgp]

your query should look like this:

select [sampleField], Cdbl(IIF(Instr([numericField] & "", "<")>0, 0.025, [numericField])) As Result FROM table

 

[pgordon]

your query should look like this:

select [sampleField], Cdbl(IIF(Instr([numericField] & "", "<")>0, 0.025, [numericField])) As Result FROM table

Thanks for your reply. I'm not sure what I did wrong, but this is what I did:

I created a new, numeric field in the table. then I created a query and in the criteria for [numeric field], I entered your expression, substituting in the correct field names.

It returned the following error message:

The syntax of the subquery in this expression is incorrect. Check the query's syntax and enclose the subquery in parentheses.

I've tried putting in parentheses in a couple of places, but no joy.

 

[arnelgp]

sorry, i thought your field is string, here it is:

select [sampleField], Cdbl(IIF([numericField] <0.05, 0.025, [numericField])) As Result FROM table

 

[pgordon]

sorry, i thought your field is string, here it is:

select [sampleField], Cdbl(IIF([numericField] <0.05, 0.025, [numericField])) As Result FROM table

arnelgp, I'm sorry! I don't think I'm explaining it very well.

I have a field [Ba_ppm], which is a text field, as it contains numbers and <0.05. I want to create a new field [Ba_ppm_CLEAN] which will be entirely numeric.

I don't mind if the new field is in the existing table, or is output as a query (I don't even know whether it's possible to generate a field in a query without that field already existing in a table).

 

[arnelgp]

yes the previous post, the select statement is:

Select [sampleField], Cdbl(IIF(Instr([Ba_ppm] & "", "<")>0, 0.025, [numericField])) As [Ba_ppm_CLEAN] FROM yourtableName;

to update your new numeric field in the same table:

UPDATE yourTableName SET [Ba_ppm_CLEAN] = Cdbl(IIF(Instr([Ba_ppm] & "", "<")>0, 0.025, [numericField])) ;

 

[pgordon]

Thanks so much for trying to help me. I'm still struggling with this, I'm not getting it. I don't think I'm putting the statements in the right places.

Do I create a calculated field and paste in the UPDATE statement above? If not, where do I put the UPDATE statement?