Cdate Function (1 Viewer)

[hycho]

Hi,

I am trying to convert a text string into a date string, and was recommended to use a cdate() function. I tried looking at the web and the help function in Access, but could not figure out how to use it.

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. I am hoping this function will help me filter information when I run a query (as in, I want any records between 1/1/2012 and 2/1/2012).

Thanks for any help.

 

[boblarson]

You have nulls in your field (or empty strings) and you can't convert those to dates. So modify to this:

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

 

[mdlueck]

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

So you are passing in "1/1/2012" and wanting the date datatype representation of the same format returned? (I do not think that exactly likely...)

I am hoping this function will help me filter information when I run a query (as in, I want any records between 1/1/2012 and 2/1/2012).

For this, I have seen posts on this forum suggesting to wrap the date to be search for inside of ## characters. Try looking searching along those lines.

 

[hycho]

You have nulls in your field (or empty strings) and you can't convert those to dates. So modify to this:

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

Thanks for the quick response. By any chance, do you know how I can link a CSV file in Access, so that it appears to be in a date format.

Currently, the CSV file has the correct date format of "1/1/2012." But when I link it, and look at in the Access table, the date format becomes a text with this date format "2012-01-01." Is there any way I can fix the CSV file so when I link it through Access, it will appear in the correct date format?

FYI, the CSV file gets refresh daily, which is why I am linking to the file.

Thanks again.

 

[hycho]

So you are passing in "1/1/2012" and wanting the date datatype representation of the same format returned? (I do not think that exactly likely...)



For this, I have seen posts on this forum suggesting to wrap the date to be search for inside of ## characters. Try looking searching along those lines.

Thanks for the help, I have elaborated my problem some more in my response to Bob Larson.

 

[boblarson]

Thanks for the quick response. By any chance, do you know how I can link a CSV file in Access, so that it appears to be in a date format.

Currently, the CSV file has the correct date format of "1/1/2012." But when I link it, and look at in the Access table, the date format becomes a text with this date format "2012-01-01." Is there any way I can fix the CSV file so when I link it through Access, it will appear in the correct date format?

FYI, the CSV file gets refresh daily, which is why I am linking to the file.

Thanks again.

Importing is the way to go.

 

[mdlueck]

Is there any way I can fix the CSV file so when I link it through Access, it will appear in the correct date format?

Convert date format from YYYY-MM-DD to DD/MM/YYYY
http://www.access-programmers.co.uk/forums/showthread.php?t=230590#post1176680

Though I have never heard of Access switching dates to YYYY-MM-DD on its own.

 

[hycho]

Importing is the way to go.

Thanks, but I am trying to avoid importing the file because I have to do this import daily. I will speak to a co-worker to see he/she can save the fix the CSV file to an Excel file, so when I link I don't have to worry about the date issue.

 

[hycho]

Convert date format from YYYY-MM-DD to DD/MM/YYYY
http://www.access-programmers.co.uk/forums/showthread.php?t=230590#post1176680

Thought I have never heard of Access switching dates to YYYY-MM-DD on its own.

Thanks for the link.

It's pretty bizarre for me as well, and it gives me a hard time to find records by a date range filter.

 

[vbaInet]

Just fyi, you can use the CVDate() function which is Null safe. So you don't need the IIF() check for Nulls so it won't fail when it encounters Null.

CVDate([STATUS_DATE])

 

[hycho]

Just fyi, you can use the CVDate() function which is Null safe. So you don't need the IIF() check for Nulls so it won't fail when it encounters Null.

CVDate([STATUS_DATE])

This works well. Thanks.

After using the CVDate() and CDate(), I have come across an unusual problem using the query filter "between [Begin Date] and [End Date]." Let's say when I 8/1/2012 and end date as 9/1/2012, I get the following results in the bottom. When I actually use "Between #8/1/2012# And #9/1/2012#", I get the correct results as shown in query2.

Query1 Cred_Status_Date 9/1/2010 8/3/2011 8/26/2011 8/1/2012 8/2/2012 8/6/2012 8/7/2012 8/8/2012 8/9/2012 8/10/2012 8/12/2012 8/13/2012 8/15/2012
Query2 Cred_Status_Date 8/1/2012 8/2/2012 8/6/2012 8/7/2012 8/8/2012 8/9/2012 8/10/2012 8/12/2012 8/13/2012 8/15/2012
Thanks again.

 

[vbaInet]

You always need the octothorpes (#) for dates.