combining values from 3 fields in one new field - removing spaces

[wilkob]

Hello,

I have a huge database (with 12000 lines) in which I would like to combine the values from 3 columns (fields) into a new column (field)

Example:

Group:
1234RP

Size:
100 100

Grade:
RPF

Should become:

ArticleCode
1234RP100100PRF

Is there a way to do this via a maketable query ?

 

[MSAccessRookie]

You should be able to base your maketable query on the query below, which allows you to view all of the fields as a single reference column:

SELECT (Group & Size & Grade) AS { YourNewFieldName } from { YourTableName };

Of course this WILL NOT remove the space(s) contained in the grade column, so you need an additional step to do that

 

[redneckgeek]

Select [group] & Replace([size]," ","") & [grade] As Newfieldname

btw: 12,000 records is not huge, unless it's 255 columns wide

 

[MSAccessRookie]

Thanks redneck, for some reason, I could not remember the replace command when I gave my initial reply.

 

[wilkob]

shows you what a newbie I am if I think 12000 is a huge number

I have tried to create a expression with the formula but it does not even like the select option??

Am I doing something wrong?

Using Access 2007

 

[MSAccessRookie]

If you are trying to add it to a form, don't forget to put an "=" in front of the query

=Select [group] & Replace([size]," ","") & [grade] As Newfieldname

 

[wilkob]

GOT IT !!!!!!!

Messed about a bit more and just used the

[group] & Replace([size]," ","") & [grade]

and it did the trick

Thanks

 

[MSAccessRookie]

Always happy to help out a fellow traveler in the wonderful Land of MS Access

 

[wilkob]

I have got another one very tricky issue (I am building it up in difficulty )

I have two field in my table in which there is a row of text like this:

QCV=<OK>/QCD=<OK>/HB=<196>/QCH=<OK>/Rm=<625>/Rp0.2=<286>

I would like to get rid of all the QCV=<OK>/QCD=<OK> QCH=<OK>/and then move the value behind the HB in a column HB, the value for the Rm into a column Rm and the value Rp0.2 into a column Rp0.2

What do you guys think?? Can this be done via Access?

The only thing my little brain can think of is exporting the table into excel and going through the cumbersom job of filtering and moving it manually

Please HELP !!!

 

[WayneRyan]

Wilkob,

This will do it:

Update YourTable
Set    HB = IIf(Instr(1, [Rm], "HB=") > 0,                                             <-- If there's an "HB=" present ...
                Mid([Rm],                                                              <--    Then return a segment of [Rm]
                Instr(1, [Rm], "HB=") + 4,                                             <--    Starting at the "HB=" + 4
                Instr(Instr(1, [Rm], "HB="), [Rm], ">") - Instr(1, [Rm], "HB=") - 4),  <--    With this length 
                "None")                                                                <-- If there's no "HB="

But, I'd hate to fix that on a Monday morning!

I'd just write a little Public VBA function:

Update YourTable
Set    HB = fnGetHB([Rm])


Public Function fnGetHB(Rm As String) As String
Dim Temp As String
Dim FrontPtr As Integer
Dim BackPtr As Integer

If Instr(1, Rm, "HB=") > 0 Then
   FrontPtr = Instr(1, Rm, "HB=")
   BackPtr = Instr(FrontPtr, Rm, ">")
   fnGetHb = Mid(Rm, FrontPtr + 4, BackPtr - FrontPtr - 4)
Else
   fnGetHB = "None"
End If
End Function

Wayne

 

[wilkob]

Oh my, I am way out of my league.....

This is NASA math to me.

I have copied and pasted the code in a new query design(in a SQL layout) and have changed the name of MyTable to the table that holds the source data.

Is this what I should have done because I am getting errormessages

 

[WayneRyan]

Wilkbob,

I just typed that off the top of my head, let me put it into a database
and give it a try ...

Wayne

 

[WayneRyan]

Wilkob,

I can't believe I got the syntax right ... I think you pasted in my comments.

Just change YourTable to your table name:

Update YourTable
Set HB =  IIf(InStr(1,[Rm],"HB=")>0,Mid([Rm],InStr(1,[Rm],"HB=")+4,InStr(InStr(1,[Rm],"HB="),[Rm],">")-InStr(1,[Rm],"HB=")-4),"None");

hth,
Wayne

 

[redneckgeek]

Don't forget to build IIF statements for the other 2 columns you need!

UPDATE YOURTABLE
SET HB=IIF...
, RM=IIF...
,RP0.2=IIF...

 

[wilkob]

I thought I understood what I had to do
but then things went bad

1.) I added the two columns HB and Rm to my table
2.) I created an update query and copy/pasted the code from WayneRyan into the SQL text
3.) I ran the update query and presto : it says None in the column HB

anyway I am happy that something happened - a small step forward, but cannot figure out why there is no value in the column.

I have attached a database extract for you to look at

P.S. I promise that after this all I will go on an Access training course

 

[wilkob]

I am a very happy man.

Couldn't let it go and struggled along

Understand now the code, where to put it and have solved my problem:

UPDATE TagsTraceDatabase
SET HB = IIf(InStr(1,[Spec],"HB=")>0,Mid([Spec],InStr(1,[Spec],"HB=")+4,InStr(InStr(1,[Spec],"HB="),[Spec],">")-InStr(1,[Spec],"HB=")-4),"none"), Rm = IIf(InStr(1,[Spec],"Rm=")>0,Mid([Spec],InStr(1,[Spec],"Rm=")+4,InStr(InStr(1,[Spec],"Rm="),[Spec],">")-InStr(1,[Spec],"Rm=")-4),"none"),
Rp02 = IIf(InStr(1,[Spec],"Rp0.2=")>0,Mid([Spec],InStr(1,[Spec],"Rp0.2=")+7,InStr(InStr(1,[Spec],"Rp0.2="),[Spec],">")-InStr(1,[Spec],"Rp0.2=")-7),"none");


nice !!!

Thanks everybody for your support

 

[wilkob]

one last one and then I promise that I'll stop

I ran my update query and have two problems:

1.) I get a message that 144 records could not be updated because of an error of the type conversion ?
Where can I find an error logs which list some details on these 144 records?

2.) for some of the fields there is a range of values

QCD=<OK>/QCV=<OK>/HB=<215>/QCH=<OK>/IACS=44.88-46.92THICK=<25>/WIDTH=<50>/LG=<665>

and then the values are not between brackets. The results is that the values in the columns are all screwed up. I found that when I manually put the value between brackets the results in the columns are ok.

Any idea how I can fix this?