Problem with calculated expression in a query

[lilAndy]

My query seems to be playing up, and I'm not entirely sure why at the moment. Can anyone shed any light on this matter?

I have a query which is constructed from a table containing the following fields:

• %w/w
• Assay (mg/ml)
• Calc %w/w

The third item (Calc %w/w) is the following:
Calc %w/w: IIf(Nz([%w/w],0)=0,IIf(IsNumeric([Assay (mg/ml)]),round(([Assay (mg/ml)]/11.86),3),"N/A"),[%w/w])

So basically:
If (%w/w = null or %w/w = "") then
if the assay is a number then
divide by 11.86 and round to 3dps
otherwise display "N/A"
otherwise display the value entered into %w/w

The problem seems to be with the division and rounding.
If I put the following statement in instead it performs the task well (aside from the fact that it isn't doing the division and isn't rounding), and displays "N/A" if the Assay field is non-numeric or if it is "".

Calc %w/w: IIf(Nz([%w/w],0)<>0,[%w/w],IIf(IsNumeric([Assay (mg/ml)]),[Assay (mg/ml)],"N/A"))

However, as soon as I put the division or rounding in, instead of displaying "N/A", it displays "#Error" instead.

Any clues?

 

[neileg]

I don't think it's causing your problems, but it's bad practice to use special characters in your field names. It's better to stick to alpha numeric caharcters, a-z, 0-9, no punctuation, no spaces.

Just to make matters worse, I have set up a db with your field defs and dropped your calculation into a query, special characters and all, and it works for me.

 

[lilAndy]

Hi Neil:
Do you mean special characters as in the "%" and "/" signs?
Isn't the punctation a necessary part of the IIF() and round() functions?

I'm sure this was working for me at one point too... maybe something else is interfering with it... looks like I'm going to have to try debugging it more.

Thanx, Andy

 

[Mile-O]

Do you mean special characters as in the "%" and "/" signs?

That's exactly what he means. Others you've used are the open and closed brackets in field names.

It's a better practive to name the field, as Neil says, as one string of text, with each word in the name being capitalised, the remaining letters in lower case.

i.e.

WeeklyStats

CalculatedAverage

WeeklyPercentage


etc.


It's also much more descriptive of the information the field holds.

 

[lilAndy]

I'll have to look into that, though those field names are exactly what they should be description-wise: the access database I'm creating is for a big medical / healthcare organisation and this is where they will be storing all of their data once it has been migrated. My point is that they might not want the field names to be changed, as they've been using them for several years worth of data collection.

Considering the main problem at hand though, I'm still fairly stumped at the moment, as this:
Calc %w/w: IIf(Nz([%w/w],0)<>0,[%w/w],IIf(IsNumeric([Assay (mg/ml)]),[Assay (mg/ml)],"N/A"))
works just fine, but this:
Calc %w/w: IIf(Nz([%w/w],0)<>0,[%w/w],IIf(IsNumeric([Assay (mg/ml)]),[Assay (mg/ml)]/11.86,"N/A"))
gives me a "#Error"...

 

[lilAndy]

Okay, I've solved this now, thankyou for your replies
It looks like it was struggling while it was trying to recognise what I wanted it to do, so I clarified it by adding in even more parenthesis.

Calc %w/w: IIf(Nz([%w/w],0)<>0,[%w/w],IIf(IsNumeric([Assay (mg/ml)]),(round(([Assay (mg/ml)]/11.86),3)),"N/A"))

~phew~

 

[neileg]

Pleased it is working, now.

However, I would make the following points:
1) The name of the fields should be completely irrelevant to the user, as you should not be displaying your data in a table. Tables are for storing data, Forms and reports are for displaying data and getting user input.
2) Mile has correctly explained my point about special characters. The dangers of using these includes:
a) It is harder read your own code and spot the difference between your field names and the arithmetic operators
b) If you make an error in your typing, you can end up with a formula that works accoprding to Access syntax, but does not give the result intended

 

[lilAndy]

Very good points... I'll start looking into it soon.