Format issue code without office 365

[mahmed1]

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')"

 

[ranman256]

Try it with double quotes, not single.
Why does the field have an underscore?

 

[mahmed1]

Hiya - its the way the date field was saved originally

 

[xenou]

That's a weird formula. What is it supposed to do? Have you tested it? Can you give a sample input and output?

 

[mahmed1]

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]

I think you need to put them together first and then convert to a date...

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

 

[xenou]

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]

sorry Xenou - my bad, spotted where the issue was with the syntax - thank you all