Creating a Month Variable

  • Thread starter Thread starter FredF
  • Start date Start date
F

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
 
Maybe you could try something like this:

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

Not sure about the ranking thing.

HTH
 
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
.
Click to expand...
Click to expand...
 
Back
Top