CLng text to long number compare problem

[JimTheProgrammer]

This query fails NOT MATTER WHAT I put in the "Where" clause. ITEM_NO and Short # are both 15 position text fields. This gives me a "data type mismatch in criteria expression" error. If I put a "is null" I get the same thing. I have tried >0. Same thing.

SELECT CLng([ITEM_NO]) AS Item_no_zeros
FROM IMF, [SDIP-35]
WHERE (((CLng([ITEM_NO]))=CLng([SDIP-35]![Short #])));

Any thoughts?

 

[PaulF]

FROM IMF, [SDIP-35]

What is IMF ????


This causes a problem because there are no fields being called from it. If Item_No is in a table called IMF then you need something like this, otherwise, remove the IMF in the FROM statement

SELECT CLng([Imf].[ITEM_NO]) AS Item_no_zeros, IMF.ITEM_NO
FROM IMF, IMF AS IMF_1 INNER JOIN [SDIP-35] ON IMF_1.ITEM_NO = [SDIP-35].[Short #]
WHERE (((CLng([Imf].[ITEM_NO]))=CLng([SDIP-35]![Short #])));

 

[JimTheProgrammer]

Sorry. Now that I re-read it I've left out a lot. There are 2 tables - IMF and SDIP-35. IMF has a part number with leading zeros to fill it to 15 positions. The problem is SDIP-35's Short # field has no leading zeros so you can't do a join on them. I've tried many ways to strip the zeros and make them both numbers but the Access gods don't like me. I've solved the problem by doing a "make table" with out the zeros and then compare it to SDIP-35. But a one step like this SHOULD work.

Thanks in advance to any help on this.