Shorten String

[stuckDuck]

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"?

 

[Turkbear]

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

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

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

 

[lbass]

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

 

[lbass]

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

-LB

 

[Turkbear]

Hi,
I think the OP just wants to parse out the string "mm-dd" not convert it to a date -

text datatype that is formatted as "yyyy-mm-dd hh:mm:ss"

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

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

 

[stuckDuck]

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.

 

[lbass]

What do you mean by:

It does not let me perform simple addition or subtration.

-LB

 

[stuckDuck]

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

 

[lbass]

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

 

[stuckDuck]

yeah thank You!