Adding amounts based on dates - combing "sum", "iif" and dat

[justine1364]

I have a table with information on sales. I want to generate a table showing:
- total sales per customer
- sales per customer in the last 18 months

The following code (which doesn't sum up anything) works:

SELECT usdsales, iif(table1.orderdate>dateadd("d", -548, date()), usdsales) as sales18months
from table1

When I try to sum up, it stops working and shows "Data type mismatch in criteria expression" message.


SELECT customerno, sum(usdsales),
sum(iif(table1.orderdate>dateadd("d", -548, date()), usdsales)) as sales18months
from linestable
group by customerno

How can I fix it?

Thank you

 

[SkipVought]

Your IIF statement has not ELSE value!

What is Table1???

SELECT customerno, sum(usdsales), 
sum(iif([b]table1.[/b]orderdate>dateadd("d", -548, date()), usdsales,0)) as sales18months
from linestable
group by customerno

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[justine1364]

Sorry for "Table1" confusion. It should have been "from table1" in the second example as well.

You're suggesting it doesn't work because I didn't put the else clause? I can't check now because I'm not at work, but I'll try with a 0 tomorrow.

Thank you for the suggestion.

 

[dhookom]

I would try:

SELECT customerno, sum(usdsales), 
Sum(Abs(orderdate>=DateAdd("m", -18, date())) * usdsales) as sales18months
FROM Table1
GROUP BY customerno

Duane
Hook'D on Access
MS Access MVP

 

[justine1364]

I'm getting the same error with both your suggestions

 

[dhookom]

We are both assuming OrderDate is an actual date/time field and is not null and usdsales is a numeric field and is not null.


Duane
Hook'D on Access
MS Access MVP

 

[justine1364]

I think I'm soon going to give up and just use two queries.

usdsales is a numeric field but it has blank fields. I don't think it's the problem though. When I use a condition different from date, it works. Eg.

sum(iif([no]='3200', usdsales, 0)) as sales

Orderdate is a date and I think it has no blank fields. When I click on “orderdate” in table1 (which is itself already a result of a query), I see “date filters”.

I did a simpler query to see if orderdate and the second date are both dates and in the same format. When I do that:

SELECT table1.customerno,
orderdate, dateadd("d", -548, date()) as secondDate
from table1

both orderdate and secondDate are in the same format "dd/mm/yyyy" and can be filtered by date. They even can be compared - this works:

SELECT customerno,
iif(orderdate>dateadd("d", -548, date()), 'Yes', 'No') as datesTest
from table1

But if I add "sum", nothing works.
Eg.
sum(iif(orderdate>dateadd("d", -548, date()), 1, 0)) as datesTest
or
sum(iif(not isnull(orderdate), 1, 0)) as sales18months

 

[justine1364]

I generated "totalsales" and sales18months columns in table1 (it's not summed by anything so it works).

I'm trying to sum them up by customer in another query.

SELECT linestable.customerno, sum(table.totalsales), Sum(table1.sales18months)
FROM linestable
GROUP BY linestable.customerno;

When I do just sum totalsales, it works. When I do sum sales18months, I get the same message error...

It is a numeric field (it has the "numeric filters") and has 0s instead of blanks. What gives?

 

[justine1364]

By the way, it's always the same table...I realize I threw in 3 different names in the query example I gave previously but that's just because I unnecessarily started using a different table name in the forum than the one I actually use.

 

[SkipVought]

By the way, it's always the same table

Don't you use COPY 'n' PASTE to post your SQL code?

You SHOULD!

Your inconsistencies diminish your credibility.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

When I click on "orderdate" in table1 (which is itself already a result of a query)

Is orderdate left or right aligned in the other query? If it is the result of Format() then I would remove the format().

Duane
Hook'D on Access
MS Access MVP

 

[justine1364]

It's right aligned. Orderdate is generated from transaction number which is a string.

DateValue(Format(Left(complete.DocumentNo,8),"0000\/00\/00"))) AS orderdate

When I removed format, it doesn't work.

 

[dhookom]

If [blue]complete.DocumentNo[/blue] is null, it will generate the error "run-time error '13': Type mismatch"

Duane
Hook'D on Access
MS Access MVP

 

[justine1364]

I found the source of the error, it was indeed complete.documentno. Documentno is a string containing the transaction number. I was generating "orderdate" from it. 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.