Recent content by 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...

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.

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.

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...

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...

I'm getting the same error with both your suggestions :)

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.

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...