CDate Not Cooperating - Need mm/yyyy format only

[chopin]

My goal is to take a date such as 1/1/2010 and convert it to 1/2010 or 1/10, either or. The way I am doing this is by using the format function as follows:

Format([table].[date],"mm/yyyy")

The problem is this spits out a string and I need this as a date for ordering. The resolution should be to use the cdate function which converts the string to a date such as this:

CDate(Format([table].[date],"mm/yyyy"))

The problem again is that converting this string spits out the format as a date 1/1/2010. As you can see, I am back to the drawing board.

Is there any way to force a format with cdate? Perhaps cdate is the wrong function in this case.

[missinglinq]

Sorry, but a month and year does not a 'date' make!

Exactly why do you 'need' a 'date' for ordering? A clearer explanation of your problem would probably enable someone here to offer a viable solution.

Linq ;0)>

[Jerry Maiapu]

Someone would definitely wonder why you want to split a date to remove the day portion of the date.
For whatever reason you got if I am on the right track then:

..The following will extract the year and the month portion at a time and combine them to format the result as "mm/yyyy" .

=Month([date])&"/"&Year([date])

The result/final field should be a text field not Date as
"mm/yyyy" is not recognised as Date.

Hope this helps..

JM

[missinglinq]

That's the same as the OP already has, using Format(). The problem is that he states he needs the resultant field to be a date, which, as we've both said, it can't!

[chopin]

If this is impossible, then so be it. I found a reasonable solution though:

CDate(Format([table].[date],"m/yyyy"))

Using the one "m" forces all the dates to start at day one for some reason (but this is a good thing). Thus my output will look like:

1/1/2010
2/1/2010
3/1/2010
etc...

This is a solution that will be good enough for me.

[missinglinq]

Sorry, chopin, but

CDate(Format([table].[date],"m/yyyy"))

yields the exact same results as your earlier code

CDate(Format([table].[date],"mm/yyyy"))

Your 'reasonable solution' is no different from what you started out with. There's nothing magical about using the single m here!

And according to your original post, only having month and year would do, not month, day and year!

[chopin]

I wasn't specifically clear with my original post, but I will explain. Basically I wanted to extract the month and year from each line item, so I can show total monthly results. And I wanted to serially increase the mm/yyyy portion so I can present these results correctly, but I needed a sort, which is why I wanted to convert to a date. With my setup with the double m, it would not start out at day one of the month if I showed the full date (the reason why I wanted to cut off the days in the first place). So with my setup, the date would spit out 1/8/2010, then 2/8/2010 etc, this would really not be correct for me. But then I discovered the single m with the combo "yyyy" automatically starts the day at "1" with cdate, and 1/1/2010, 2/1/2010 actually is the same as 1/2010, 2/2010 etc, which works for me.

[NeoPa]

There seems to be a number of items of confusion here :

1. Dates are dates. What is important is that they not be confused with the strings that portray them. Date fields can have default format characteristics, but however they are displayed, makes not one jot of difference to what is stored. Internally, Access stores dates as real numbers. The whole number part refers to the number of days since 30/12/1899. The fractional part indicates the time within the day as a fraction of the whole (.5 indicates noon for instance).
2. As far as date formats go, there is no practical difference between "m/yyyy" and "mm/yyyy". The only difference at all being that a month before October (10) would show with a leading zero (0) in the latter version but not in the former.
3. Access will always treat a month date string, that is one without the day specified, as being the first day of the month when converting it to a date. I suspect you discovered this at about the time you tried using the format string "m/yyyy", so associated the two together in your mind. This is not exclusive to this particular format. It works that way generally.

[NeoPa]

To look at your specific issue, that of grouping records together that share the same month, this can be done in two ways :

1. Convert the date to a string that only shows the month and year ("m/yyyy" for instance). The GROUP BY would consist of this string result directly.
2. The same as A above, but converting the result back into a date value. The GROUP BY would consist of this date result directly.

It's really as simple as that when you ignore all the misunderstandings and confusions.

The former is the simpler and would involve :

GROUP BY (Format([date],'m/yyyy'))