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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Shorten String

Status
Not open for further replies.

stuckDuck

Programmer
Jun 30, 2010
30
CA
In an access database the field "date1" is a text datatype that is formatted as "yyyy-mm-dd hh:mm:ss"

How do i shorten this to display only "mm-dd"?
I'm getting the left side but i need it to cut off.
LEFT({Anaheim_Import_Data.Incident_Date},6)


How do I convert this to "mm-dd-yyyy"?




 
Hi,
use the InStr function to locate the position of the mm-dd part then use the MID function to extract it"

Code:
MID({Anaheim_Import_Data.Incident_Date},Instr("mm-dd"),5)

[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Try:

totext(datetime({Anaheim_Import_Data.Incident_Date}),"MM-dd")

Note that the "MM" must be in upper case to distinguish it from minutes.

-LB
 
PS. You could also just leave it as a datetime and then go to format field->date and set the formatting there.

-LB
 
Hi,
I think the OP just wants to parse out the string "mm-dd" not convert it to a date -
stuckDuck said:
text datatype that is formatted as "yyyy-mm-dd hh:mm:ss"

How do i shorten this to display only "mm-dd"?

[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
This worked perfectly to display the data.

totext(datetime({Anaheim_Import_Data.Incident_Date}),"MM-dd")



It does not let me perform simple addition or subtration.
 
DATE1 = totext(datetime({Anaheim_Import_Data.Incident_Date}),"MM-dd")

DATE2 = totext(datetime({Anaheim_Import_Data.Total_Date}),"MM-dd")


I cannot do the math of:
if not(isnull(DATE2)) then DATE1 - DATE2

I tried tonumber(totext(datetime({Anaheim_Import_Data.Total_Date}),"MM-dd") and several combinations of that back and forth. It gives the error Bad Date Format String
 
Then use my earlier suggestion to convert the fields to dates (which can be formatted to display as MM-dd):

datetime({Anaheim_Import_Data.Incident_Date})

Then you can subtract one from the other or use:

if not isnull({@date2}) then
datediff("d",{@date2},{@date1})

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top