date format - datediff - Not working

B

buckpeace

I have a date format of 19850617, I need it to be 06/17/1985. The
19850617 is in text format and when I run a query to change it, it comes out
as 105 - for everyone.....May I please ask for some help.
 
O

Ofer Cohen

I hope the field is always 8 chr, in that case you can try spliting the field
and then use the format

Format(Mid([FieldName],5,2) &"/" & Right([FieldName],2) & "/" &
Left([FieldName],4),"\#mm\/dd\/yyyy\#")
 
F

fredg

I have a date format of 19850617, I need it to be 06/17/1985. The
19850617 is in text format and when I run a query to change it, it comes out
as 105 - for everyone.....May I please ask for some help.
Click to expand...

If the text value is always 8 characters, then:

DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2))

will convert the text string 19850617 into a Date datatype value of
06/17/1985.
 
J

John W. Vinson

I have a date format of 19850617, I need it to be 06/17/1985. The
19850617 is in text format and when I run a query to change it, it comes out
as 105 - for everyone.....May I please ask for some help.
Click to expand...

Don't confuse *datatype* with *format*.

A Text field is *not a date*. This is an eight-character text string, and will
not be recognized as a date by Access.

See the other posts in this thread for how to convert it to a Date/Time field,
which can then be formatted any way you choose.

John W. Vinson [MVP]
 
J

John Spencer

DateValue(Format([YourField],"@@@@\/@@\/@@")

That assumes that the values in your datefield will always yield a
correct date and never be null (blank).

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Top