Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011
    Posts
    4

    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?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    eg
    avg(iif(not isnull(mycolumn),iif isnumeric(mycolumn),mycolumn,NULL),NULL) as AverageOfMyColumn
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •