Format Date

Gismo

Registered User.
Local time
Today, 07:47
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")#
 
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.
 
Hi. Try
Format([StartDate],"0000\/00\/00")
 
also:

Start Date: CDate(Format([IW73 TBL with SN]![Start Date], "0000/00/00"))
 
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")
 
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

Back
Top Bottom