Date Format in CSV and in Access Table

[hycho]

Hi,

In CSV, I have the date as 7/23/2012. But in an Access table, the date is 2012-07-23 and stored as a text. I would like to have the Access table to have the format 7/23/2012, so that I can run queries using the filter: between 7/1/2012 and 8/1/2012.

FYI, I am linking the CSV file from MS Access, and did not import the table.

Is there anyway I can change the data format to 7/23/2012 from 2012-07-23 using a query or changing some options in Access? I would like to have this changed be as automated as possible.

Thanks,
Henry

 

[Beetle]

Try the CDate function;

CDate([YourTextField])

 

[hycho]

How do you use the cdate function? I tried putting the Cdate function in a query, and I get an error message saying "Invalid use of Null".

I used the function like this below:
CDate([STATUS_DATE])

I am looking to convert a text string into a short date, as in 1/1/2012.

Thanks for any help.

 

[hycho]

Nevermind, I got my problem solved using the formula below by boblarson.

MyNewFieldNameHere:IIF(Len([STATUS_DATE] & "") = 0, NULL, CDATE([STATUS_DATE]))

 

[hycho]

Are you sure the date is stored as text? Look at the format property on the date column and change it to Short Date. It is probably Long Date now. The named date formats are controlled by your Windows configuration. You can change them using the control panel.

If I got to the CSV file, and look at the property a cell in the date column, it will indicate it as a date, type as "*3/14/2001".

If I go to the Access table linking to the CSV file, it indicates the date column datatype as a text (when I open in design view). When I change the date column datatype to "date/time" and save it as a short date, I get a message saying "Microsoft Office Access can't save changes for link tables."

I tried going to the control panel for my computer, but I am not authorize to make any changes. I will have to call an IT guy to get that fix. Is there a specific section that says I can change the format of dates for Access link tables?

Thanks.

 

[hycho]

No. You can't change the properties of linked tables. I don't see why Access would change the format if it thinks it is text. Can up upload a copy of the file with the problem? Just a few records would be fine. Also, what version of Access and Windows are you using?

I kind of solved the problem. When I open the CSV file, and save it as new, the date format correct itself when I am linking. I also notice that the CSV file size gets like 5% smaller when I save as the new file, even though I didn't do anything. I can't post attach the original file to this website, as I might get in trouble. I will try to resolve the issue with the IT people for now. The problem also goes away when I save it as an Excel file. I am going to try to ask them to export it into an Access database perhaps.

Thanks for your help.