Sorting the data based on a criteria and storing in multiple columns.

[junaid]

I have a Field InvoiceDate, I want to know the Days Elapsed
so i have subtracted InvoiceDate from currentDate and i got my DaysElapsed.

Now I want to make 4 more field 30Days and get the store the data if DaysElapsed is less than 30 days, similarly
60Days, 90Days and Over90Days.

How to set the criteria in query to display values in
multiple fields.

Thank You.

 

[allanon]

I assume you are storing other data than the elapse days so lets just add 1 to 4 counter fields:

Dim iCount30, iCount60, iCount90, iCountO90 as integer
Dim ElapseDays as integer

ElapseDays = DateDiff("d", [InvoiceDate], currentDate)

IF ElapseDays < 30 then
iCount30 = iCount30 + 1
elseif ElapseDays < 60 then
iCOunt60 = iCOunt60 + 1
elseif ElapseDays < 90 then
iCount90 = iCount90 + 1
else
iCountO90 = iCountO90 + 1
end if

The icount fields could be your four buckets or you could accumulate your amounts and then move them to your fields. Whatever works for you.