Reformatting Data Type

OK, i'm getting a type mismatch. I've tried searching for that and read a lot of posts, but none hit the nail on the head.

I know where it is. I am trying to link two table by using a field which is a string in one to link to a field which is a number of some kind in the other. I can;t change the formats of these as it's a query i'm running on a database i don't control. I understand I need to convert the format of one of the fields in a query and then link using that, but when i try to reformat the number it doesn't do what i'm expecting after reading the help.

in the help it suggest using this:
Format(expression[, format[, firstdayofweek[, firstweekofyear]]])

So i'm trying to use that to reformat the string field to a number so i can use that query.

Numbers: Use predefined named numeric formats or create user-defined numeric formats: This is where i fail completely!! it just rejects everything i write in my query.