Results 1 to 5 of 5
  1. #1
    lynnmc26 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    18

    Date Field - Access - from Linked Table

    I have a linked table set up in Access 2007. The cells in Excel are a number of date fields and are set as such. Issue is in Access the data (date) is being stored as a number. Common problem, however, I can't really find a solution anywhere - so thought I would come to my experts here for help. What is the best way for me to start seeing the date as a date format in Access?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I do not typically link to Excel. However, when I ask Access to Import data from the outside, often times I will look at the outside data as text. Once I have it as text, I convert it to the appropriate data type and include error trapping. I then place my converted and validated data in a permanent home.

  3. #3
    lynnmc26 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    18
    Thanks - I have good luck with using the importing functions into Access. For the spreadsheet linked to the Access table I need the cell in excel to be a date field so the dates can be entered in on a daily basis. Still looking for an answer

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I was not implying that changing data types of outside data is a solution or even a practice. If having your Access field as type Date is not providing the desired result, try type Text. Then, use another column as production data, after conversion and validation.

  5. #5
    lynnmc26 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    18
    hmm - gonna give that a try - thanks again

    I solved it - all I did was go into the form in Access and set the Format property to "Medium Date" and it converted the number to the date format. The cell in Excel is set to "Date" format so all is good and was simple. Not sure why I didn't try that in the first place!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-24-2013, 04:20 PM
  2. Access table linked to excel - field size problem
    By tzmyo in forum Import/Export Data
    Replies: 1
    Last Post: 10-02-2013, 01:03 AM
  3. Replies: 21
    Last Post: 10-29-2012, 02:01 PM
  4. Replies: 3
    Last Post: 11-19-2009, 09:15 PM
  5. Replies: 2
    Last Post: 10-27-2009, 07:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums