Export query to Excel - Format Date as Year and Number

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Thomas T - 18 Aug 2005 11:50 GMT

Hi, I have a date field in a table calles tblFire. I want to export the year
part of this date (eg. 2005) to excel and have this formated as a number.
Currently I'm using this query:

SELECT Format(Date, "yyyy") AS Year FROM tblFire

the Year field is stored as text in excel and I have to manualy change the
formating in excel to number. I've also tried :

SELECT Val(Format(Date, "yyyy")) AS Year FROM tblFire

which results in all rows in the Year column filled with the letters "yyyy".

Any Ideas anyone?

thanks,

t

Reply to this Message

Ken Snell [MVP] - 18 Aug 2005 14:09 GMT

Try this:

SELECT CInt(Format(Date, "yyyy")) AS Year FROM tblFire

Note that it's not a good idea to use Year as the name of a field. See these
Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Signature

Ken Snell
<MS ACCESS MVP>

> Hi, I have a date field in a table calles tblFire. I want to export the
> year
[quoted text clipped - 16 lines]
>
> t

Reply to this Message

Thomas T - 18 Aug 2005 14:43 GMT

Thanks for your reply, unfortunately it does not help. The rows only display
"yyyy" in the column Year. As for reserved words the query i posted is only
to demonstrade as the original query har norwegian fieldnames and aliases,
and is pretty huge. The field is actually named "Aar" so it should not
represent a problem.

>Try this:
>
[quoted text clipped - 17 lines]
>>
>> t

Reply to this Message

Ken Snell [MVP] - 18 Aug 2005 14:55 GMT

I'm not understanding your answer: "The rows only display "yyyy" in the
column Year." Do you literally mean the letters yyyy are being displayed in
the column? You say you are not using Year as the output field name. Let's
see the actual SQL statement that you're using.

Did you try
SELECT Year(Date) AS TheYear FROM tblFire

Signature

Ken Snell
<MS ACCESS MVP>

> Thanks for your reply, unfortunately it does not help. The rows only
> display
[quoted text clipped - 26 lines]
>>>
>>> t

Reply to this Message

Thomas T - 19 Aug 2005 07:31 GMT

Yes i literally mean the letters yyyy are being displayed, but curiously
enough, when i place the cursor in one of the rows in the year column the
year is correctly displayed (instead of the letters yyyy) in that particular
row/column. This holds true for both excel and access. My actual SQL
statement is as follows (keep in mind there are norwegian special characters
in there):

