Move a selected piece of data in a table

[Santude]

:banghead:I am trying to separate a random model # and move it to another column; example:

AHUCond1
CHP1232CD6 MBV2000AA-1A
CAP5454BD6
CAP4848DCD6
CHP6060BD6 GMV2451CDC6


The End Result should be:

AHUCond1 BlowerMod
CHP1232CD6 MBV2000AA-1A
CAP5454BD6
CAP4848DCD6
CHP6060BD6 GMV2451CDC6


I was thinking about using the Split () Function or Selct Case, I am new working with Modules and VBA, but I was hoping that someone could give me an example?

Thanks in advance for your help!!

 

[pr2-eugin]

Where will you get the Random Model # value from? How do you wish to manipulate the data? is it by using a bound Form??

 

[plog]

First you have to identify a pattern. From what you have given me, it looks like everything after the first space in AHUCond1 should be moved into BlowerMod. Is that true in all cases?

If so, I would do this in an UPDATE query, not a macro. First I would create a SELECT query and make a custom field that displays what should be in BlowerMod to make sure I get it right. Then after I have crafted that custom field correctly, I would convert that SELECT query into an update query and update the BlowerMod field to whats inside that calculated field.

If my analysis is correct and everything after the first space in AHUCond1 should go into BlowerMod, you will need to use the MID function (http://www.techonthenet.com/access/functions/string/mid.php) and the InStr function (http://www.techonthenet.com/access/functions/string/instr.php) in that calculated field.

 

[Santude]

pr2-ugin,

The model# will always be part of the AHUCond1 column. The idoits that put the table together just through all the Air Handler Model (AHU) #'s together to save time. I need to seperate the fields so that I can tie pricing back to each model #. Please note that not all AHU's have two pieces and are not the same model #, as I had tried to show in the above example. If I can copy only the AHU that have 2-pieces tied to it into a new column I would be golden....THANKS PR2!!!