Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-11-2019, 12:31 PM   #1
marseneault
Newly Registered User
 
Join Date: Jan 2019
Location: Atlanta, GA USA
Posts: 24
Thanks: 4
Thanked 0 Times in 0 Posts
marseneault is on a distinguished road
Updating a linked Table field from Short Text to Date

Hello,
I am having issues with a linked table (in excel) not formating a date field as a date field.

I have an online program that outputs all the information in One Excel Spreadsheet with Tabs. That Spreadsheet is linked to it's corresponding table in my database.
Most of the Date Types are what they need to be, however, i have one date field that comes in as Short Text, instead of date/Time. this just happens to be a field where I need to run a search query by selecting a date range for the past 7 days.

What can I do to update this field to the proper Data Type?

marseneault is offline   Reply With Quote
Old 06-11-2019, 12:37 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,091
Thanks: 36
Thanked 735 Times in 718 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Updating a linked Table field from Short Text to Date

If you look at the Excel sheet, are there any empty rows towards the top of the problem column?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-12-2019, 02:59 AM   #3
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,768
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Updating a linked Table field from Short Text to Date

you could create a template table, and read the data into that table. If that fails, then import the data as at present, add another date field, and update the new field with the converted dates.

As TheDBGuy says, it's maybe worth inspecting the spreadsheet to understand what's going on. If dates in the table are badly formed, it will import as text

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Reply

Tags
data type , linked excel file , update queary

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[Access2000_Form]Date Format for Textbox which is linked with table text field. hmmh Forms 1 12-18-2015 06:43 AM
[SOLVED] Linked Table Date is Text kujospam Queries 10 07-23-2013 03:06 AM
Setting text box format to short date and optional short time choaspes Forms 7 06-21-2013 08:29 AM
linked excel table long date to short date conversion rrohde1 Queries 2 06-14-2012 08:32 AM
adding field in table using VBA data format - short date mazza Modules & VBA 1 06-11-2007 05:54 AM




All times are GMT -8. The time now is 01:06 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World