Help in Concatenating Cell Contents. 2

[pdldavis]

Hi, I've got have a series of Cost Codes in field Ccode. These are Text. They currently look like this:

Ccode

3906.
01
02
3907.
01
02

Which needs to look like this:
3906.01
3906.02
3907.01
3907.02

I beleive I need to do some sort of looping function, concatenate the strings and then delete the original five character string. The string lenghth will either be two characters or five characters in all cases.

Any help with this would be appreciated.

Thanks

 

[JeremyNYC]

This all depends on whether or not there is some field that can be used to keep these records in order, as rows in a table are not stored in any particular order. If you have no such field, but you are _certain_ the rows are in the order you want, just add an autonumber field to the table. I'll assume for now that you've got this table scheme:

tblWhatever
===========
WhateverID (autonum)
CostCode

Make a sub that looks like this:
sub

SCREEEECH. Wait. Whoa. This is all wrong. I must be a little spacey today. Here's the thing. These data items should be stored in separate fields. There's no way that it will make sense to have both the four digit and two digit portions of this in the same field. They represent different bits of data, so they should be in different fields. If you don't like the sound of that, check out the Fundamentals article on my website for tips on why you'll want to normalize these data. Then, for displaying and printing, you'll be able to do this
format([CCSuper], "0000&quot & "." & format([CCSub], "00&quot

That said, you'll still probably need some code to get the right values in the right field, so here's how I'd go about it. First, make your table like this:

tblWhatever
===========
WhateverID (autonum)
CCSuper (numeric)
CCSub (numeric)
CostCode (text = your original field)

(just changed the names so there's less typing for me)
Sub Whatever()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Dim lngSuper As Long
Dim lngSub As Long

Set db = CurrentDb
strSql = "SELECT * FROM tblWhatever ORDER BY WhateverID"
Set rst = db.OpenRecordset(strSql, dbOpenDynaset)

lngSuper = CLng(rst("CostCode&quot)
Do Until rst.EOF
lngSub = rst("CostCode&quot
If Right(rst("CostCode&quot, 1) = "." Then
lngSuper = CLng(rst("CostCode&quot)
lngSub = 0
End If
rst.Edit
rst("CCSuper&quot = lngSuper
rst("CCSub&quot = lngSub
rst.Update
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

End Sub

Hope this helps.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[pdldavis]

Hi, thank for the help. I hadn't thought of the autonumber suggestion either. I'll try to get back to it later on today. This information was imported from a a very poorly designed Excel Spreadsheet and I'd managed to get the data cleaned up to this point.

That is why the data is not stored in separate fields. I wish it was and I wouldn't have to be asking how to do this but then again, I wouldn't be learning anything new.

Thanks, Dan

 

[JoyInOK]

Jeremy, you get a star for cracking me up, while also being helpful.

 

[pdldavis]

Hi,

It was very easy to incorporate the code and it works but there is one problem. The five character field LngSuper
repeats itself all the way down the column so instead of:

lngSuper lngSub
3900 01
3900 02
3903 01
3903 02

It comes out as:

lngSuper lngSub
3900 01
3900 02
3900 01
3900 02


The code:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Dim lngSuper As Long 'Five Character Field
Dim lngSub As Long 'Two Character Field

Set db = CurrentDb
strSql = "SELECT * FROM tbltest ORDER BY ID"
Set rst = db.OpenRecordset(strSql, dbOpenDynaset)

lngSuper = CLng(rst("CostCode&quot)
Do Until rst.EOF
lngSub = rst("CostCode&quot
If Right(rst("CostCode&quot, 1) = "." Then
lngSuper = CLng(rst("CostCode&quot)
lngSub = 0
End If
rst.Edit
rst("CCSuper&quot = lngSuper
rst("CCSub&quot = lngSub
rst.Update
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

Would someone assist me in correcting this?

Thanks, Dan

 

[JeremyNYC]

Dan,

Hmm. Maybe I misunderstood your table, because I just tested your code (which was a lot like mine <g>) and it worked fine on the table I was testing.

Here's what I have in the table

WhateverID	CCSuper	CCSub	CostCode
1               0       0       3243.
2               0       0       01
3               0       0       94
4               0       0       9342.
5               0       0       93
6               0       0       39
7               0       0       23

Does that match what you start with? When I'm done I've got this:
WhateverID	CCSuper	CCSub	CostCode
1               3243    0       3243.
2               3243    1       01
3               3243    94      94
4               9342    0       9342.
5               9342    93      93
6               9342    39      39
7               9342    23      23

Does that match what you're looking for? (One thing I didn't mention is that you might want to delete the records where ccsub is zero, depending on what you're doing--shouldn't be that difficult.)

In any case, if I modeled your table wrong, show me what you've got and I'll try again. But the code you posted did work for the model I made.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[pdldavis]

Hi Jeremy,

My mistake. I omitted the '.' at the end of the cost code. The code that looks suspiciously like yours [<G>] now works like a charm.

Thanks very much,

Dan