Changed Field type but query sees them different

[dianemarie]

Hello, I have a PurchaseOrder table and an Invoice table. The Invoice table looks up the PO number from the PO table. However, when I created the two tables, the PO_Num in the PO table is text, the PO_Num in the Invoice table is a number. (I know...my bad.) I went back after the fact and changed the PO_Num in the Invoice table to text, to match the PO_Num in the PO table, so I can link on those two fields in a query. However, the query does not read the two fields as being the same. All I did was change the number format to text in table design mode. Is there something else I need to do? Thank you for any help.

 

[Melagan]

First of all, before you start changing your table field definitions and such, back up your DB. After that, why not go the other way and convert both of your fields to numbers instead of text? They're numbers, right?

~Melagan
______
"It's never too late to become what you might have been.

 

[dianemarie]

Hi, thanks for getting back so quickly! Yes, I always backup. They are numbers, and I tried changing it the other way around but the query didn't like that either. I thought perhaps it was because the Invoice table is looking up the PO number. So either way, when I run a query linking on the PO_Num, it lets me do it, but returns no results. If I say "all records in PO Table and only those that match in Invoice table" then I get all PO records and vice versa when I change the link. So it appears to me that when I change the data type in either table, the data is not actually being converted and the query sees text and numbers and doesn't match them up. I'm wondering if I'm doing something wrong converting the data?