Keeping 12 months of records

[hoachen]

The data i have is: Jan - dec 09 and Jan 10 and Feb 10 and will add each
month after that. I don't want to keep all of the months. I just want to keep
like now is feb i want the data is from feb 09 to feb 10 and then next month
i will be march 09 to march 10.

the query i have is take all the months, is there a way to change it to make
what i need?

Expr1: Format([PurchaseDate],"mmm, yy")

 

[KARL DEWEY]

Use criteria on PurchaseDate.
Add a field in design view like this --
MySelection: Format([PurchaseDate],"yyyymm")

Then for criteria use this --
Between DateAdd("m", 1, SerialDate(Year(Date()), Month(Date()), 0)) AND
DateAdd("yyyy", -12, SerialDate(Year(Date()), Month(Date()), 1))

This will pull 13 months data.

 

[hoachen]

Thank you very much for your quick reply!

Why it say undefine "SerialDate"?
Actually i am using crosstab query

(Field)MySelection: Format([PurchaseDate],"yyymm")
(Total) Group by
(Crosstab) Column Heading
(Criteria) Between DateAdd("m", 1, SerialDate(Year(Date()), Month(Date()),
0)) AND DateAdd("yyyy", -12, SerialDate(Year(Date()), Month(Date()), 1))

Use criteria on PurchaseDate.
Add a field in design view like this --
MySelection: Format([PurchaseDate],"yyyymm")

Then for criteria use this --
Between DateAdd("m", 1, SerialDate(Year(Date()), Month(Date()), 0)) AND
DateAdd("yyyy", -12, SerialDate(Year(Date()), Month(Date()), 1))

This will pull 13 months data.

--
Build a little, test a little.

The data i have is: Jan - dec 09 and Jan 10 and Feb 10 and will add each
month after that. I don't want to keep all of the months. I just want to keep
like now is feb i want the data is from feb 09 to feb 10 and then next month
i will be march 09 to march 10.

the query i have is take all the months, is there a way to change it to make
what i need?

Expr1: Format([PurchaseDate],"mmm, yy")

 

[KARL DEWEY]

My error, should be DateSerial.

Try this --
(Field)MySelection: Format([PurchaseDate],"yyyYmm")
(Total) WHERE
(Crosstab)
(Criteria) Between DateAdd("m", 1, DATESerial(Year(Date()), Month(Date()),
0)) AND DateAdd("yyyy", -12, DATESerial(Year(Date()), Month(Date()), 1))

--
Build a little, test a little.

Thank you very much for your quick reply!

Why it say undefine "SerialDate"?
Actually i am using crosstab query

(Field)MySelection: Format([PurchaseDate],"yyymm")
(Total) Group by
(Crosstab) Column Heading
(Criteria) Between DateAdd("m", 1, SerialDate(Year(Date()), Month(Date()),
0)) AND DateAdd("yyyy", -12, SerialDate(Year(Date()), Month(Date()), 1))

Use criteria on PurchaseDate.
Add a field in design view like this --
MySelection: Format([PurchaseDate],"yyyymm")

Then for criteria use this --
Between DateAdd("m", 1, SerialDate(Year(Date()), Month(Date()), 0)) AND
DateAdd("yyyy", -12, SerialDate(Year(Date()), Month(Date()), 1))

This will pull 13 months data.

--
Build a little, test a little.

The data i have is: Jan - dec 09 and Jan 10 and Feb 10 and will add each
month after that. I don't want to keep all of the months. I just want to keep
like now is feb i want the data is from feb 09 to feb 10 and then next month
i will be march 09 to march 10.

the query i have is take all the months, is there a way to change it to make
what i need?

Expr1: Format([PurchaseDate],"mmm, yy")

 

[hoachen]

Hmm... nothing is return..empty

My error, should be DateSerial.

Try this --
(Field)MySelection: Format([PurchaseDate],"yyyYmm")
(Total) WHERE
(Crosstab)

Between DateAdd("m", 1, DATESerial(Year(Date()), Month(Date()), 0)) AND
DateAdd("yyyy", -12, DATESerial(Year(Date()), Month(Date()), 1))

--
Build a little, test a little.

Thank you very much for your quick reply!

Why it say undefine "SerialDate"?
Actually i am using crosstab query

(Field)MySelection: Format([PurchaseDate],"yyymm")
(Total) Group by
(Crosstab) Column Heading
(Criteria) Between DateAdd("m", 1, SerialDate(Year(Date()), Month(Date()),
0)) AND DateAdd("yyyy", -12, SerialDate(Year(Date()), Month(Date()), 1))

