DateSerial

[BrendaR]

Hi!
Our system is currently using dates in this format: 20140306 = 3/6/14

The expression I am using to convert this to a regular date in a query is:
DateSerial (Left([fieldname],4),mid([fieldname],5,2),Right([fieldname],2)) which changes it from 20140306 to 3/6/14 and works perfectly.

However, I now want to just change it to the year and month rather than add the day so it will fall into monthly buckets in a crosstab query. I've taken off the last part of the expression after "Right", etc., but this does not work. The result I want is 20140306 = 201403

Can someone tell me how to do this please? Thanks!
Brenda

 

[pbaldy]

Well, a "date" includes a day. You can just use Left(fieldname, 6) to get the result you want, but of course it will still be a string.

 

[BrendaR]

Ok, so it's not a date anymore but it works! Thanks so much!

 

[pbaldy]

Happy to help!