making join between numeric field and text field

[wilkob]

I would like to make a join between two field in 2 different tables:

1.) Table with number field for Purchase Order number
2.) Table with text field for customers PO ref (where normally above PO number is entered)

When I link the both I get an error/warning message that the "types in the expression don't match"

Can anyone help me to overcome this problem ??

Thanks

 

[Dreamweaver]

They both have to be of the same data type you will need to change one or the others data type so both match.

mick

 

[RoyVidar]

Probably does wonders for performance and efficiency

In your join

...
FROM Table1 a INNER JOIN TableB b ON 
    a.ID = Val(b.ID)

assuming TableB is the one with number as Text. If Nulls etc is a problem, you'd probably need to play with NZ too.

 

[Moniker]

"playing with Nz" (the null zero function) would look like this (assuming you want that):

a.ID = Nz(Val(b.ID),0)

That would convert any NULL value to a 0. You can convert a NULL to anything this way. The format for Nz is this:

Nz([Value],[Value If Null])

Value - The variable you want to check to see if it's null.

Value If Null - What to assign to the variable if [Value] returns a null value.

~Moniker