Solved

MS Access 2007 Remove "NA" from Date/Time Field

Posted on 2013-01-22
8
739 Views
Last Modified: 2013-01-22
I'm having trouble with a couple of fields from a "linked table".  
The field names are "Actual Start" and "Actual Finish".
The fields are supposed to be date fields but if a record does not have a date a "NA" appears.  This "NA" seems to override the Date\Time type and forces the field to a "Text" field.

I've attached a DB with an example table (in the real world this table is a linked table that I can't change).

Is there a way to configure a query to get rid of the "NAs" so that the fields can be exported out as a Date/Time field?
Test.accdb
0
Comment
Question by:dec789
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38807066
>This "NA" seems to override the Date\Time type and forces the field to a "Text" field.
That is correct, in order to pull off any non-date value in the column, it changes the data type to a Text.

>Is there a way to configure a query to get rid of the "NAs" so that the fields can be exported out as a Date/Time field?
Yes.  Instead of NA leave the value as a NULL, and allow whatever application consumes this data to have it's own logic to display the NA.
0
 

Author Comment

by:dec789
ID: 38807086
I do not have control over how the blank data is represented in the linked table.  I have to work with the data that is given in the linked table and in the linked table the blanks cells are replaced with NAs.  Is there a way to change the NAs to Null in Access???
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38807109
>I do not have control over how the blank data is represented in the linked table.
Okay, then it's Text.

>Is there a way to change the NAs to Null in Access???
I recommend creating a query in Access that goes something like this:

SELECT * IIF(IsDate(YourDateColumn), YourDateColumn, Null) as YourRenamedDateColumn
From LinkedTable

Open in new window


Then you can deal with YourRenamedDateColumn as a date/time
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 38807120
<slight correction>

SELECT *, IIF(IsDate(DateColumnName), DateColumnName, Null) as RenamedDateColumnName
From LinkedTableName

Open in new window


Then you can deal with RenamedDateColumnName as a date/time
0
 

Author Closing Comment

by:dec789
ID: 38807135
Thank you that worked perfectly.

Thank you so much for your time and patience with me.
0
 
LVL 8

Expert Comment

by:d0ughb0y
ID: 38807154
I'm not sure how to do what you want from Access, but I can give you a workaround.

Download the Windows version of the *nix sed command. You can get it from here. After unpacking it, and putting it somewhere you know how to reach it, you can issue the following command. Let's say you've exported the data to data.txt.

<path>\sed.exe -r s/"NA"/""/g data.txt >> data1.txt

The file data1.txt will now have "" in place of "NA". It's not as neat as having that fixed from within Access, but I'm not sure how you're going to get that result there.

Update: T'would seem I'm too late. ;)
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38807172
d0ughb0y - That could come in handy.  Thanks for posting.
0
 

Author Comment

by:dec789
ID: 38807399
doughboy - thank you for that tip.  It will not work for me in this specific situation but it looks like in the near future I will be doing a lot of this type of data transfer - I will definitely keep this in mind.
Thanks again!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

751 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