Format Date (1 Viewer)

Gismo

Registered User.
Local time
Today, 21:53
Joined
Jun 12, 2017
Messages
1,298
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, 19:53
Joined
Jul 26, 2013
Messages
10,355
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, 12:53
Joined
Oct 29, 2018
Messages
21,358
Hi. Try
Format([StartDate],"0000\/00\/00")
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:53
Joined
May 7, 2009
Messages
19,169
also:

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

onur_can

Active member
Local time
Today, 12:53
Joined
Oct 4, 2015
Messages
180
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, 15:53
Joined
Feb 19, 2002
Messages
42,973
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