date problem

[pholligan]

Hi

I am trying to sort out some records into the last year to date. I have been trying Between (DateAdd("d",-365,Date())) And (Date()) but this wont work. Any ideas?

My next step is to consolidate the information from the last year which will include a number of entries with the same name, a number with each entry which needs to be totalled for all occurences of the same name, and the total number of occurences eg.

bill 2
bill 7
bill 3
fred 6
fred 7

needs to be displayed as:

name total occurences
bill 12 3
fred 13 2


Seeing as I'm struggling to display data from the last year I'm guessing I wont stand much of a chance with the consollidation either so any help/pointers would be very much appreciated.

Thanks Again
Paul

 

[pholligan]

I think I may have some sort of date format problem as I have now entered some data for today's date and it brings that up. It also brings up data from the same day of any month eg.

if my criteria is Date().....it will show records with 21/01/2003, but also 21/09/2002!

What date format is returned from the function Date()?

 

[Vassago]

Try this for you between statement

Between (Date()-30) And Date()

and then create a second query based from the first query with these fields

field 1 = name
field 2 = Sum(occurances)
field 3 = count(occurances)

This should give you the data you needed.

Hope that helps

Vassago