Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Format text date to 1st day of month...

Status
Not open for further replies.

air1access

Technical User
Jan 27, 2008
123
US
I have a table with records that hold a "text date" that is formatted like:
Mar-2015
Apr-2015
Feb-2015
Dec-2014

I need this field reformatted with a new field to show the value like:
3/1/2015
4/1/2015
2/1/2015
12/1/2014

Any suggestions or examples..?
 
It is better to store a real date value, then you can use the format function to display it anyways you want. So if you would have stored 1/1/2015 you can display it anywhere as Jan-2015. More importantly you then can do date calculations in your database if needed.

So I would convert it to a real date

Public Function TextToDate(strDate) as date
TextToDate = cdate("1-" & strDate)
end function

You can use this function in a query as

Select yourDate, TextToDate([yourDate]) as RealDate,...

The cdate function is real powerful. It can convert most strings or number that remotely looks like a date into a date.
 
The cdate function is more powerful than I thought
You do not even have to first coerce the string into 1-Mon-2015 format. Simply
cdate("Dec-2015") produces the date 12/1/2015
so in a query
Select yourDate, cdate([yourDate]) as RealDate
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top