CAST conversion error: Syntax Error missing operator

[Azzna]

Hello,

I am importing data from an outside source into my database. Before we
can work with the data, I have to do a series of updates to said data
and then insert the data into an existing table. So, I have this
"import" table I work with to manipulate the data before inserting. As
I do my updates to manipulate the table, Desc15 which starts as a
combination of text and numeric ends up with values of only numeric. I
don't want to change the data type of the field during this conversion.


I am trying to use the CAST option to evaluate the numbers there as
numbers, but I am getting a syntax error missing operator on it and I
can't seem to find a reason why. If you all could take a look and let
me know I would appreciate it. I am use to Oracle SQL coding, and
there are just enough differences to mess me up sometimes.

Here is my code:

UPDATE Import SET Import.ArtGrp = IIf(CAST(Desc15 AS
NUMBER)<201,Desc15, IIf(CAST(Desc15 AS
NUMBER)<1000,Right(Desc15,2),IIf(CAST(Desc15 AS
NUMBER)>999,Right(Desc15,3),Null)));

 

[George Nicholson]

What engine is executing your SQL?

AFAIK, neither Access, VBA or Jet SQL will recognize CAST (Access 2003), but
it should work if you are passing SQL to an engine that does.

That said, I would have expected a stronger error message, so I'm sure
someone will correct me if I'm wrong.

HTH,

 

[Azzna]

I am passing it back to the access database engine. I didn't change
any of those settings. If I can't use cast, is there another way to
temporarly compare a string as if it were a number?

Such as iif (String1 > 1000) then....

I have tried looking it up, but none of my books seem to have it, nor
can I seem to find it on the web. I hate to change the actual value of
the field because the data that comes into that table regularly starts
out with characters in it, and we have update queries that remove said
characters to bring it to a numeric field again. Any thoughts?

 

[Douglas J. Steele]

IIf(Val(String1) > 1000, ...)

 

[Azzna]

You rock! That fixed it. I have spent hours trying to find a way to
do this and the val did the trick. Thank you so much!