Mission Impossible: Make table so Data Types Match

[justagrunt]

Hi,
Given that because of the way two tables grew, "a" (has an auto number field) and "b" (text feild that contains numbers) , certain data fields have a type mismatch (numeric and text) when trying to make a query using both initially.
With one , say "B" table I can run a query with a filter to isolate all numbers. This query is a make table. Physically I can change the properties of the made table to obtain a numeric data type where once the field was text.
Now in a query using the data from the two tables "A' and the made table of "b" I can extract information because the two fields concerned match data type wise and the query runs.
If the make table is run again, the information over writes, and field types revert to there original type of text.
Is there a way to retain datatype in the new make table where a field can be forced to be numeric?
Or is there a way to get two mismatched datatypes to join in a query? text and numeric?
Regards
Bill

 

[thecreator]

In Query by Design (QBD) mode, don;t link the tables by dragging a field over another .. rather do it by dragging one field to the "select" area (at the bottom of the QBD screen) and set the criteria to either

str(MyNumnericValue) to match a "dragged" text to a numeric

or

val(MyTextValue) to match a dragged "numeric" to a text value (this assumes that all your "text" values are in fact numbers.)

I haven't tested this but I am pretty sure it will work ... check the SQL that it produces.

Hope this helps

 

[justagrunt]

Cheers, will give it a go.
Kind Regards
bill