Formatting numeric data in a calculated query to not show too many significant figure

[Peakwalker]

SampleDatabase.zip



I've never attached anything before, I hope this has worked.

My description of the file and queries:

The table _Assay contains a sample number, with data CU_A1 and CU_A2. The CU_PLOT field merges the two, as data from CU_A2 is used preferentially over CU_A1.
The table _Assay_Standards is the same but does not have the same combined plot field. However data from A2 is still used preferentially over A1.
Note for both tables there are only three decimal places for each value. There are also null values in each table, which happens as we use different assay methods, different labs, etc.
It is important that the assay and standards tables remain separate, but they do at times need to be reviewed together. To accomplish this, I’ve created a joined table, lut_master_sample_number_list, which combines the sample numbers from both the assay and standards tables into one.
Then, all three tables are used within the lut_master_assay_data table, which is meant to show all sample numbers, and their preferred values, as one.
I’ve done a few different queries to try and figure out the issue.
CU 1 shows how the data looks on its own, without me specifying the number of decimal places, rounding, currency, etc. As you can see, it displays as a float with many decimal places.
CU 2 is exactly the same but adding the round function. No change.
CU 3 is the same as two but I have specified zero decimal places to round to. Although it won’t round to 3 in CU 2, it will round to zero places in this example.
CU 4 shows the same as CU 2 but with the CCur function added. Now the data is displaying correctly to three decimal places, but I get a #error for all null values. Note the round function works as you would expect in this example, if you change it to two decimal places, then it only shows 2.
CU 5 is the same as CU 1 again, but in the property sheet, I have specified that the format is fixed, with 3 decimal places. In this case it visually seems to display correctly, but if you click on a cell, the data is all still there.
CU 6 is the same as CU 5 but with the round function added. So it is fixed at 3, and rounded to 3, but again, if you click on a cell, there are still many sig figs.
CU 7 has your solution of using the IsNull function.
CU 8 is the same as CU 7 but with rounding. No change.
CU 9 is the same as CU 8 but changed to currency. Same result as CU4, correct decimal places, but error message showing.
The final answer should be the values as displayed in CU 4 and CU 9 but without any #error messages in the null fields. They should still remain blank like all the other examples.

[JoeM]

That is odd. I don't think I have seen that before.
I never really use the Switch function, so perhaps that is part of the cause of the issue. It does say that Switch returns a Variant value.

Maybe try creating a User Defined Function instead to do your calculation. Perhaps that will work?

[Peakwalker]

Thanks for looking at it, you can see why I got stumped!

I'm not sure how to do user defined functions, and really, although frustrating and annoying, it's not like I can't still do my work. I'll leave this as being unsolved, maybe someone else wants to play with it in the future.

[JoeM]

Yes, sorry I couldn't be of more help. Hopefully, someone else will have some ideas.

[June7]

Does this get you what you want:

SELECT lut_master_sample_number_list.SAMPLE_NO, Val(Nz([_Assay_Standards]![CU_A2],Nz([_Assay_Standards]![CU_A1],Nz([_Assay]![CU_PLOT],0)))) AS [CU10], IIf([CU10]=0,Null,[CU10]) AS [CU10Adj]
FROM (lut_master_sample_number_list LEFT JOIN _ASSAY ON lut_master_sample_number_list.SAMPLE_NO = [_ASSAY].SAMPLE_NO) LEFT JOIN _ASSAY_STANDARDS ON lut_master_sample_number_list.SAMPLE_NO = [_ASSAY_STANDARDS].SAMPLE_NUMBER;


BTW, several fields are set to display 4 decimals. I seldom bother with format settings in tables.