Expression results not showing when Query is run

[joce]

Hi,

I have made several calculations using expression builder in one of my forms, and now before i create my report I am trying to use the query wizard or builder to pull out the information which i want from my tables, but when I run the query, the results from the expressions/calculation which I had functioning in the form, the results are not showing up in the query.

The expression which I am talking about currently trying to egt it to show, is one which shows the average of the results of 10 fields combined i.e. I have 10 body condition scores of animals which this calculation finds the average of them combined.

Also do I really need to always use a query builder before forming a report if I am taking a range of things from tabels or different tables combined?

I am enw to this, so any help at all would be greatly appreciated.

Joce

 

[vbaInet]

Can we see the expression.
What section of the form and report did you put the expression?

 

[joce]

The expression is just placed in the main body of the form.

The expression is as follows....
= ([Cow 1 BCS] + [Cow 2 BCS] +[Cow 3 BCS]+[Cow 4 BCS]+[Cow 5 BCS]+[Cow 6 BCS]+[Cow 7 BCS] + [Cow 8 BCS]+[Cow 9 BCS]+[Cow 10 BCS]) / 10

 

[vbaInet]

Can you tell me what the data types of each of the fields are? Are they Text?

 

[joce]

No they are numbers.

 

[vbaInet]

I don't mean the value. What is the Data type? Is Cow 1 BCS a field in a table?

 

[joce]

Yes it is.

 

[vbaInet]

So why not move that expression to the query?

 

[joce]

I have tried that but I am not sure that I am doing it correctly. I thought if i just selected the field with the expression in it, in the query wizard it would just work, but it doesnt.

 

[vbaInet]

Just put the following in a new column in the query:

([Cow 1 BCS] + [Cow 2 BCS] +[Cow 3 BCS]+[Cow 4 BCS]+[Cow 5 BCS]+[Cow 6  BCS]+[Cow 7 BCS] + [Cow 8 BCS]+[Cow 9 BCS]+[Cow 10 BCS]) / 10

Notice it's without the equal to sign (=)

Also try wrapping all the fields in Nz() if it's still not working. E.g.

Nz([Cow 1 BCS], 0)