MS Access load datetime field as YYYYMM

[#1]

Hi Experts,

This is probably a silly question, but what is the syntax to load an MS Access DateTime as YYYYMM?  I tried this, but it's not working

Date(Date#(VisitDate, 'YYYYMMDD'), 'YYYYMM') as 'VisitYear Month',

Thanks in advance!

[sunny_talwar]

What format does VisitDate come in? If it does come in as YYYYMMDD, then what you are using looks right except for the single quote around the field name. I would either use double quotes or square brackets

Date(Date#(VisitDate, 'YYYYMMDD'), 'YYYYMM') as [VisitYear Month],

Update: I guess it doesn't matter, single quotes seems to work as well.

[#3]

Thanks for the reply!  OK, I changed the line to :

Date(Date#(VisitDate, 'MM/DD/YYYY'), 'YYYYMM') as ['Visit Year Month]',

The format in Access is MM/DD/YYYY.  I'm still not getting any results.  Is there something else that could be the problem?

[sunny_talwar]

I think single quotes seems to work, but have square bracket or single quotes (not both). Try this:

Date(Date#(VisitDate, 'MM/DD/YYYY'), 'YYYYMM') as [Visit Year Month],

[jpapador · Accepted solution]

If it is already coming in as a data type "Date" you won't need the Date#.

Try:

Date(MonthStart(VisitDate), 'YYYYMM') as [Visit Year Month],

[#6]

This worked!  I think the fact that the field is already DateTime must somehow play into the syntax choice.  (I'm a total newbie, so take that for what it's worth!)

Thank you both very very much!