Thread: expression builder
07-23-13, 10:05 #1Registered User
- Join Date
- Aug 2011
Unanswered: expression builder
Using access 2010 query and trying to add or average several fields, some have alpha. Want to evaluate for numeric and then add number to field total. Not having luck. So far have tried with expression builder
QuestTotal: isnumeric(int([question])) + isnumeric(int([question]))
Have tried int(isnumeric........ have tried I if statements. Nothing works. Get errors like missing operand or missing ) or ] but I cannot see any that are missing.
Can someone please help?
07-23-13, 11:11 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
can we see the sql please
switch to the sql view (button under the file tab)thne paste what code you've got.
you probably need something like
avg(iif not isnumeric(mycolumn),NULL,mycolumn) as averageofmycolumn
this is a symptom of bad column design. if the value shudl be numeric then make it numeric, use the appropriate datatype of the range of values you expect.
so you may need to use a compound iif to handle the nulls
avg(iif(not isnull(mycolumn),iif isnumeric(mycolumn),mycolumn,NULL),NULL) as AverageOfMyColumnI'd rather be riding on the Tiger 800 or the Norton