join 2 tables with different links of data

[Guest]

Hello all,

I have 2 tables that I want to do a join query on. Both fields in the table
are text, but with numeric data. On one table the data has 2 leading zeros
and on the other this is omitted.

What is the best way to join these two fields? Is it with a like statement?

Here what I have been trying, but getting errors..

vSql = "select * from tbImport"

Set db = CurrentDb()
Set rs = db.OpenRecordset(vSql)

Do While Not rs.EOF

vSql1 = "Select sku,sku_type, sku_desc from viaware_pm_f where sku_type =
""NORM"" and sku like rs!PartNum"

Set db = CurrentDb()
Set rs = db.OpenRecordset(vSql1)

I get error that say’s “To few parameter’s. Expect 1†this is right after
the last open recordset. Am I going about it the right way, and if so what
is wrong with this code?

 

[Guest]

There may be other ways but here is one.
Create a query for the table missing the leading zeros and add this field --
X:"00"&[YourTextField]

Now this query can be joined with the table that has the leading zeros.

 

[Douglas J. Steele]

Try:

vSql1 = "Select sku,sku_type, sku_desc from viaware_pm_f where sku_type =
""NORM"" and sku like *""" & rs!PartNum & """"

Better still, though, would be to convert your second value so that it was
the leading zeros.

If it's always 2 leading zeros, it would be something like


vSql1 = "Select sku,sku_type, sku_desc from viaware_pm_f where sku_type =
""NORM"" and sku = ""00" & rs!PartNum & """"

If it's a varying number of zeros (to a fixed width), try something like:

vSql1 = "Select sku,sku_type, sku_desc from viaware_pm_f where sku_type =
""NORM"" and sku = """ Right$("000000" & rs!PartNum, 6) & """"

That will ensure it's always 6 characters wide. Increase the number of 0s
and the digit 6 if you need something else.