Format Date (1 Viewer)

Gismo

Registered User.
Local time
Today, 04:42
Joined
Jun 12, 2017
Messages
685
Hi All,

I have a imported file with date formatted as yyyymmdd with no spaces, no /

i want to format to yyyy/mm/dd but i get a overflow error

#Start Date: Format([IW73 TBL with SN]![Start Date],"yyyy/mm/dd")#
 

Minty

AWF VIP
Local time
Today, 02:42
Joined
Jul 26, 2013
Messages
7,798
I think that due to the lack of separators access won't recognise your field a valid date.
If you type the following into the immediate window you get a type mismatch.
? datevalue ("20200123")

So you may need to do some formatting of the string first.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:42
Joined
Oct 29, 2018
Messages
13,751
Hi. Try
Format([StartDate],"0000\/00\/00")
 

arnelgp

error reading drive A:
Local time
Today, 10:42
Joined
May 7, 2009
Messages
11,171
also:

Start Date: CDate(Format([IW73 TBL with SN]![Start Date], "0000/00/00"))
 

onur_can

Member
Local time
Yesterday, 18:42
Joined
Oct 4, 2015
Messages
141
do not include the / signs in your format.
Start Date: Format([IW73 TBL with SN]![Start Date],"yyyy/mm/dd")
Start Date: Format([IW73 TBL with SN]![Start Date],"yyyy mm dd")
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:42
Joined
Feb 19, 2002
Messages
30,665
I'm not sure what the # are for so I omitted them:

Start Date: Left([IW73 TBL with SN]![Start Date],4) & "/" & Mid([IW73 TBL with SN]![Start Date], 5,2) & "/" & Right([IW73 TBL with SN]![Start Date],2)

You can only use Format() on a VALID date field since yyyymmdd is not a valid date field, Format() will not work.
 

Users who are viewing this thread

Top Bottom