Text/Number conversion

[drkestrel]

I need to run a query like the following

SELECT
field1.tab1,field2.tab1
fielda.tabA,fieldb.tabA
FROM
tab1,tabA
WHERE
field1=fielda AND
field2<>fieldb;

the problem is field 2 is numeric, whilst fieldb is Text

All records in fieldb contains either a numerical value (stored as text) or null, however some numbers are prepended with 0, so the following doesn't work.

SELECT
    field1.tab1,field2.tab1
    fielda.tabA,fieldb.tabA
FROM
    tab1,tabA
WHERE
    field1=fielda AND
    CStr(field2)<>fieldb;

Any ideas of how to convert fieldb to numeric?

 

[tlbroadbent]

Use CInt if Integer. Use CSng or CDbl if the filedb has a decimal.

SELECT
field1.tab1,field2.tab1
fielda.tabA,fieldb.tabA
FROM
tab1,tabA
WHERE
field1=fielda AND
field2<>CInt(fieldb);

Access HELP contains more information on these and other useful functions. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[MichaelRed]

The conversion would, perhaps, benefit from the inclusion of NZ as well:

field2<>CInt(Nz(fieldb));

although it may complicate the process further if Zero is a valid value for field2. If field2 is limited (and VALIDATED) to a range, the Nz could still be used by setting the Null replacement to any value not in the validated range of field2.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over