Format Date (1 Viewer)

[Gismo]

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]

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]

Hi. Try
Format([StartDate],"0000\/00\/00")

 

[arnelgp]

also:

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

 

[Gismo]

also:

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

This worked fine

 

[onur_can]

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]

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.