By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Filter last 6 months records on a month field ie 12/2013 format

100+
P: 547
How do one filter on a "month field" ie 12/2013 format when mmddyyyy is not available in a query.
I need to view only the last 6 month records.
Usually i would use this based on dates:
Expand|Select|Wrap|Line Numbers
  1. >=DateAdd("m",-6,Date())
The Date() part is messing me around
Please advise
Feb 4 '14 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 634
Hi
You could try something like this
Create a field like this
Expand|Select|Wrap|Line Numbers
  1. DateSerial(Right([month field],4),Left([month field],2),1) as MonthDate 
And add a where clause as you indicated ie.
Expand|Select|Wrap|Line Numbers
  1. [MonthDate] >= DateAdd(“m”.-6,Date())
If the [month field] hold lower month like this 2/2014 and not 02/2014 then the Month past of DateSerial should something like this
Expand|Select|Wrap|Line Numbers
  1. Left([month field],Instr([month field],”/”)-1)
Also you may need -7 month to return the requied data?

HTH

MTB
Feb 4 '14 #2

Expert 100+
P: 1,221
Your question is not very clear. What is available in the query? Are you saying the query does not have a date field but only a MMYYYY field, (probably in an integer)? If that is the case you could rearrange it to be YYYYMM and compare it to (Year(Date()))*100)+Month(Date().
It would be something like this to get the number of months difference:
Expand|Select|Wrap|Line Numbers
  1. (Year(Date()))*100)+Month(Date() - ((Right(yourdatefield,4)+Cint(yourdatefield/10000))
Jim
Feb 4 '14 #3

100+
P: 547
Jim, i filtered the query to not display dd/mm/yyyy but ONLY MM/YYYY . I need to display the last 6 months data in this query now. I cannot filter on dd/mm/yyyy as i merged two different dates into one mm/yyyy field, based on criteria that must now be filtered.
Feb 4 '14 #4

NeoPa
Expert Mod 15k+
P: 31,271
Neelsfer:
"How do one filter on a "month field" ie 12/2013 format when mmddyyyy is not available in a query."

I can't work out what you're trying to say. It doesn't make sense as it stands and I have no idea what you mean.
Feb 5 '14 #5

100+
P: 547
Hi Neopa
perhaps these pics will clarify the issue on how to filter the last 6 months records only
Attached Images
File Type: jpg monthly filter.jpg (51.4 KB, 345 views)
File Type: jpg filter month field.jpg (69.4 KB, 373 views)
File Type: jpg monthly filtering.jpg (22.9 KB, 286 views)
Feb 5 '14 #6

NeoPa
Expert Mod 15k+
P: 31,271
I'm afraid they don't Neels. Is there any reason you cannot express your question in words that make sense together?
Feb 6 '14 #7

100+
P: 294
In your initial post, you might need to make sure the formats that are comparing the 2 dates are the same.

This might be a helpful link --> http://office.microsoft.com/en-us/ac...010078108.aspx

Edit: NeoPa is correct. Date() does return a Date/Time value. You might want to try a Format() on the Date() to match your criterion. You are trying to compare that to a custom mask MM/YYYY. Hope this helps.

NeoPa : I was not trying to mislead the OP, however convey to him that the format is different. You are indeed correct. I should have told him what you mentioned. Thanks for the correction.
Feb 6 '14 #8

NeoPa
Expert Mod 15k+
P: 31,271
MCupito:
"Date() returns a short date."

I'm sorry - that's just wrong. Date() returns a Date/Time value - not any type of string. How it's formatted when displayed is not what it is, but simply how it's formatted.

It may well be that trying to do a comparison using SQL and getting the formats wrong (so they don't match) is what's getting Neels confused. I'll have to wait until his question makes sense before I can help further.
Feb 7 '14 #9

Post your reply

Sign in to post your reply or Sign up for a free account.