Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    31

    Unanswered: setting up an update query...problem with sql

    Hello,

    Here is what I'm attempting to do. I have tbmain that gets it's data from a form...I'm attempting to set up a tbavg that pulls info from tbmain from which I will pull reports. I am getting an error message that says Access didn't update the 8 fields due to a type conversion failure. Can anyone give me any insight into this error? Below is the sql for this query.

    UPDATE DISTINCTROW tbmain LEFT JOIN tbavg ON tbmain.[Call ID] = tbavg.[Call ID] SET tbavg.[Call ID] = [tbmain].[Call ID], tbavg.[Call Type] = [tbmain].[Call Type], tbavg.[Analyst Name] = [tbmain].[Analyst Name], tbavg.Evaluator = [tbmain].[Evaluator], tbavg.[Date] = [tbmain].[Date], tbavg.Q1 = ([tbmain].[1a]+[tbmain].[1b])/2, tbavg.Q2 = ([tbmain].[2a]+[tbmain].[2b])/2, tbavg.Q3 = ([tbmain].[3a]+"tbmain[3b]")/2, tbavg.Q4 = ([tbmain].[4a]+[tbmain].[4b])/2, tbavg.Q5 = ([tbmain].[5a]+[tbmain].[5b]+[tbmain].[5c])/3, tbavg.Q6 = ([tbmain].[6a]+[tbmain].[6b]+"tbmain[6c]")/3, tbavg.Overall = ([tbmain].[1a]+[tbmain].[1b]+[tbmain].[2a]+[tbmain].[2b]+[tbmain].[3a]+[tbmain].[3b]+[tbmain].[4a]+[tbmain].[4b]+[tbmain].[5a]+[tbmain].[5b]+[tbmain].[5c]+[tbmain].[6a]+[tbmain].[6b]+[tbmain].[6c])/(IIf([tbmain].[6a]=0,11,14)), tbavg.Comments = [tbmain].[Comments], tbavg.[Training Needs] = [tbmain].[Training Needs]
    WHERE (((tbavg.[Call ID]) Is Null));

  2. #2
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    I'd start by making sure that each of the fields in the calculation can be evaluated to a number.
    Cathy

  3. #3
    Join Date
    Jan 2004
    Posts
    31
    some of the fields are text and others are memo. Is there special rules for them?

  4. #4
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    Text and memo fields must be characters. By looking at what you have here, my first thought is that you are dividing by something that cannot be evaluated as a number.
    Cathy

  5. #5
    Join Date
    Jan 2004
    Posts
    31

    Unhappy

    The fields that are being divided are all numerical fields, the text and memo fields are the ones in the beginning of the sql and the two fields at the end.

Posting Permissions

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