Get a text date to be a number

[mj_stev]

Good morning,

I have an access database with dates in it that are listed as text and not numbers.

I'm using the following code to pull out the four digits for the year:

SELECT right(Date,4) AS [Year]
FROM TableName
GROUP BY right(Date,4)

The thing is that the year comes back as text. When I dump it into the pivot table, I can't use it as a number.

What can I do to correct this?
Thanks for your time,
Michael

 

[Norie]

Michael

Try this.

SELECT Year(DateValue(Date))
FROM TableName
GROUP BY Year(DateValue(Date))

By the way I would strongly recommend you don't use Date as the name of a field in the table.