Date format mm/yyyy

[JPR]

Hello,
Is there a way a can show a date field in a query as mm/yyyy format?
The query has its recods source to a table with a regular date field (mm/dd/yyyy). I will be always entering data in the correct date format but would like to add ta column to a query with this other format- Thank you

 

[DHookom]

Have you tried creating an additional column with the field in the Format() function?
DateMMYYYY: Format([DateField], "mm/yyyy")

 

[Gasman]

Have you tried creating an additional column with the field in the Format() function?
DateMMYYYY: Format([DateField], "mm/yyyy")

I would expect not.

 

[GPGeorge]

Hello,
Is there a way a can show a date field in a query as mm/yyyy format?
The query has its recods source to a table with a regular date field (mm/dd/yyyy). I will be always entering data in the correct date format but would like to add ta column to a query with this other format- Thank you

This display format would be for display only, though, correct?

 

[JPR]

Thank you.
Yes, the format is only to display data.
I have copied the code in a new column of the query but I am getting a sintax or missing operator error.

 

[GPGeorge]

Thank you.
Yes, the format is only to display data.
I have copied the code in a new column of the query but I am getting a sintax or missing operator error.

Please show us the SQL from the query. Not a screenshot of the query designer. The copy and paste the SQL, please.

 

[JPR]

The query has its record source to table1 and has the following fields:

- DateCost
- Amount

Date cost has the format dd/mm/yyyy

This is the code in the new column:

DateMMYYYY: Format([DateCost], "mm/yyyy")

Thank you

 

[Gasman]

Thank you.
Yes, the format is only to display data.
I have copied the code in a new column of the query but I am getting a sintax or missing operator error.

The Datefield needs to be the actual name of your date field?

 

[Gasman]

The query has its record source to table1 and has the following fields:

- DateCost
- Amount

Date cost has the format dd/mm/yyyy

This is the code in the new column:

DateMMYYYY: Format([DateCost], "mm/yyyy")

Thank you

That *should* work?

This works for me. I even added an extra space after the comma, but Access removed it.

SELECT tblDaily.DailyID, Format([dailydate],"mm/yyyy") AS Expr1
FROM tblDaily;

 

[GPGeorge]

The query has its record source to table1 and has the following fields:

- DateCost
- Amount

Date cost has the format dd/mm/yyyy

This is the code in the new column:

DateMMYYYY: Format([DateCost], "mm/yyyy")

Thank you

Given the snippet shown, it should work, but that is why we need to see the actual SQL, not parts of it.

The error about syntax or missing operator suggests the problem is elsewhere in that SQL, so that's what we need to evaluate.

Also, please confirm that DateCost is a datetime datatype field (I understand that the way it's formatted in the table implies it is, but just to be sure).

 

[JPR]

Finally got it to work. Not sure if it's because I use an Italian language of Access. This is how I changed it.

Expr1: Format([dailydate];"mm/aaaa")

Thank you

 

[Gasman]

Finally got it to work. Not sure if it's because I use an Italian language of Access. This is how I changed it.

Expr1: Format([dailydate];"mm/aaaa")

Thank you

Well yes
It will be whatever your language settings are.

I presume 'a' is for ano's? Other languages use ; instead of ' as separators. I am suprised you have not come upon that in the past as you have been given Format() in other threads you have posted?

Lucky for you, months are the same as in English.

 

[GPGeorge]

Finally got it to work. Not sure if it's because I use an Italian language of Access. This is how I changed it.

Expr1: Format([dailydate];"mm/aaaa")

Thank you

Yes, language settings matter.

 

[JPR]

Actually year is Anno.
Ano ........totally different meaning.....

 

[Gasman]

Actually year is Anno.
Ano ........totally different meaning.....

I just asked Alexa and that is what it sounded like to me. If it offended in any way (and a Google translate shows it could ), then I apologise.

 

[JPR]

Absolutely!!! No worries. It just made me have a good laugh!!