Exporting table containing dates

okerkula · Apr 15, 2009

Hello everyone,

I would really appreciate your advice with how to export an Access table to
Excel and maintain the "long date" format. Currently when I export a long
date from Access - example; Wednesday, April 15, 2009; it will export to
Excel as a short date - example 4/15/09.

I am having the same problem when I export date fields that are setup to
display only the "weekday" - instead of exporting "Wednesday" it will export
4/15/09.

Thank you - Your help is very much appreciated.

Ken Snell MVP · Apr 16, 2009

Use a calculated field as the one with the "Wednesday" in it, and use Format
function to give you the desired format:

SELECT Field1, Field2, Format(Field3IsTheDate, "dddd") AS Field3, Field4
FROM Yourtablename

okerkula · Apr 16, 2009

Thanks so much Ken for taking the time to answer my post. However, I'm still
confused. You suggested I use a calculated field as the one with "Wednesday"
- but to display the weekday I did not use a calculated field, instead using
a Query, I put "dddd" into the format section of the field properties.

From my query, I did try using the expression builder and entered the
following in field 3;
Format(Field3IsTheDate, "dddd")
I was prompted the following message; "Format is an undefined function"

Maybe it would help if I provided the Table name and field name:
The Table name is: Test
The Field name is: date

Would you please reply with a bit more clarification - or if you don't mind
a phone call to walk me through the steps would be very much appreciated.

I work for Girl Scouts of Northern CA and can be reached at 408-287-4170 X235.

I really hope to hear from you or someone who can help me with this very
important project.

Thanks again,

Olivia
------------------------------------------------------------------------------

Ken Snell MVP · Apr 16, 2009

Create a query based on your table. (Open New Query in design view, add your
table to the design grid, and close the table window.)

Put all the fields except for the date field onto the grid.

In the first empty column, type this in the Field: box:
Date_Format: Format([date], "dddd")

Save the query, name it something like qryTest.

Now export this query instead of exporting your table.

Also, note that you should not name a field "date". It and many other words
are reserved words in ACCESS and should not be used for control names, field
names, etc. Allen Browne (MVP) has a very comprehensive list of reserved
words at his website:

Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html

See these Knowledge Base articles for more information about reserved words
and characters that should not be used (these articles are applicable to
ACCESS 2007 as well):

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

See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

okerkula · Apr 24, 2009

Hi again Ken,

First I have to thank you again for replying to my post. I tried your
suggestion and again Access is not liking the word "Format". I keep getting
the following message;

"Format is an undefined function"

Do you have any other suggestions? I really need help with this.

Thanks

Olivia

Ken Snell MVP said:
Create a query based on your table. (Open New Query in design view, add your
table to the design grid, and close the table window.)

Put all the fields except for the date field onto the grid.

In the first empty column, type this in the Field: box:
Date_Format: Format([date], "dddd")

Save the query, name it something like qryTest.

Now export this query instead of exporting your table.

Also, note that you should not name a field "date". It and many other words
are reserved words in ACCESS and should not be used for control names, field
names, etc. Allen Browne (MVP) has a very comprehensive list of reserved
words at his website:

Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html

See these Knowledge Base articles for more information about reserved words
and characters that should not be used (these articles are applicable to
ACCESS 2007 as well):

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

See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

okerkula said:

Thanks so much Ken for taking the time to answer my post. However, I'm
still
confused. You suggested I use a calculated field as the one with
"Wednesday"
- but to display the weekday I did not use a calculated field, instead
using
a Query, I put "dddd" into the format section of the field properties.

From my query, I did try using the expression builder and entered the
following in field 3;
Format(Field3IsTheDate, "dddd")
I was prompted the following message; "Format is an undefined function"

Maybe it would help if I provided the Table name and field name:
The Table name is: Test
The Field name is: date

Would you please reply with a bit more clarification - or if you don't
mind
a phone call to walk me through the steps would be very much appreciated.

I work for Girl Scouts of Northern CA and can be reached at 408-287-4170
X235.

I really hope to hear from you or someone who can help me with this very
important project.

Thanks again,

Olivia

Click to expand...

Douglas J. Steele · Apr 24, 2009

Your References collection is probably messed up.

References problems can be caused by differences in either the location or
file version of certain files between the machine where the application was
developed, and where it's being run (or the file missing completely from the
target machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, go into the VB Editor and select
Tools | References from the menu bar. Examine all of the selected
references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

okerkula said:
Hi again Ken,

First I have to thank you again for replying to my post. I tried your
suggestion and again Access is not liking the word "Format". I keep
getting
the following message;

"Format is an undefined function"

Do you have any other suggestions? I really need help with this.

Thanks

Olivia

Ken Snell MVP said:

Create a query based on your table. (Open New Query in design view, add
your
table to the design grid, and close the table window.)

Put all the fields except for the date field onto the grid.

In the first empty column, type this in the Field: box:
Date_Format: Format([date], "dddd")

Save the query, name it something like qryTest.

Now export this query instead of exporting your table.

