Error: Type mismatch. Number provided where a String is required

Hi all,

I am getting an error: Type mismatch. Number provided where a String is required when I am trying to perform the following:

[COL1] = Type is double

[COL2] = the new column this is creating, Type is V_WString

IF IsNull([COL1]) THEN Null() ELSEIF Length([COL1]) = 8 THEN LEFT([COL1], 4) + "-" + Substring([COL1], 5,2) + "-" RIGHT(COL1], 2)) ELSE DateTimeFormat(DateTimeAdd("1899-12-30", [COL1], "days"), "%Y-%m-%d") ENDIF

Thanks for your help!

Best Practices

Accepted answers

fmvizcaino

Hi JP02,

Right, left, length and substring function demand a text in the string parameter.

We can use the function tostring() to modify the [COL1] type, another way is to use a select tool before.

It should be like this.

IF IsNull([COL1]) THEN Null() ELSEIF Length(tostring([COL1])) = 8 THEN LEFT(tostring([COL1]), 4) + "-" + Substring(tostring([COL1]), 5,2) + "-" RIGHT(tostring(COL1]), 2)) ELSE DateTimeFormat(DateTimeAdd("1899-12-30", [COL1], "days"), "%Y-%m-%d") ENDIF

Best,

Fernando Vizcaino

All comments

fmvizcaino

Hi JP02,

Right, left, length and substring function demand a text in the string parameter.

We can use the function tostring() to modify the [COL1] type, another way is to use a select tool before.

It should be like this.

Best,

Fernando Vizcaino

JP02

Hi Fernando!

I am gong to try that right now! I kept staring at it, thinking there had to be a function that I could call to convert the number to a string. I tried the Select tool to convert earlier, but the DateTimeAdd seems to expect a number, and so I was hitting a wall. I will let you know what happens in a few minutes!

Thanks again.

JP02

Worked!! Thanks Much!

JokeFun

Hi, in my input file, the valueDate changes from time to time. In one report it is Double format while in another one it's V_String.

I used below formula and it still says "Number provided where a string is required."

if length(tostring([valuedate]))=8 then datetimediff(datetimeparse(LEFT(tostring([valueDate]),4)+SUBSTRING(tostring([valueDate]),5,2)+SUBSTRING(tostring([valueDate]),8,2) ,"%y%m%d"),[startDate],"days")+2
else tonumber([valueDate]) endif

where [startdate] is 1900-01-01

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167