I am running a query on Sales for a given period. We changed some of our part #s this year, because we changed the net weight of the product. However, I would still like to group them together. In order to do this, I want to change the old part # to the new part#. For example I want to change 222B24AB01 to 228B24AB01.
All that has changed is the thrid character.

I want to use an IIF expression like IIF(ItemNum Like 222*,,ItemNum), but I don't know what to put in the If True section to change the third character.

Any Ideas?

Thanks in advance.

Hi,
use:

=Replace([YourField], "2", "8", 3, 1)

HTH
Good luck

Do you want to change the part numbers permanantly? ... If YES then use an udate query with SQL text similar to ...

UPDATE tblMyPartNumbers SET PartNumber = Left(PartNumber, 2) & "8" & Mid(PartNumber,4) WHERE Left(PartNumber,3) = "222"

EDITS ADDED:

You could also use Freaks function instead of my concatenation.

Edited by: datAdrenaline on Thu Jan 19 13:40:03 EST 2006.

Thanks to both of you. I don't want to change them permantly. I am running a query from an ODBC datasourse (Microsoft Navision), so the Item numbers are hardwired in there. I just want to total them together for inventory planning purposes.

Thanks again.

Whoops. For some reason the Replace Function is removing the first 2 characters. It does replace the third character though.

Item: IIf([ItemNum] Like "222*",Replace([ItemNum],"2","8",3,1),[ItemNum])