Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-01-22
8
Medium Priority
?
746 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 66

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 66

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 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 66

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

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 …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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 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 …

721 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