Format Date (1 Viewer)

Gismo

Registered User.
Local time
Today, 17:01
Joined
Jun 12, 2017
Messages
548
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, 15:01
Joined
Jul 26, 2013
Messages
7,177
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
Today, 07:01
Joined
Oct 29, 2018
Messages
11,199
Hi. Try
Format([StartDate],"0000\/00\/00")
 

arnelgp

error reading drive A:
Local time
Today, 22:01
Joined
May 7, 2009
Messages
9,925
also:

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

onur_can

Member
Local time
Today, 07:01
Joined
Oct 4, 2015
Messages
75
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
Today, 10:01
Joined
Feb 19, 2002
Messages
29,179
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 (Users: 0, Guests: 1)

Top Bottom