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

[neelsfer]

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:

>=DateAdd("m",-6,Date())

The Date() part is messing me around
Please advise

[MikeTheBike]

Hi
You could try something like this
Create a field like this

DateSerial(Right([month field],4),Left([month field],2),1) as MonthDate

And add a where clause as you indicated ie.

[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

Left([month field],Instr([month field],”/”)-1)

Also you may need -7 month to return the requied data?

HTH

MTB

[jimatqsi]

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:

(Year(Date()))*100)+Month(Date() - ((Right(yourdatefield,4)+Cint(yourdatefield/10000))

Jim

[neelsfer]

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.

[NeoPa]

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.

[neelsfer]

Hi Neopa
perhaps these pics will clarify the issue on how to filter the last 6 months records only

[NeoPa]

I'm afraid they don't Neels. Is there any reason you cannot express your question in words that make sense together?

[mcupito]

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.

[NeoPa]

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.