Grab Month and Year from a date

[Guest]

I'm trying to design a field that grabs the Month and Year from a date field
within my query. I can get it to partially work where the Month is displayed
as a Number and the Year is displayed in 4 digits.

I've been using the Month and Year functions together in the same field so
the results look like this: "3-2006". The function I used is as follows:

Month([Date]) & "-" & Year([Date])

But what I really want is the month and year displayed like this: "Mar-06".
I've tried to use the Format function within the Month function, such as:

Format(Month([Date]),"mmm"), but for some very strange reason, the Month
that returns is not the same month as what is in [Date], but when I remove
the Format function, the Month number returned is correct.

I'm trying to avoid having a date table that shows every single date and
create a relationship within the query to grab what I want, but if that is
the best way to go, then so be it.

Any help would be appreciated.
Thanks,
Frank

 

[Duane Hookom]

You might simplify this with
Format([Date],"mmm-yy")

--
Duane Hookom
MS Access MVP
--

Never mind. I figured it out. I just removed the Month function and it
worked exactly how I wanted it. For those interesed, here's how the
function
looks:

Format([Date],"mmm") & "-" & Format([Date],"yy")

The result of [Date]=3/16/2006: "Mar-06"

Hope this helps anyone looking to perform the same action.

I'm trying to design a field that grabs the Month and Year from a date
field
within my query. I can get it to partially work where the Month is
displayed
as a Number and the Year is displayed in 4 digits.

I've been using the Month and Year functions together in the same field
so
the results look like this: "3-2006". The function I used is as
follows:

Month([Date]) & "-" & Year([Date])

But what I really want is the month and year displayed like this:
"Mar-06".
I've tried to use the Format function within the Month function, such as:

Format(Month([Date]),"mmm"), but for some very strange reason, the Month
that returns is not the same month as what is in [Date], but when I
remove
the Format function, the Month number returned is correct.

I'm trying to avoid having a date table that shows every single date and
create a relationship within the query to grab what I want, but if that
is
the best way to go, then so be it.

Any help would be appreciated.
Thanks,
Frank

 

[Guest]

Thanks, that works too. I sometimes have a difficult time thinking simple.

You might simplify this with
Format([Date],"mmm-yy")

--
Duane Hookom
MS Access MVP
--

Never mind. I figured it out. I just removed the Month function and it
worked exactly how I wanted it. For those interesed, here's how the
function
looks:

Format([Date],"mmm") & "-" & Format([Date],"yy")

The result of [Date]=3/16/2006: "Mar-06"

Hope this helps anyone looking to perform the same action.

I'm trying to design a field that grabs the Month and Year from a date
field
within my query. I can get it to partially work where the Month is
displayed
as a Number and the Year is displayed in 4 digits.

I've been using the Month and Year functions together in the same field
so
the results look like this: "3-2006". The function I used is as
follows:

Month([Date]) & "-" & Year([Date])

But what I really want is the month and year displayed like this:
"Mar-06".
I've tried to use the Format function within the Month function, such as:

Format(Month([Date]),"mmm"), but for some very strange reason, the Month
that returns is not the same month as what is in [Date], but when I
remove
the Format function, the Month number returned is correct.

I'm trying to avoid having a date table that shows every single date and
create a relationship within the query to grab what I want, but if that
is
the best way to go, then so be it.

Any help would be appreciated.
Thanks,
Frank