TEXT / NUM Compare Problem (CDbl) ???

[kev100 via AccessMonster.com]

I'm got an app with mulitple forms / cross-tab queries / regular queries...
etc.

It also links to mulitple tables (2-3) at a time.

All of these tables have a common value.......a Route Number.

This value is almost always stored as TEXT in the tables. And because it
often needs to be numerically sorted.....I will often use CDbl([RouteNumber])
in queries.

However...sometimes (and I have no control over this)...a given table/dataset
will come in in which the Route Number IS formatted as Numeric.

Well....when this happens....a given form query will break (incompatible data
types...etc)....where the route number is being used as a common value
between tables (e.g. 2 tables will be joined in a query using Route #...but
it is text in one and numeric in another).

When this happens...I have to search through misc queries and remove the CDbl
function for things to work...for that occasional exception.....then put it
BACK afterwards.

Is there a method for using CDbl (or some Equivalent Function)....that will
work in both cases (i.e. if the original value is formatted as text OR
numeric.....it will always be interpreted as Numeric?)

Thanks very much.
Kev100

 

[KARL DEWEY]

I do not know of one - maybe others.

Maybe try using it this way - In your queries have both, [RouteNumber] as
the orignal text and Route#: CDbl([RouteNumber]) as an alias for the numeric.

 

[John Spencer]

You can force both values to text with CStr().

It sounds to me as if the joins are breaking due to the data mismatch. CDbl
will work to convert a string to a double and it will convert a double to a
a double, so I'm not sure what is failing. Admittedly, CDbl will fail if it
gets a value that it can't convert - Null, non-number characters in a
string).

Can you post an example of a query that you have to change if the
RouteNumber is typed as a number?


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..