work around for the mis-type error? text = numeric

[techexpressinc]

I have two tables, I need to link up.
One table has the field define as text
and another has the field define as numeric.

I can really change the definition of the fields. Reason being they are dumped there monthly in SQL format and we link up to them in Access.

I think there is a numeric conversion function for Access, but of course it is not obvious. The text field always has numbers in it anyway. So, I was thinking of expression "family-id-numeric: value(table1.family-id-text)".

Does this sound possible.
Russ

 

[pbaldy]

This type of thing should work:

FROM Table1 INNER JOIN Table2 ON Table1.FieldName = Val(Table2.FieldName)

or

FROM Table1 INNER JOIN Table2 ON Table1.FieldName = CLng(Table2.FieldName)

 

[techexpressinc]

I do not want to get into the actual SQL code. I much rather do it through the panels.

I have tried both function val and clng without success and still get that nasty message:

"Data type mismatch.."

Attached is a screen print.

Russ

 

[DCrake]

The easiest way to do this is to take either of the tables in question and add all the fields to a new query. The foeld that you want to join to will need to be formatted accordingly

either

TextToNumber:Val(TextField)
Or
NumbertoText:CStr(NumberField)

Then create the original query but use the new query istead of the table and join accordingly.

 

[techexpressinc]

You were Right On.

My problem was in another part of the query.

I have checking for a date field not be nulls.

I had it coded like > "nulls".

That was causing the error message of data mismatch. Once I word it right all worked good.

Is NullIs Not Null

Thx for direction and help.
Russ