Format issue code without office 365

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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')"
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

ranman256

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

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hiya - its the way the date field was saved originally
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
478
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I think you need to put them together first and then convert to a date...

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

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. 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,245
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
sorry Xenou - my bad, spotted where the issue was with the syntax - thank you all
 

Forum statistics

Threads
1,136,277
Messages
5,674,799
Members
419,526
Latest member
ranjit446

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top