Solved

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

Posted on 2013-01-22
8
736 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
  • 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
Independent Software Vendors: 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!

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 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