Format issue code without office 365

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,241
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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

ranman256

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

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,241
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,241
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
469
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,241
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
sorry Xenou - my bad, spotted where the issue was with the syntax - thank you all
 

Watch MrExcel Video

Forum statistics

Threads
1,133,429
Messages
5,658,740
Members
418,467
Latest member
sc356448

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