SELECT Brann.Id AS [Reg nr], Brann.[Registrert dato] AS [Reg dato], Brann.
[NTE anleggsnummer], Brann.[NTE id], Brann.Dato AS Branndato, Val(Format
(Brann.Dato,"yyyy")) AS År, Brann.Saksnummer AS [SAFE nr], Kommune.Navn AS
Kommune, Kommune.Nummer AS [Kom nr], Brann.Adresse, Brann.Postnummer, Brann.
Sted, Format(Brann.Klokkeslett,"Short Time") AS Klokkeslett, Tilsynsdistrikt.
Nummer AS [Distr nr], Tilsynsdistrikt.Navn AS Distrikt, Brann.Utetemperatur,
Brann.Værforhold, Årsak.ÅrsaksgruppeKode & "." & Årsak.Kode AS Årsakskode,
Årsaksgruppe.Beskrivelse & ", " & Årsak.Beskrivelse AS Årsak, Utstyr.Kode AS
[El utstyrskode], Utstyr.Beskrivelse AS [El utstyrsbeskrivelse], Fabrikat.
Beskrivelse AS Fabrikat, Typebetegnelse.Beskrivelse AS Typebetegnelse,
Feilobjekt.Årsmodell, Feilobjekt.Anmerkninger, Skadeomfang.Beskrivelse AS
Skadeomfang, Brannobjekt.Objekt AS Brannsted, Brann.Brannobjektbeskrivelse AS
Brannstedbeskrivelse, Etterforsker.Etternavn & ", " & Etterforsker.Fornavn AS
Etterforsker
FROM Årsaksgruppe RIGHT JOIN (Årsak RIGHT JOIN (Utstyrsgruppe RIGHT JOIN
(Utstyr RIGHT JOIN ((Fabrikat RIGHT JOIN Typebetegnelse ON Fabrikat.Id =
Typebetegnelse.FabrikatId) RIGHT JOIN (Tilsynsdistrikt RIGHT JOIN
(Skadeomfang RIGHT JOIN (Kommune RIGHT JOIN (Etterforsker RIGHT JOIN
(Brannobjekt RIGHT JOIN (Brann LEFT JOIN Feilobjekt ON Brann.Id = Feilobjekt.
BrannId) ON Brannobjekt.Id = Brann.BrannobjektId) ON Etterforsker.Id = Brann.
EtterforskerId) ON Kommune.Id = Brann.KommuneId) ON Skadeomfang.Id = Brann.
SkadeomfangId) ON Tilsynsdistrikt.Id = Brann.TilsynsdistriktId) ON
Typebetegnelse.Id = Feilobjekt.TypeId) ON Utstyr.Id = Feilobjekt.UtstyrsId)
ON Utstyrsgruppe.Id = Utstyr.UtstyrsgruppeId) ON Årsak.Id = Brann.ÅrsaksId)
ON Årsaksgruppe.Kode = Årsak.ÅrsaksgruppeKode
WHERE ( Brann.Id Like 'kjell' or Brann.[NTE id] Like 'kjell' or Brann.[NTE
anleggsnummer] Like 'kjell' or Brann.Saksnummer Like 'kjell' or Brann.Dato
Like 'kjell' or [Etterforsker].[Etternavn] Like 'kjell' or [Etterforsker].
[Fornavn] Like 'kjell' or [Brannobjekt].[Objekt] Like 'kjell' or [Kommune].
[Navn] Like 'kjell' or [Tilsynsdistrikt].[Navn] Like 'kjell' or Årsak.
Beskrivelse Like '*kjell*' or Årsaksgruppe.Beskrivelse Like '*kjell*' or
Utstyrsgruppe.Beskrivelse Like '*kjell*' or Utstyr.Beskrivelse Like '*kjell*'
or Fabrikat.Beskrivelse Like '*kjell*' or Typebetegnelse.Beskrivelse Like
'*kjell*' )
ORDER BY Brann.Id, Brann.[NTE id], Brann.[NTE anleggsnummer], Brann.
Saksnummer, Brann.Dato, [Etterforsker].[Etternavn], [Etterforsker].[Fornavn],
[Brannobjekt].[Objekt], [Kommune].[Navn], [Tilsynsdistrikt].[Navn], Årsak.
Beskrivelse, Årsaksgruppe.Beskrivelse, Utstyrsgruppe.Beskrivelse, Utstyr.
Beskrivelse, Fabrikat.Beskrivelse, Typebetegnelse.Beskrivelse;

>I'm not understanding your answer: "The rows only display "yyyy" in the
>column Year." Do you literally mean the letters yyyy are being displayed in
[quoted text clipped - 9 lines]
>>>>
>>>> t

Reply to this Message

Klatuu - 18 Aug 2005 14:52 GMT

SELECT Year(Date) AS Year FROM tblFire

> Hi, I have a date field in a table calles tblFire. I want to export the year
> part of this date (eg. 2005) to excel and have this formated as a number.
[quoted text clipped - 14 lines]
>
> t

Reply to this Message

Thomas T - 19 Aug 2005 07:40 GMT

I've solved the problem, it turns out the norwegian word for Year ("År") also
is a reserved word in the norwegian version of MS Access. This results in
unexpected behaviour:

these statements don't work (they result in the letters yyyy):

SELECT Year(Date) AS Year FROM tblFire
SELECT Year(Date) AS År FROM tblFire

these give correct results:

SELECT Year(Date) AS TheYear FROM tblFire
SELECT Val(Format(Date, "yyyy")) AS TheYear FROM tblFire
SELECT CInt(Format(Date, "yyyy")) AS TheYear FROM tblFire

the alias may be anything except a reserved word.

Thanks alot for your help!

>SELECT Year(Date) AS Year FROM tblFire
>
[quoted text clipped - 3 lines]
>>
>> t

Reply to this Message