Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rating: Thread Rating: 57 votes, 5.00 average. Display Modes
Old 10-12-2009, 03:40 PM   #1
tangcla
Newly Registered User
 
Join Date: Sep 2008
Location: Melbourne, Australia
Posts: 35
Thanks: 2
Thanked 0 Times in 0 Posts
tangcla is on a distinguished road
Linked CSV file, date format error, #Num! ...

I need some help with a linked table in Access.

I have a linked CSV file, in which some columns are date fields. I import it as a date/time and when I try to open the table (or run queries) it returns #Num! .

The text field in the CSV file is like this (opened in Notepad): 21/07/2009 12:00:00 PM

Is there any way I can link this text file through WITHOUT importing it as a text field? As I have the date field linked to another date table in a query.
Attached Files
File Type: zip Extract.zip (1.7 KB, 291 views)


Last edited by tangcla; 10-21-2009 at 06:51 PM.
tangcla is offline   Reply With Quote
Old 10-12-2009, 11:49 PM   #2
DCrake
Remembered
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 325 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: Linked CSV file, date format error, #Num! ...

I would check the full column that contains the date field. It may be that certain records have invalid data in them and that is what is causing the error.

If not that post a sample to look at.
David
__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Old 10-13-2009, 12:08 AM   #3
tangcla
Newly Registered User
 
Join Date: Sep 2008
Location: Melbourne, Australia
Posts: 35
Thanks: 2
Thanked 0 Times in 0 Posts
tangcla is on a distinguished road
Re: Linked CSV file, date format error, #Num! ...

Quote:
Originally Posted by DCrake View Post
I would check the full column that contains the date field. It may be that certain records have invalid data in them and that is what is causing the error.
Thanks David, I don't think the file contains a footer at that range, I know it contains one earlier on. However even if it does, the values with a valid date should still show up, right? That's the weird part.

I'll get a sample of the CSV and see if it makes more sense that way.

tangcla is offline   Reply With Quote
Old 10-18-2009, 04:57 PM   #4
sportsguy
Finance wiz, Access hack
 
sportsguy's Avatar
 
Join Date: Dec 2004
Location: Boston, MA, USA
Posts: 341
Thanks: 4
Thanked 1 Time in 1 Post
sportsguy has a little shameless behaviour in the past
Re: Linked CSV file, date format error, #Num! ...

if you check the #num records in a text editor, are the date fields null?

access has a hard time with null date fields.

sportsguy
sportsguy is offline   Reply With Quote
Old 10-18-2009, 05:45 PM   #5
tangcla
Newly Registered User
 
Join Date: Sep 2008
Location: Melbourne, Australia
Posts: 35
Thanks: 2
Thanked 0 Times in 0 Posts
tangcla is on a distinguished road
Re: Linked CSV file, date format error, #Num! ...

Quote:
Originally Posted by sportsguy View Post
if you check the #num records in a text editor, are the date fields null?

access has a hard time with null date fields.

sportsguy
No, they're the ones with dates. The null ones are fine, blank.
tangcla is offline   Reply With Quote
Old 10-19-2009, 02:31 AM   #6
Kiwiman
Registered User
 
Kiwiman's Avatar
 
Join Date: Apr 2008
Location: Bath, UK
Posts: 799
Thanks: 8
Thanked 57 Times in 57 Posts
Kiwiman will become famous soon enough Kiwiman will become famous soon enough
Send a message via ICQ to Kiwiman
Re: Linked CSV file, date format error, #Num! ...

Howzit

When you linked the table, did you check that the data type of the date field is set to Date\Time. Access will take a guess at the data type based on the contents on the first row (few rows??) and may well get it wrong.

I have just linked a csv sheet, and selected datatype double for a date field and the vlaues in the table come up with #NUM. Changing the datatype to date\time imports links them correctly.
__________________
HTH's
Take It Easy
Pete
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Man who keep feet firmly on ground have trouble putting on pants.
Kiwiman is offline   Reply With Quote
Old 10-19-2009, 02:35 AM   #7
tangcla
Newly Registered User
 
Join Date: Sep 2008
Location: Melbourne, Australia
Posts: 35
Thanks: 2
Thanked 0 Times in 0 Posts
tangcla is on a distinguished road
Re: Linked CSV file, date format error, #Num! ...

Definitely did have date/time as the field data type My current workaround is linking it as a text field, it's cumbersome but it'll do for now

tangcla is offline   Reply With Quote
Old 10-19-2009, 02:37 AM   #8
Kiwiman
Registered User
 
Kiwiman's Avatar
 
