Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rating: Thread Rating: 57 votes, 5.00 average. Display Modes
Old 10-22-2009, 12:32 AM   #16
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! ...

Very strange, the dates seem to be valid, but still return #num!??

Taking away the PM part fixes some but not all, is there some unreadable character or something that is messing this up??

Looks very wierd... I would go with import as text and use a Dateserial + Left/right/mid functions to convert it into a real date and be over with it.

__________________
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-22-2009, 12:34 AM   #17
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! ...

haha see? told you so
I've linked it as a text field now, is it possible to link that field to another table's date field without doing anything else? It give sme a data type mismatch at the moment, but if there's a way of doing it, that'd be great...
tangcla is offline   Reply With Quote
Old 10-22-2009, 12:36 AM   #18
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

You may want to see this link from http://support.microsoft.com/kb/89618

Imports will only be successful where the date\time format will be in a 24 hour format. They fail if you have the 1:00 PM format. Admitedly news to me.

This link also has some possible solutions.

__________________
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-22-2009, 12:38 AM   #19
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! ...

Thanks Kiwiman, sadly those solutions don't look as if it would work if I had a linked table and no import involved at all (i.e. query run on top of that linked table directly)
tangcla is offline   Reply With Quote
Old 08-13-2010, 08:49 AM   #20
themurph2000
Fat, drunk, and stupid
 
themurph2000's Avatar
 
Join Date: Sep 2007
Location: Chicago, IL USA
Posts: 181
Thanks: 8
Thanked 11 Times in 11 Posts
themurph2000 is on a distinguished road
Re: Linked CSV file, date format error, #Num! ...

Quote:
Originally Posted by Kiwiman View Post
Howzit

You may want to see this link from http://support.microsoft.com/kb/89618

Imports will only be successful where the date\time format will be in a 24 hour format. They fail if you have the 1:00 PM format. Admitedly news to me.

This link also has some possible solutions.
I had this same problem with a database program designed for Windows Mobile (since there's no Access for Mobile). I converted the field to record time into a text field. In Access, that same field is formatted as a Date/Time field (and I specify the format). Thankfully, it works.

This might be the way to go if using a military time format won't work for you.

Thanks for the suggestions!
__________________
Patrick J. Murphy
Chicago, IL

"The only thing I hate more than hippie neo-liberal fascists and anarchists are the hypocrite fat cat suits they eventually grow up to become."---John Casey, Chuck
themurph2000 is offline   Reply With Quote
Old 10-25-2012, 12:19 AM   #21
QuChe Yvonne
Newly Registered User
 
Join Date: Oct 2012
Posts: 11
Thanks: 3
Thanked 0 Times in 0 Posts
QuChe Yvonne is on a distinguished road
Re: Linked CSV file, date format error, #Num! ...

OT Hrs: IIf([Production Details]![Depositing Qty Overtime]<30,"0",Round(([Production Details]![End Time]-[Production Details]![End Work])*24,2))

The result was show properly. But when I used it the OT Hrs to multiply the OT quantity. The answer correctly calculation execpt the OT value was 0. The error show as #Num!.

Anyone could help for this? Thanks in advance.

QuChe Yvonne 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 11:08 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