?
Solved

Access Linked excel CSV will not accept YYYY-MM-DD hh:mm:SSAM/PM

Posted on 2012-08-25
5
Medium Priority
?
545 Views
Last Modified: 2012-08-25
Access Importing and Linking Experts:
A #Num! error occurs When linking an Excel CSV that contains the date as YYYY-MM-DD hh:mm:SSAM/PM.
Properties in the advanced dialogue are set and changing code page does not seem to help.
Seting to text displays the value.
IE: 2012-06-19 12:00AM
What if anything could we do to maintain the Date Format?
AJP
0
Comment
Question by:AMixMaster
  • 3
  • 2
5 Comments
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38332892
Insert a space before AM/PM:

YYYY-MM-DD hh:mm:SS AM/PM

/gustav
0
 

Author Comment

by:AMixMaster
ID: 38333001
The source data is a CSV file (report) generated by Cognos with data from an Oracle Database.  
The Access table is linked to the downloaded CSV report.  

Modifying the incoming data every time it is renewed would be required.  
I would prefer to set the import specs to recognize the data as it is recieved.  

The solution seems to be in the code page?  

AJP
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 38333043
No, there is no code page issue here.

From your example data you can link the field as text.

Then create a query using the linked table as source.
For your date field, use the expression:

MyDate: IIf(IsDate([YourTextField]),DateValue([YourTextField]),Null)

/gustav
0
 

Author Closing Comment

by:AMixMaster
ID: 38333062
gustav:
You are The Man!
Thanks Again!
AJP
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38333147
You are welcome!

/gustav
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question