Matching Invoice Number

[CAM]

Hello,

I have a table called "Vertax", which has a field called "Invoice" it is
text field with field size of 12. I have another table called "Sales",
which has a field call "InvNumber", which is a text field and has a field
size of 7. I want to pick up all the records from the table called "Vertax"
matching the field call "Invoice" to the table "Sales" field call
"InvNumber". Now I don't know if this is the problem, but I the field call
"Invoice" really has 7 digits not 12 although there are records that has 12
digits and I have to account for that, but all I want is the table "Vertax"
field "Invoice" first 7 digits to match with the 7 digits from "InvNumber"
from the "Sales" table. How do I code that. Any tips will be appreciated.
Thank you in advance.

Cheers

 

[John W. Vinson]

Hello,

I have a table called "Vertax", which has a field called "Invoice" it is
text field with field size of 12. I have another table called "Sales",
which has a field call "InvNumber", which is a text field and has a field
size of 7. I want to pick up all the records from the table called "Vertax"
matching the field call "Invoice" to the table "Sales" field call
"InvNumber". Now I don't know if this is the problem, but I the field call
"Invoice" really has 7 digits not 12 although there are records that has 12
digits and I have to account for that, but all I want is the table "Vertax"
field "Invoice" first 7 digits to match with the 7 digits from "InvNumber"
from the "Sales" table. How do I code that. Any tips will be appreciated.
Thank you in advance.

Cheers

Well... your best design would be to change the structure of Vertax so that
the invoice field is split into two fields. Since this InvNumber 7-byte field
has a meaning of its own, it should ideally be in a separate field; you could
for example have InvNumber and InvSuffix. They can be concatenated in a query
for display purposes, and joined directly.

Failing that, you can use a non-equi join. Create a Query joining Vertax to
Sales, joining Invoice to InvNumber (there will of course be no matches). Open
the query in SQL view (you can't do this in the grid) and change the JOIN
clause from

ON Vertax.Invoice = Sales.InvNumber

to

ON Vertax.Invoice LIKE Sales.InvNumber & "*"

to do a partial match. The query will almost certainly not be updateable.