|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 4th, 2010, 09:51 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Keeping 12 months of records
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") 
|
|
#2
February 4th, 2010, 10:08 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Keeping 12 months of records
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. "hoachen" wrote: 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")
|
|
#3
February 4th, 2010, 10:29 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Keeping 12 months of records
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)) "KARL DEWEY" wrote: 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. "hoachen" wrote: 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")
|
|
#4
February 4th, 2010, 10:56 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Keeping 12 months of records
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. "hoachen" wrote: 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)) "KARL DEWEY" wrote: 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. "hoachen" wrote: 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")
|
|
#5
February 5th, 2010, 04:24 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Keeping 12 months of records
Hmm... nothing is return..empty
"KARL DEWEY" wrote: 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. "hoachen" wrote: 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)) "KARL DEWEY" wrote: 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. "hoachen" wrote: 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")
|
|
#6
February 5th, 2010, 04:26 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Keeping 12 months of records
the original date type is date/time (1/28/2010)
"hoachen" wrote: Hmm... nothing is return..empty "KARL DEWEY" wrote: 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. "hoachen" wrote: 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)) "KARL DEWEY" wrote: 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. "hoachen" wrote: 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")
|
|
#7
February 8th, 2010, 02:46 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Keeping 12 months of records
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!! "hoachen" wrote: the original date type is date/time (1/28/2010) "hoachen" wrote: Hmm... nothing is return..empty "KARL DEWEY" wrote: 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. "hoachen" wrote: 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)) "KARL DEWEY" wrote: 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. "hoachen" wrote: 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")
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
