access substring 1

[manny1234]

I need to take a date that is formatted like this yyyymmddhhmmss and turn it into mm/dd/yyyy. I have an aspx page that uses substring to accomplish this and I figured I could do the same in access but noooo here is the code I have

substring(shipdate,5,2) + '/' + substring(shipdate,7,2) + '/' + substring(shipdate,0,5) as shipdate2

When I try it I get Undefined function Substring.

 

[Melagan]

In Access, you'll probably want to use the Format() function.

Ex:
Format([SomeDateField], "mm/dd/yy")

~Melagan
______
"It's never too late to become what you might have been.

 

[manny1234]

Thanks but the field is not a date/time field it is a long integer if I remember correctly and the data stored in it looks like this 20060601155900. yyyymmddhhmmss

 

[dhookom]

Try

DateSerial(Int([ShipDate]/10000000000),Int([ShipDate]/100000000) Mod 100,Int([ShipDate]/1000000) Mod 100)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Melagan]

The number "20060601155900" is far too great a number to be stored as a long integer. I'll bet it's stored as a string. I'll work on a VB function that will help format this correctly, unless someone beats me to the punch. Meanwhile, can you verify exactly what data-type your psuedo-date field is?

~Melagan
______
"It's never too late to become what you might have been.

 

[manny1234]

dhookom, that worked thanks.

Melagen thanks for the attempt.

 

[manny1234]

just so I understand for the first position in dateserial it grabs everything from the position one is in to the left for a total of four digits, and the same for the second and third except they grab 2 digits?

 

[dhookom]

I try not to use character/string functions such as Left(), Right(), Mid() when I really should be working with numeric values. My expression performs divisions and finds remainders.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]