Access and SQL

[Jay-Aim]

I have a text field with various data, some text, some text as numbers

I can't change that field to a numeric field due to loss of data

Writing an SQL Querry, I want to select data by having the potential value of that field over a given number (let's say 100)

What is the command in SQL to convert from text to value?

>>> Edited by Jay-Aim on Friday 3rd December 10:57

[pdV6]

CAST([fieldname] as <type> )

N.B. Smells of iffy database design if you even need to think about this kind of thing!

>> Edited by pdV6 on Friday 3rd December 11:03

[Jay-Aim]

pdV6 said:

CAST([fieldname] as <type> ) N.B. Smells of iffy database design if you even need to think about this kind of thing! >> Edited by pdV6 on Friday 3rd December 11:03

Ta and yes I agree

unfortunately I'm left with having to deal with it as it is and as it is from a running program, I can't modify it.

>> Edited by Jay-Aim on Friday 3rd December 11:38

[Jay-Aim]

nope

I need to only convert the value to a number not the filed type, the equivalent of 'ToNumber(x)' of Crystal Report

[pdV6]

CAST is what you want, but I've just twigged that you're working in Access, not SQL Server!

Not sure if Access has an equivalent. Sorry.

[JoolzB]

Try CInt() or one of the Cxxx type functions.

[Don]

You'll need to open a recordset with the field in test to see if the value is numeric with IsNumeric() and then CDbl() or CInt() it. IIRC.

[catretriever]

the expression to use in the query builder, or the raw SQL is along the lines of...

IIf(IsNumeric([fieldname]),CDbl([fieldname])/100,"not a number")