Show best customers in a year

  • Thread starter Thread starter Gina
  • Start date Start date
G

Gina

Hi.

I would like to create a query that shows those customers who have brought
the most money.
I have a date in my invoice table, but do not know how to use it properly to
show up what is required

PerYear: DatDiff("y";[Invoice]![InvoiceDate];Now())

The above gives me an error saying there wouldn't be an InvoiceDate in the
aggregate function
Any help most appreaciated, thanks

Gina
 
I put

DatPart("yyyy";[InvoiceDate]) as function - condition

but now it works only if I type lets say 2005 as the criteria ....

better would be sth. generic like 'this year' and another one with 'last
year'

how ??
Gina
 
How about a little more detail. Can you post the SQL query statement?

Switch to SQL View, Copy and paste into your posting.

First question, do you have a field named InvoiceDate in the table(s) you are
using in your query? The fact that you are getting prompted for a value is
often indicative that the field does not exist in the query - no such field, a
misspelled field name, etc.
I put

DatPart("yyyy";[InvoiceDate]) as function - condition

but now it works only if I type lets say 2005 as the criteria ....

better would be sth. generic like 'this year' and another one with 'last
year'

how ??
Gina

Gina said:
Hi.

I would like to create a query that shows those customers who have brought
the most money.
I have a date in my invoice table, but do not know how to use it properly to
show up what is required

PerYear: DatDiff("y";[Invoice]![InvoiceDate];Now())

The above gives me an error saying there wouldn't be an InvoiceDate in the
aggregate function
Any help most appreaciated, thanks

Gina
Click to expand...
Click to expand...
 
Back
Top