|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 12th, 2007, 09:50 PM
posted to microsoft.public.access.queries
|
|||
|
|||
date format - datediff - Not working
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. -- Buck 
|
|
#2
April 12th, 2007, 10:20 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
date format - datediff - Not working
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\#") -- Good Luck BS"D "buckpeace" wrote: 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. -- Buck
|
|
#3
April 12th, 2007, 10:59 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
date format - datediff - Not working
On Thu, 12 Apr 2007 13:50:00 -0700, buckpeace wrote:
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. 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. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
|
#4
April 13th, 2007, 01:10 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
date format - datediff - Not working
On Thu, 12 Apr 2007 13:50:00 -0700, buckpeace
wrote: 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. 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]
|
|
#5
April 13th, 2007, 01:15 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
date format - datediff - Not working
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 '================================================= === buckpeace wrote: 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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
