Is it possible to have this as a textbox control source:
when the field Q4c is formatted as a text field?
The field Q4c can contain "N/A, 1, 2, 3, 4, or 5" so I think it must be formatted as text due to the "N/A"
But I need to take the average of this field on a report. I tried and it worked once but not again (see post at http://www.thescripts.com/forum/thread768415.html).
How about a better way to find the average of this field? Thanks!
martin
6 2476 FishVal 2,653
Recognized Expert Specialist
Hi, martin.
Try the following: -
=Avg(iif([Q4c]="N/A", Null, Val([Q4c]))
-
Though I would recommend to use Number type field and just replace Null values with "N/A" using Nz() function either in ControlSource or in RowSource SQL when displaying single values. -
SELECT *, Nz([Q4c], "N/A") AS Q4cAlias FROM SourceTable;
-
Regards,
Fish
This
=(Avg(Val[Q4c]))
should do. The Val() function will return zero for all fields where "N/A" appears, and Avg() will include these fields when calculating the average.
Linq ;0)>
Hi, martin.
Though I would recommend to use Number type field and just replace Null values with "N/A" using Nz() function either in ControlSource or in RowSource SQL when displaying single values. -
SELECT *, Nz([Q4c], "N/A") AS Q4cAlias FROM SourceTable;
-
Thanks, FishVal; I want to look into your recommendation.
I converted the datatype of Q4c to Number which deleted all "N/A" from the table, leaving some null values.
Now, on my report, I would set the control source of the text box that was previously
to
Is that correct?
Thanks.
FishVal and missinglinq,
I actually tried both suggestions and both return the same error: "Data type mismatch in criteria expression."
I'm a little unfamiliar with this aspect - what about writing a query that calculates the average and having that query be the control source for the textbox?
Any ideas are most appreciated - thanks.
FishVal 2,653
Recognized Expert Specialist
Hi, Martin.
I'd like to explain the logic of what I've suggested. - you want to calculate average on data set where some values may be absent ("N/A")
- average on text fields definitely has no sence
- if you try to convert your text field to a number, then Val() function will return 0 for "N/A"s thus including them in calculating of average which will cause a wrong result
- on the other hand Avg() will work smoothly with Null values
- so, if you want to stay with a text field. then you need to convert it to a corresponding number with Val() function or to Null if its value is "N/A"
-
=Avg(IIf([FieldName]="N/A", Null, Val([FieldName]))
-
- on the other hand you may convert the field to Number as it supposed to have a numeric values
- but in this case, if you want to see Null values as "N/A"s you'll need to convert them using Nz() function
Hope that this makes a sence
Fish
FishVal,
Thank you very much for breaking down the logic of your response - it always helps to see layperson's terms along-side the code!
I knew taking the average of a text field made no sense, but I wasn't sure how to get around the fact that the fields could include "N/A." So I have done as you suggested and my report is calculating a printing correctly.
Thank you!
martin
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Nicholas Then |
last post by:
I am writing an ASP.NET application and I have a class
that I have written to create a vCard...it just returns a
string with all the necessarry info... Anyway...is there
a way that I can create a...
|
by: Roland Smith |
last post by:
I have recently installed Visual Studio .Net 2003 Professional.
I have written my own source control provider which supports the Microsoft
SCC API. I wrote it using MS Visual C/C++ 6.0 ( which...
|
by: typingcat |
last post by:
First of all, I'm an Asian and I need to input Japanese, Korean and so
on. I've tried many PHP IDEs today, but almost non of them supported
Unicode (UTF-8) file.
I've found that the only Unicode...
|
by: Colin McGuire |
last post by:
Hi, I have an report in Microsoft Access and it displays everything in
the table. One column called "DECISION" in the table has either 1,2,
or 3 in it. On my report it displays 1, 2, or 3. I want...
|
by: Guy |
last post by:
I have extended the datetimepicker control to incorporate
a ReadOnly property.
I have used the new keyword to implement my own version
of the value property, so that if readonly == true then
it...
| |
by: Paulustrious |
last post by:
Hi
The SUO solution file appears to have absolute file addresses. Is it
possible to force it to use relative addresses to make it easier to move
projects and solutions. Or maybe I'm going about...
|
by: Don |
last post by:
I have a page that has several controls on it and the controls are
shown or hidden during postback. I show and hide them using the
visible property. When I use 'view source' in Internet Explorer...
|
by: Dave Calkins |
last post by:
I have a native Win32 C++ app built with Visual Studio 2005. I'd like to
make use of a property grid control in this app. For an example of this, in
Visual Studio, see the properties control...
|
by: Rolf Welskes |
last post by:
Hello,
I have an ObjectDataSource which has as business-object a simple array of
strings. No problem.
I have an own (custom) control to which I give the DataSourceId and in the
custom-control...
|
by: Hexman |
last post by:
Hello All,
I'd like your comments on the code below. The sub does exactly what I want it to do but I don't feel that it is solid as all. It seems like I'm
using some VB6 code, .Net2003 code,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |