Convert Date into Month and then data of only last two months (1 Viewer)

Ihsang

Member
Local time
Today, 05:24
Joined
Apr 7, 2020
Messages
49
Hi,
I am filtering data in query by Months. My date field name is "OrderDate".
I am able to filter the data in Month (with Month Name).
I used the following code
Code:
Month: Format([OrderDate],"mmmm\,yyyy")

in this code "mmmm" gives me full name of month, and "yyyyy" gives me full year
this code I am using in Query "Field"
as picture below.
1599245031146.png

and I get the result as below picture
Query.JPG


I have one problem and One question.

1) Months are not sorted by Latest month 1st and so on ------- rather they can only be sorted alphabetically
2) My main question is How can I get result of last two months only.
Thank you
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:24
Joined
Oct 29, 2018
Messages
13,261
Hi. I'm afraid you'll have to use the numeric values of the months to get the filtering to work correctly. You can display text and filter by numbers. Those are separate operations.
 

Ihsang

Member
Local time
Today, 05:24
Joined
Apr 7, 2020
Messages
49
Hi. I'm afraid you'll have to use the numeric values of the months to get the filtering to work correctly. You can display text and filter by numbers. Those are separate operations.
Thank you for your reply. Yes by add date function I was able to get it, months in numerical value, by using following code, (for example for 1 month)
Code:
Format(DateAdd("m", -1, Date()), "mmmyy")

But the reason for my requirement of "Month name" is I want present data in graph. Monthly by for each user bargraph.
Each month, what was value of each user. and I want only last two months by Name.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:24
Joined
Oct 29, 2018
Messages
13,261
Thank you for your reply. Yes by add date function I was able to get it, months in numerical value, by using following code, (for example for 1 month)
Code:
Format(DateAdd("m", -1, Date()), "mmmyy")

But the reason for my requirement of "Month name" is I want present data in graph. Monthly by for each user bargraph.
Each month, what was value of each user. and I want only last two months by Name.
Hi. If you're saying you already got it sorted out, then Congratulations! If you're trying to explain what you're trying to do, I get it. What I was saying was you can do both of them: display using Text and sort by using Numbers. Cheers!
 

namliam

The Mailman - AWF VIP
Local time
Today, 05:24
Joined
Aug 11, 2003
Messages
11,680
1
you need to simply make an other column to sort on, you format said column as YYYYMM and order by that
2
same as 1, but you keep the original unformatted date and filter by that ... put in the where clause: >= dateserial(year(date), month(date) -1,1)
this will make >= 01-aug-2020 for the months aug and sept... if you want Jul Aug and Sept : >= dateserial(year(date), month(date) -2,1)

If you dont want the running month, i.e. jul and aug: between dateserial(year(date), month(date) -2,1) and dateserial(year(date), month(date) -2,0)

That is assuming it is a proper date field without time component, if you have times as well it could be a little more complex depending on details.
 

Ihsang

Member
Local time
Today, 05:24
Joined
Apr 7, 2020
Messages
49
Hi. If you're saying you already got it sorted out, then Congratulations! If you're trying to explain what you're trying to do, I get it. What I was saying was you can do both of them: display using Text and sort by using Numbers. Cheers!
Thank you. Yes I tried to explain, what I could do and what could not and for what I am looking for help.
How can get text (Month name) by filtering with numbers Like >=Date()-30
I am not that expert, It will be nice If you can share code example. Thanks
 

Ihsang

Member
Local time
Today, 05:24
Joined
Apr 7, 2020
Messages
49
1
you need to simply make an other column to sort on, you format said column as YYYYMM and order by that
2
same as 1, but you keep the original unformatted date and filter by that ... put in the where clause: >= dateserial(year(date), month(date) -1,1)
this will make >= 01-aug-2020 for the months aug and sept... if you want Jul Aug and Sept : >= dateserial(year(date), month(date) -2,1)

If you dont want the running month, i.e. jul and aug: between dateserial(year(date), month(date) -2,1) and dateserial(year(date), month(date) -2,0)

That is assuming it is a proper date field without time component, if you have times as well it could be a little more complex depending on details.
Thank you very much. I will try this out and will give you feed back. kind regards
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:24
Joined
Oct 29, 2018
Messages
13,261
Thank you. Yes I tried to explain, what I could do and what could not and for what I am looking for help.
How can get text (Month name) by filtering with numbers Like >=Date()-30
I am not that expert, It will be nice If you can share code example. Thanks
Hi. Here's an example. Hope it helps...
SQL:
SELECT Format([DateField],"mmmm, yyyy") As FormattedMonths
FROM TableName
WHERE [DateField] >= DateAdd("m", -2, Date())
ORDER BY [DateField]
 

Users who are viewing this thread

Top Bottom