ADD MONTH -1

  • Thread starter ESHTA via AccessMonster.com
  • Start date
E

ESHTA via AccessMonster.com

Hello...
I have table with these values:
Date Product Imported Exported Total

I want to view the data of the current month (this is straightforwared) and
want to view the data of previous month of the current that I have searched
in the same table to be like this...

Product Imported Exported Total Total
(previous_month)

How it'll be passed??

Warm Regards...
 
M

Michel Walsh

Hi,


Bring the table reference twice in the query, one will get an _1 added to
its name. Add the inner join on the productID fields and, in the where
clause, add the criteria such that

tableName.Date = DateAdd( "m", 1, tableName_1.Date )


tableName_1 fields will now refer to those of the previous month (in
comparison to tableName, without _1, fields you can reference).


I *assumed* your "date" field always hold the first of a month/year, for
value.



Hoping it may help,
Vanderghast, Access MVP
 
K

KARL DEWEY

If you use true dates then add a field in the query like this --
Year: DatePart("yyyy",[tableName].[DateField]) & Right("0" &
DatePart("m",[tableName].[DateField]),2)

Use this for criteria for last month data --
DatePart("yyyy",Date()-Day(Date())) & Right("0" &
DatePart("m",Date()-Day(Date())),2)

For current month data use this for criteria --
DatePart("yyyy",Date()-Day(Date())+1) & Right("0" &
DatePart("m",Date()-Day(Date())+1),2)
 
Top