Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 760
  • Last Modified:

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

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
dec789
Asked:
dec789
  • 4
  • 3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
dec789Author Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<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
 
dec789Author Commented:
Thank you that worked perfectly.

Thank you so much for your time and patience with me.
0
 
d0ughb0yPresident / CEOCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
d0ughb0y - That could come in handy.  Thanks for posting.
0
 
dec789Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now