Export query to Excel - Format Date as Year and Number

[Thomas T via AccessMonster.com]

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

 

[Ken Snell [MVP]]

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

 

[Thomas T via AccessMonster.com]

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:

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

Hi, I have a date field in a table calles tblFire. I want to export the
year

[quoted text clipped - 16 lines]

t

 

[Guest]

SELECT Year(Date) AS Year FROM tblFire

 

[Ken Snell [MVP]]

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


--

Ken Snell
<MS ACCESS MVP>

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:

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

Hi, I have a date field in a table calles tblFire. I want to export the
year

[quoted text clipped - 16 lines]

t

 

[Thomas T via AccessMonster.com]

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
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

Thanks for your reply, unfortunately it does not help. The rows only
display

[quoted text clipped - 26 lines]

 

[Thomas T via AccessMonster.com]

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

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