Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2011
    Posts
    6

    Unanswered: Can't sum a field calculated based on dates

    Hi,

    I want to generate a table showing total profit and profit brought in the last 18 months by each customer.

    I have a table with all transactions, I have to calculate the profit based on the transactions' amount and other criteria and sum up by customer. For the "last 18 months profit" I also have to filter by date and that has proven an insurmountable problem so far.

    I tried to do it in one query and in two queries and both methods fail.

    Here's the one query attempt:

    SELECT customerno, sum(profit),
    sum(iif(linestable.orderdate>dateadd("d", -548, date()), profit, 0)) as profit18months
    from linestable
    group by customerno

    It produces the "Data type mismatch" error message.

    Two queries attempt:
    in the linestable, I added:
    IIf(orderdate>dateadd("d",-548,date()),profitprod1+profitprod2,0) AS profit18months

    It works.

    However, when I try to sum the field by customer in another query:
    SELECT linestable.customerno, Sum(profit18months)
    FROM linestable
    GROUP BY linestable.customerno;

    It gives the same "Data type mismatch" error.

    When I calculate "total profit" without the date filter, I have no problem. What's the issue?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why don't you use a WHERE clause to select the rows you want to sum?
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    6
    Because I need total profit as well and for that I need all the rows.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I have a feeling that you have bad data in at least one record in the profit field. To find the bad data, you might want to open the table in datasheet view, and sort it on the profit field. Scroll slowly through the table until you find the bad data, and fix it or delete the record.

    BTW, out of curiosity, why do you use "d", -548 to capture the last 18 months, why don't you use "m", -18?

    Sam

  5. #5
    Join Date
    Jul 2011
    Posts
    6
    For the "d" it's just because it's the first thing I thought of.

    Maybe you're right with the bad data. When I try to sort on "orderdate" I also get the "Data type mismatch" error (but then it appears to sort fine). However, the table is +500,000 rows long. Is there any other way to find bad data than "scrolling slowly"?

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Chances are that the bad data is the first x nuimber of records or the last x number of records. Besides, once you start scrolling, you'll get faster and faster.

    Don't forget: when you're looking for numeric data, even spaces are invalid. It must be 0. I'm not too sure about nulls.

    I always set the default for new records to 0 for numeric fields. Access doesn't always do it for me.

    Sam

  7. #7
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Write a quick script for a random button on a random form that:

    1) Opens a recordset, containing all of the profit field
    2) Uses a while loop, that looks something like:
    Code:
    Do While Not (rs.EOF)
         if Not (IsNumeric(rs![profit].value)) Then
              MsgBox "Your error is in record: " & rs![ID].value & vbCrLf & _
                     "It contains the following data: " & rs![profit].value
         End If
         rs.MoveNext
    Loop
    3) Find the flagged up records in your half a million strong database.
    4) Enjoy your database.

    If you have any issues with the rest of the VBA, let me know and I'll try to assist.

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Having seen the answer above, it occurs to me that you can also write a query, as in:
    Code:
    SELECT * FROM linestable WHERE IsNumeric(profit) = False
    I think you can even fix the record right there on screen, but I may be wrong. In any event, you can see the entire record and find them one at a time after printing out the results.

    Sam

  9. #9
    Join Date
    Jul 2011
    Posts
    6
    Thank you all for the suggestions.

    I haven't tried the VBA yet, but the query:
    SELECT * FROM linestable WHERE IsNumeric(profit) = False

    produces something strange.

    First I see the records, then the message "Data type mismatch" appears and when I click "OK", all records are changed to #Name?

    The table (with all the #Name?) has 8,499 rows.

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    H'mmm.

    Try this. Go into the table in design mode, and add a field, maybe t_profit (temp), data type text, and save the design. Write an update query, making
    Code:
    t_profit = IIf(IsNull([profit],"Null",IIf([profit] = " ", "Spaces",IIf(Not IsNumeric([profit]),"Bad Data",CStr([profit]))))
    You can copy and paste the code if you wish into the query designer. See what happens then.

    You may be able to trace the bad records with the same query as before, only substituting the field name "t_profit" for "profit".

    Good luck,
    Sam
    Last edited by Sam Landy; 07-24-11 at 14:55. Reason: forgot something

  11. #11
    Join Date
    Jul 2011
    Posts
    6
    Sam, I haven't tried the formula because I stumbled on the solution.
    After a few changes (reimporting data, changing primary key etc. (to be able to identify records more easily) I retried the
    SELECT * FROM linestable WHERE IsNumeric(profit) = False

    It produced the same "strange" behavior - a table populated by #name? but I noticed the table was 17,876 long. So I went to record 17,876 and sure enough, there was a record with errors nearby. I was generating "orderdate" from the "transaction number" string. As it turned out, one of those strings had 2.006 instead of 2006 in the date section of the transaction number and this is what caused all the problems.

    Thank you all for suggestions, you pointed out it could be bad data rather than a problem with the query.

    I'm new to Access so this behavior surprised me somewhat. I used another problem before (Stata - a rather obscure statistics program) and Stata, in such a situation, would generate "missing data" but would not ultimately stop executing commands.

  12. #12
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I'm glad it worked out for you.

    Happy Accessing!

    Sam

Posting Permissions

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