Use criteria on PurchaseDate.
Add a field in design view like this --
MySelection: Format([PurchaseDate],"yyyymm")

Then for criteria use this --
Between DateAdd("m", 1, SerialDate(Year(Date()), Month(Date()), 0)) AND
DateAdd("yyyy", -12, SerialDate(Year(Date()), Month(Date()), 1))

This will pull 13 months data.

--
Build a little, test a little.


:

The data i have is: Jan - dec 09 and Jan 10 and Feb 10 and will add each
month after that. I don't want to keep all of the months. I just want to keep
like now is feb i want the data is from feb 09 to feb 10 and then next month
i will be march 09 to march 10.

the query i have is take all the months, is there a way to change it to make
what i need?

Expr1: Format([PurchaseDate],"mmm, yy")

 

[hoachen]

the original date type is date/time (1/28/2010)

Hmm... nothing is return..empty

My error, should be DateSerial.

Try this --
(Field)MySelection: Format([PurchaseDate],"yyyYmm")
(Total) WHERE
(Crosstab)

Between DateAdd("m", 1, DATESerial(Year(Date()), Month(Date()), 0)) AND
DateAdd("yyyy", -12, DATESerial(Year(Date()), Month(Date()), 1))

--
Build a little, test a little.

Thank you very much for your quick reply!

Why it say undefine "SerialDate"?
Actually i am using crosstab query

(Field)MySelection: Format([PurchaseDate],"yyymm")
(Total) Group by
(Crosstab) Column Heading
(Criteria) Between DateAdd("m", 1, SerialDate(Year(Date()), Month(Date()),
0)) AND DateAdd("yyyy", -12, SerialDate(Year(Date()), Month(Date()), 1))

:

Use criteria on PurchaseDate.
Add a field in design view like this --
MySelection: Format([PurchaseDate],"yyyymm")

Then for criteria use this --
Between DateAdd("m", 1, SerialDate(Year(Date()), Month(Date()), 0)) AND
DateAdd("yyyy", -12, SerialDate(Year(Date()), Month(Date()), 1))

This will pull 13 months data.

--
Build a little, test a little.


:

The data i have is: Jan - dec 09 and Jan 10 and Feb 10 and will add each
month after that. I don't want to keep all of the months. I just want to keep
like now is feb i want the data is from feb 09 to feb 10 and then next month
i will be march 09 to march 10.

the query i have is take all the months, is there a way to change it to make
what i need?

Expr1: Format([PurchaseDate],"mmm, yy")

 

[hoachen]

Nothing return

after the change the (Field)MySelection: Format([PurchaseDate],"yyyymm") and
now the data look like 200902, 201001, but nothing is return. Plelase help!!

the original date type is date/time (1/28/2010)

Hmm... nothing is return..empty

My error, should be DateSerial.

Try this --
(Field)MySelection: Format([PurchaseDate],"yyyYmm")
(Total) WHERE
(Crosstab)

Between DateAdd("m", 1, DATESerial(Year(Date()), Month(Date()), 0)) AND
DateAdd("yyyy", -12, DATESerial(Year(Date()), Month(Date()), 1))

--
Build a little, test a little.


:

Thank you very much for your quick reply!

Why it say undefine "SerialDate"?
Actually i am using crosstab query

(Field)MySelection: Format([PurchaseDate],"yyymm")
(Total) Group by
(Crosstab) Column Heading
(Criteria) Between DateAdd("m", 1, SerialDate(Year(Date()), Month(Date()),
0)) AND DateAdd("yyyy", -12, SerialDate(Year(Date()), Month(Date()), 1))

:

Use criteria on PurchaseDate.
Add a field in design view like this --
MySelection: Format([PurchaseDate],"yyyymm")

Then for criteria use this --
Between DateAdd("m", 1, SerialDate(Year(Date()), Month(Date()), 0)) AND
DateAdd("yyyy", -12, SerialDate(Year(Date()), Month(Date()), 1))

This will pull 13 months data.

--
Build a little, test a little.


:

The data i have is: Jan - dec 09 and Jan 10 and Feb 10 and will add each
month after that. I don't want to keep all of the months. I just want to keep
like now is feb i want the data is from feb 09 to feb 10 and then next month
i will be march 09 to march 10.

the query i have is take all the months, is there a way to change it to make
what i need?

Expr1: Format([PurchaseDate],"mmm, yy")