Unanswered: Can't sum a field calculated based on dates
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
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
However, when I try to sum the field by customer in another query:
SELECT linestable.customerno, Sum(profit18months)
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?
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?
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"?
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:
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
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.
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.