Also, note that you should not name a field "date". It and many other
words
are reserved words in ACCESS and should not be used for control names,
field
names, etc. Allen Browne (MVP) has a very comprehensive list of reserved
words at his website:

Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html

See these Knowledge Base articles for more information about reserved
words
and characters that should not be used (these articles are applicable to
ACCESS 2007 as well):

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

See this site for code that allows you to validate your names as not
being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA
Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

okerkula said:

Thanks so much Ken for taking the time to answer my post. However, I'm
still
confused. You suggested I use a calculated field as the one with
"Wednesday"
- but to display the weekday I did not use a calculated field, instead
using
a Query, I put "dddd" into the format section of the field properties.

From my query, I did try using the expression builder and entered the
following in field 3;
Format(Field3IsTheDate, "dddd")
I was prompted the following message; "Format is an undefined function"

Maybe it would help if I provided the Table name and field name:
The Table name is: Test
The Field name is: date

Would you please reply with a bit more clarification - or if you don't
mind
a phone call to walk me through the steps would be very much
appreciated.

I work for Girl Scouts of Northern CA and can be reached at
408-287-4170
X235.

I really hope to hear from you or someone who can help me with this
very
important project.

Thanks again,

Olivia
------------------------------------------------------------------------------
:

Use a calculated field as the one with the "Wednesday" in it, and use
Format
function to give you the desired format:

SELECT Field1, Field2, Format(Field3IsTheDate, "dddd") AS Field3,
Field4
FROM Yourtablename

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hello everyone,

I would really appreciate your advice with how to export an Access
table
to
Excel and maintain the "long date" format. Currently when I export
a
long
date from Access - example; Wednesday, April 15, 2009; it will
export
to
Excel as a short date - example 4/15/09.

I am having the same problem when I export date fields that are
setup
to
display only the "weekday" - instead of exporting "Wednesday" it
will
export
4/15/09.

Thank you - Your help is very much appreciated.

Click to expand...

Click to expand...

okerkula · Apr 24, 2009

Thank you so, so much -

You're right, it was a missing reference link causing so much havoc. The
query is working fine now!! Thank you so, so much for taking the time to
assist me with this problem.

Olivia

Douglas J. Steele said:
Your References collection is probably messed up.

References problems can be caused by differences in either the location or
file version of certain files between the machine where the application was
developed, and where it's being run (or the file missing completely from the
target machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, go into the VB Editor and select
Tools | References from the menu bar. Examine all of the selected
references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

okerkula said:

Hi again Ken,

First I have to thank you again for replying to my post. I tried your
suggestion and again Access is not liking the word "Format". I keep
getting
the following message;

"Format is an undefined function"

Do you have any other suggestions? I really need help with this.

Thanks

Olivia

Ken Snell MVP said:

Create a query based on your table. (Open New Query in design view, add
your
table to the design grid, and close the table window.)

Put all the fields except for the date field onto the grid.

In the first empty column, type this in the Field: box:
Date_Format: Format([date], "dddd")

Save the query, name it something like qryTest.

Now export this query instead of exporting your table.

Also, note that you should not name a field "date". It and many other
words
are reserved words in ACCESS and should not be used for control names,
field
names, etc. Allen Browne (MVP) has a very comprehensive list of reserved
words at his website:

Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html

See these Knowledge Base articles for more information about reserved
words
and characters that should not be used (these articles are applicable to
ACCESS 2007 as well):

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

See this site for code that allows you to validate your names as not
being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA
Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

Thanks so much Ken for taking the time to answer my post. However, I'm
still
confused. You suggested I use a calculated field as the one with
"Wednesday"
- but to display the weekday I did not use a calculated field, instead
using
a Query, I put "dddd" into the format section of the field properties.

From my query, I did try using the expression builder and entered the
following in field 3;
Format(Field3IsTheDate, "dddd")
I was prompted the following message; "Format is an undefined function"

Maybe it would help if I provided the Table name and field name:
The Table name is: Test
The Field name is: date

Would you please reply with a bit more clarification - or if you don't
mind
a phone call to walk me through the steps would be very much
appreciated.

I work for Girl Scouts of Northern CA and can be reached at
408-287-4170
X235.

I really hope to hear from you or someone who can help me with this
very
important project.

Thanks again,

Olivia
------------------------------------------------------------------------------
:

Use a calculated field as the one with the "Wednesday" in it, and use
Format
function to give you the desired format:

SELECT Field1, Field2, Format(Field3IsTheDate, "dddd") AS Field3,
Field4
FROM Yourtablename

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hello everyone,

I would really appreciate your advice with how to export an Access
table
to
Excel and maintain the "long date" format. Currently when I export
a
long
date from Access - example; Wednesday, April 15, 2009; it will
export
to
Excel as a short date - example 4/15/09.

I am having the same problem when I export date fields that are
setup
to
display only the "weekday" - instead of exporting "Wednesday" it
will
export
4/15/09.

Thank you - Your help is very much appreciated.

Click to expand...

Click to expand...