Join Date: Apr 2008
Location: Bath, UK
Posts: 799
Thanks: 8
Thanked 57 Times in 57 Posts
Kiwiman will become famous soon enough Kiwiman will become famous soon enough
Send a message via ICQ to Kiwiman
Re: Linked CSV file, date format error, #Num! ...

Howzit

Can you post a copy of your csv file?
__________________
HTH's
Take It Easy
Pete
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Man who keep feet firmly on ground have trouble putting on pants.
Kiwiman is offline   Reply With Quote
Old 10-19-2009, 02:38 AM   #9
tangcla
Newly Registered User
 
Join Date: Sep 2008
Location: Melbourne, Australia
Posts: 35
Thanks: 2
Thanked 0 Times in 0 Posts
tangcla is on a distinguished road
Re: Linked CSV file, date format error, #Num! ...

Yep, will do it tomorrow at work. I hope I remember.
tangcla is offline   Reply With Quote
Old 10-19-2009, 04:26 AM   #10
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 800 Times in 787 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Linked CSV file, date format error, #Num! ...

this probably has to do with the US vs Euro format of dates...

DD/MM vs MM/DD, where your date is Euro and access is using US
Gotta love dates
__________________
ruoY yppah namliaM si syawla yppah ot pleh
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.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 10-19-2009, 12:46 PM   #11
tangcla
Newly Registered User
 
Join Date: Sep 2008
Location: Melbourne, Australia
Posts: 35
Thanks: 2
Thanked 0 Times in 0 Posts
tangcla is on a distinguished road
Re: Linked CSV file, date format error, #Num! ...

Nah, not dd/mm vs mm/dd. Both date sources are normal (dd/mm) for my region
tangcla is offline   Reply With Quote
Old 10-19-2009, 01:10 PM   #12
Kiwiman
Registered User
 
Kiwiman's Avatar
 
Join Date: Apr 2008
Location: Bath, UK
Posts: 799
Thanks: 8
Thanked 57 Times in 57 Posts
Kiwiman will become famous soon enough Kiwiman will become famous soon enough
Send a message via ICQ to Kiwiman
Re: Linked CSV file, date format error, #Num! ...

Howzit

How about setting the text qualifier to " or ' when you are linking the csv file. If actual text values have a comma in the string it will effectively move everthing over into incorrect fields when linking.

E.g. A column with "coming, and going" without the text qualifier set will effectivley split this into two distinct fields. One field will have "Coming" and the another will have "and going"
__________________
HTH's
Take It Easy
Pete
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Man who keep feet firmly on ground have trouble putting on pants.
Kiwiman is offline   Reply With Quote
Old 10-19-2009, 01:13 PM   #13
tangcla
Newly Registered User
 
Join Date: Sep 2008
Location: Melbourne, Australia
Posts: 35
Thanks: 2
Thanked 0 Times in 0 Posts
tangcla is on a distinguished road
Re: Linked CSV file, date format error, #Num! ...

Quote:
Originally Posted by Kiwiman View Post
How about setting the text qualifier to " or ' when you are linking the csv file. If actual text values have a comma in the string it will effectively move everthing over into incorrect fields when linking.
Done that too I learnt from previous CSVs which had ""s.
tangcla is offline   Reply With Quote
Old 10-20-2009, 12:45 AM   #14
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 800 Times in 787 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Linked CSV file, date format error, #Num! ...

Quote:
Originally Posted by tangcla View Post
Nah, not dd/mm vs mm/dd. Both date sources are normal (dd/mm) for my region
Bull hockey !

Yes nice and all, but Access using (semi hidden to you) MM/DD not DD/MM

Try searching any table using a query for the date #08-05-2009#
You will find it returns NOT 8 may, but Aug 5 !
__________________
ruoY yppah namliaM si syawla yppah ot pleh
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.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 10-21-2009, 06:51 PM   #15
tangcla
Newly Registered User
 
Join Date: Sep 2008
Location: Melbourne, Australia
Posts: 35
Thanks: 2
Thanked 0 Times in 0 Posts
tangcla is on a distinguished road
Re: Linked CSV file, date format error, #Num! ...

I've added an extract of the CSV file to the first post. It's taken me a bit longer to remember than I hoped!

tangcla is offline   Reply With Quote
Reply

Tags
csv , import , link , table , text

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Converting Date from string to date format problem!!!! URGENT HELP NEEDED THANKS redyul Queries 1 10-28-2009 11:11 PM
Date Format Issue psatkar Modules & VBA 8 01-22-2009 05:41 AM
MDW file linked to MDB? AC5FF General 9 01-20-2009 11:16 AM
are my if statements clashing? hoda64 Modules & VBA 6 07-17-2008 10:14 AM
Uppercase date format purple_chicken Reports 4 11-23-2005 11:58 AM




All times are GMT -8. The time now is 03:35 PM.


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