Format issue code without office 365

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,146
Hi all,

I have the code below that works fine in office 365 however some of my colleagues aint on office 365 and throws a format error issue on the line below

How can i get around it to work on both versions?

DoCmd.RunSQL "UPDATE WFM_AllResults SET Date_ = FORMAT(Date_,'dd/mm/yyyy'),[Datetime] = CDate(CStr([Date_]) & ' ' & Period & ':00')"
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,897
Try it with double quotes, not single.
Why does the field have an underscore?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,675
Office Version
2013
Platform
Windows
That's a weird formula. What is it supposed to do? Have you tested it? Can you give a sample input and output?
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,146
Hiya

i have a date and period column

what im trying to do is change/set the date column to dd/mm/yyyy and change the datetime column to combine the date and period column but treating it as datetime

ie

02/07/2020, 01:00
To
Date_, Period, DateTime
07/02/2020, 01:00, 07/02/2020 01:00
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
344
Office Version
365, 2016
Platform
Windows
I think you need to put them together first and then convert to a date...

Not tested: cDate([Date_] & ' ' & [Period])
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,675
Office Version
2013
Platform
Windows
02/07/2020, 01:00
is this data in one field or two fields? I'm not sure why you have a comma in there...

Also what is the data type of your input fields?
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,146
sorry Xenou - my bad, spotted where the issue was with the syntax - thank you all
 

Watch MrExcel Video

Forum statistics

Threads
1,102,565
Messages
5,487,592
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top