Creating a Month Variable

[FredF]

Hello all,

I have a table of sales transactions. There is a field
called date which is the transaction date. I want to
create another field based on this called month.
Everything in transaction date field would be assigned to
month. The value in the month field could actually be
1/1/03, 2/1/03 etc. I am doing this because I want to
create a bunch of Access reports that sum by month and I
can't do this with just the transaction date field.

Also, is there a way to put a rank value in either a query
or a report? I am summing the transactions by customer
and want to add a field that lists who is number one,
number two. It is sorted in descending order but a rank
value would be nice.

Thanks,

Chuck

 

[Guido]

Maybe you could try something like this:

Month: CDate(Month([TransDT]) & "/1/" & Year([TransDT]))

Not sure about the ranking thing.

HTH

 

[John Spencer (MVP)]

FirstOfMonth: DateSerial(Year(TransDate),Month(TransDate),1)

or to get a unique value for the month, you could use the format function.

Format(TransDate,"YYYYMM")

Maybe you could try something like this:

Month: CDate(Month([TransDT]) & "/1/" & Year([TransDT]))

Not sure about the ranking thing.

HTH

-----Original Message-----
Hello all,

I have a table of sales transactions. There is a field
called date which is the transaction date. I want to
create another field based on this called month.
Everything in transaction date field would be assigned to
month. The value in the month field could actually be
1/1/03, 2/1/03 etc. I am doing this because I want to
create a bunch of Access reports that sum by month and I
can't do this with just the transaction date field.

Also, is there a way to put a rank value in either a query
or a report? I am summing the transactions by customer
and want to add a field that lists who is number one,
number two. It is sorted in descending order but a rank
value would be nice.

Thanks,

Chuck
.