Date formatting in a table

  • Thread starter Thread starter Gary W. Graley
  • Start date Start date
G

Gary W. Graley

Had an interesting insight recently, I usually set the INPUT MASK fo
dates as Short Date and when I run a query and copy and paste th
information into an Excel spread sheet, invariably the dates are no
recognized as dates and will not sort by dates, UNLESS I highlight th
column and do a Find/Replace by replacing all the - marks with - mark
again and THEN Excel says AH you have dates in this column...;)

So I put up with that nonsense for a long time, but as I say, recentl
I dug into it, because some fields came out as dates while others di
not. I noticed that on the ones that came out ok, in the Table desig
I didn't assign an INPUT MASK for entry to the 99-99-99;0;_
So I still left the Format to m-d-yy, but left the INPUT MASK empty an
only made that format at the Form creation area.

So NOW when I run the query and copy and paste the info, the dates ar
recognized as real dates, weird eh?

Just thought I'd share,
G
 
What you should rather do is set your date format in windows to your desired format (Regional Settings,Date/Time).The auto date format for Access will then be what you want it to be and it will be recognized in Excel as well

----- Gary W. Graley wrote: ----


Had an interesting insight recently, I usually set the INPUT MASK fo
dates as Short Date and when I run a query and copy and paste th
information into an Excel spread sheet, invariably the dates are no
recognized as dates and will not sort by dates, UNLESS I highlight th
column and do a Find/Replace by replacing all the - marks with - mark
again and THEN Excel says AH you have dates in this column...;

So I put up with that nonsense for a long time, but as I say, recentl
I dug into it, because some fields came out as dates while others di
not. I noticed that on the ones that came out ok, in the Table desig
I didn't assign an INPUT MASK for entry to the 99-99-99;0;_
So I still left the Format to m-d-yy, but left the INPUT MASK empty an
only made that format at the Form creation area

So NOW when I run the query and copy and paste the info, the dates ar
recognized as real dates, weird eh?

Just thought I'd share
G
 
Gary,

The recommended way to export data from an Access table or query to
Excel would be via the File|Export menu, or via a macro or vba procedure
using the TransferSpreadsheet action/method.
 
Steve said:
*Gary,
The recommended way to export data from an Access table or query to
Excel would be via the File|Export menu, or via a macro or vb
procedure
using the TransferSpreadsheet action/method.
Click to expand...

Thanks Steve, but even if you do that and have the table setup tha
way, you end up with Excel not recognizing the date as a date.

And X, yep I have my regional settings to display as I like, it's th
way things happen when once they leave Access and move to Excel, if th
table had that formatting, then I'd only get the 2 position date fiel
for the year and Excel said uh? but once I changed it in the table, an
then all was ok. One reason I have it set that way is some of th
people that use the databases I setup vary in the way their machine
are set, the regional part that is, so I wanted to keep it the same an
that way on reports the field size doesn't balloon up and not fit o
become unreadable for those that don't change to a 2 digit year.

G
 
Steve said:
*Gary,
The recommended way to export data from an Access table or query to
Excel would be via the File|Export menu, or via a macro or vb
procedure
using the TransferSpreadsheet action/method.
Click to expand...

Thanks Steve, but even if you do that and have the table setup tha
way, you end up with Excel not recognizing the date as a date.

And X, yep I have my regional settings to display as I like, it's th
way things happen when once they leave Access and move to Excel, if th
table had that formatting, then I'd only get the 2 position date fiel
for the year and Excel said uh? but once I changed it in the table, an
then all was ok. One reason I have it set that way is some of th
people that use the databases I setup vary in the way their machine
are set, the regional part that is, so I wanted to keep it the same an
that way on reports the field size doesn't balloon up and not fit o
become unreadable for those that don't change to a 2 digit year.

G
 
Steve said:
*Gary,
The recommended way to export data from an Access table or query to
Excel would be via the File|Export menu, or via a macro or vb
procedure
using the TransferSpreadsheet action/method.
Click to expand...

Thanks Steve, but even if you do that and have the table setup tha
way, you end up with Excel not recognizing the date as a date.

And X, yep I have my regional settings to display as I like, it's th
way things happen when once they leave Access and move to Excel, if th
table had that formatting, then I'd only get the 2 position date fiel
for the year and Excel said uh? but once I changed it in the table, an
then all was ok. One reason I have it set that way is some of th
people that use the databases I setup vary in the way their machine
are set, the regional part that is, so I wanted to keep it the same an
that way on reports the field size doesn't balloon up and not fit o
become unreadable for those that don't change to a 2 digit year.

G
 
Steve said:
*Gary,
The recommended way to export data from an Access table or query to
Excel would be via the File|Export menu, or via a macro or vb
procedure
using the TransferSpreadsheet action/method.
Click to expand...

Thanks Steve, but even if you do that and have the table setup tha
way, you end up with Excel not recognizing the date as a date.

And X, yep I have my regional settings to display as I like, it's th
way things happen when once they leave Access and move to Excel, if th
table had that formatting, then I'd only get the 2 position date fiel
for the year and Excel said uh? but once I changed it in the table, an
then all was ok. One reason I have it set that way is some of th
people that use the databases I setup vary in the way their machine
are set, the regional part that is, so I wanted to keep it the same an
that way on reports the field size doesn't balloon up and not fit o
become unreadable for those that don't change to a 2 digit year.

G
 
Back
Top