Solved

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

Posted on 2013-01-22
8
731 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now