Copy portion of a string

[vandel212]

Hi, I'm kind of new to VBA but I do have some programming experience. I'm not sure if it matters but I'm using Access 2007. What I am trying to do is to copy 4 characters of a cell and paste them into a different cell. I will need to do this 3 times for each cell and copy different sections of the string each time. Any help would be greatly appreciated, even if you could point me in the right direction. Thanks.

Vandel212.

 

[MSAccessRookie]

Hi, I'm kind of new to VBA but I do have some programming experience. I'm not sure if it matters but I'm using Access 2007. What I am trying to do is to copy 4 characters of a cell and paste them into a different cell. I will need to do this 3 times for each cell and copy different sections of the string each time. Any help would be greatly appreciated, even if you could point me in the right direction. Thanks.

Vandel212.

Check out the MS Access 2007 Help Page

http://office.microsoft.com/en-us/access/default.aspx

Seek information on the following commands

Left()
Right()
Mid()
Instr()
Len()

 

[Mike375]

You might want to post up an example of the data you want copy and the variations.

From your post it sounds like you might be trying to use an Access table in the same manner as Excel

 

[vandel212]

Well, what I need to do is take a set of demsions (ex 21 x 5 x 40 [Width x Depth x Height) and put them into three seperate columns of Width, Depth, and Height. I have almost 200 rows of these so it would take a very long time to do it manually. Probably about as long as it would take me to figure out how to code it, but this is a good learning experience.

Also how would I bring the string within the cell into the variable, I'm not sure how they address it in access, like excell would by calling it cell A1 or G6.

Thanks for all the help.

 

[Mike375]

Here is a start for you and as you will see MSAccessRookie's post above is about this.

Assume your 21 x 5 x 40 is in a field called [abc]

Make a query which will have [abc] and you then make three calculated fields in the query. To that you enter the new field name (call it what you like) and the new name followed by a colon and a formula, but without the = sign

For this exercise I will call the new fileds L, M and R for left, mid and right

L: Left([abc],2)
M: Mid([abc],6,1)
R: Right([abc],2)

That is on the basis that the 21 x 5 X 40 is the same all the time. For example if there is one for 21 X 15 X 40 then what I posted will get the 21 and 40 but only get 1 from the 15

If you examine how the functions work it will become apparent. The Left is taking the first two characters. The Mid is starting at character 6 and ten taking one character and the Right is taking two characters off the right. Remember that spaces are characters.

Unlike Excel the formulas refere to every row so it will automatically do the 200.

However, if you have entries that vary from the 21 X 5 X 40 then you will probably need to fiddle with Len, InStr and IIF

For example, InStr will find the location of the X. So

Postion: InStr([abc],"X") will fill a column (field) with numbers that represent the character count from left to right for the first X

You can then use the result of InStr in the fucntions like Left.

For example if you had 21 X 5 X 40 then

xyz: Left([abc],[Position]-1) Or Left([abc],InStr([abc],"X")-1) that is the same but comibining it in one

would get the 21 but with a trailing section. In other words if you dragged across the 21 you wouod drag past it into black. Kind of like hitting the enter key several times at the end of a Word doc.

Hope that gets you going OK

 

[raskew]

Hi -

Here's an example from the immediate (debug) window that works regardless of the size of the dimension, provided the "21 x 5 x 40" format is consistent:
NOTE: Don't use Width, Depth, Height as field/variable names as they are reserved words in Access and will cause you big problems:

Consider p as the original dimensions, x as Width, y as Depth and z as Height

p = "21 x 5 x 40"
? p
21 x 5 x 40
x = left(p, instr(p, " ")-1)
? x
21
p = trim(mid(p, instr(p, "x")+1))
? p
5 x 40
y = left(p, instr(p, " ")-1)
? y
5
p = trim(mid(p, instr(p, "x")+1))
? p
40
z = p
? z
40

HTH - Bob

 

[Mike375]

Neat Bob,

Here is a Left assuming same format and no matter how many numbers

GetLeft: Left([abc],Len(Left([abc],InStr([abc],"X")))-2)

There is no end to it

 

[Mike375]

The middle number, assuming same format and no matter how big the numbers

R1: Right([abc],(Len([abc])-Len(Left([abc],InStr([abc],"X"))))-1)

MidExp1: Mid(Right([abc],(Len([abc])-Len(Left([abc],InStr([abc],"X"))))-1),1,InStr([R1],"X")-1)

 

[vandel212]

So how do I get the numbers from the dimensions column to the height width and depth column. I guess I'm asking how do I "paste" them (I know I'm not pasting anything)?

ADDITION:

I'm getting this error ([Dimension] is highlighted):
-----------------------------------------
compile error:

External name not defined
-----------------------------------------
I think it may have something to do with this:

"Make a query which will have [abc] and you then make three calculated fields in the query. To that you enter the new field name (call it what you like) and the new name followed by a colon and a formula, but without the = sign"

but I do not know what to do.

Thanks

 

[Mike375]

So how do I get the numbers from the dimensions column to the height width and depth column. I guess I'm asking how do I "paste" them (I know I'm not pasting anything)?

The values coming from the functions are in fields.

In a case like this you might have fields in your table like

Dimensions......W..........D.......H

21 X 5 X 40....Null........Null.....Null

and you want it to be

Dimensions......W..........D.......H

21 X 5 X 40.....21.........5........40

Your query will add the calculated fields which are the results of the various functions.

As you can see it might need a couple of goes to get the result.

In other words one calculated field might give 21 X and then a calculated field based on that calculated field gets the 21. You then copy that column and paste it into the W column.

But as Bob sayd be wary of using field names such as Width etc. Won't matter here but can have problems later.

 

[vandel212]

So am I doing all this coding in the query window or am I doing this in the module window? becaue I don't really know how to make a query the way you discribed.

thank you and sorry for my ingnorance.

 

[scalextric59]

This should work:

y = 1
For x = 1 To Len(mystring)
If Mid(mystring, x, 1) = "x" Then
Debug.Print Mid(mystring, y, x - y)
y = x + 1
End If
Next
Debug.Print Mid(mystring, y, x - y)

 

[Mike375]

This one get the number on the right

RightExp: Mid([R1],InStr([R1],"X")+2,100)

and again using the resulst from [[R1]

and again assuming the same format that is a space between the numbers and the X.

 

[Mike375]

The attached will show you how it works.

Open the DB and then Query1.

Bobs is better but you get into fucntions and you are probably better to learn a bit about queries to start with.

In the field [abc] increae of decrease the numbers and then click somewhere it should do them.

 

[Mike375]

PS,

As long as there is at least one space between the number and the X it will work.

However, more spaces can mean a leading space in one of the results.

You fix that by enclosing with LTrim()

So you would change RightExp: Mid([R1],InStr([R1],"X")+2,100)

to

RightExp: LTrim(Mid([R1],InStr([R1],"X")+2,100))

 

[Mike375]

So am I doing all this coding in the query window or am I doing this in the module window? becaue I don't really know how to make a query the way you discribed.

thank you and sorry for my ingnorance.

Go to what I attached and look at Query2, it has all been done for you.

If you open the query in design view then you will see what calculated fields are about. The field abc is the field in the table.

 

[rapsr59]

I Used this code to parse the number for Width, Height, and Depth and place the results in three controls.

Private Sub cmdDoParsing_Click()
    Dim strWDH As String
    Dim T As Integer
    Dim intCharCollected As Integer
    Dim strChar As String
    Dim strNumber As String
 
   'txtSubjectDimensions ctl has the WDH values and other characters in between
    strWDH = Me.txtSubjectDimensions
    For T = 1 To Len(strWDH)
        strChar = Mid(strWDH, T, 1)
        If Asc(strChar) >= 48 And Asc(strChar) <= 57 Then 'Numbers
            strNumber = strNumber & strChar
            intCharCollected = intCharCollected + 1
        Else
            If Right(strNumber, 1) <> "/" Then 'Add a slant between number
                strNumber = strNumber & "/"
            End If
        End If
    Next T
 
    '----------------------------------------------------------------------------
    ' Example: strNumber will now be ww/ddd/h, ww/d/hhh, or whatever combination,
    ' but will have a slant between the three dimensions and all other characters
    ' will be removed
    '----------------------------------------------------------------------------
 
    'Now set the controls
    Me.txtWidth = Left(strNumber, InStr(strNumber, "/") - 1) 'Set width control to width
    T = Len(Left(strNumber, InStr(strNumber, "/"))) 'Locate the first /
    strNumber = Right(strNumber, Len(strNumber) - T) 'Remove width and first /
    Me.txtDepth = Left(strNumber, InStr(strNumber, "/") - 1) 'Set depth control to depth
    T = Len(Left(strNumber, InStr(strNumber, "/"))) 'Locate the second /
    strNumber = Right(strNumber, Len(strNumber) - T) 'Remove depth and second /
    Me.txtHeight = strNumber 'Set Height control
 
 
End Sub

This code will compute the Width, Depth, and Height no matter how many alpha character or spaces are in between the WDH values and the WDH Values can be any number or digits.

Hope this helps


Richard

 

[vandel212]

thank you all very much I got the results that I wanted. I appreciate your help very much. Once Mike375 showed me his mdb and I got to see what I was doing, everything fell into place. So here is how I did it:

DIMENSION|X1: InStr([Dimension],"x")|X2: InStr([X1]+1,[Dimension],"x")|Length: Len([dimension])|Width: Left([dimension],[X1]-1)|Depth: Mid([Dimension],[X1]+2,[X2]-[X1]-3)|Height: Right([dimension],[length]-([X2]+1))


So thank you all very much,
Vandel212

 

[Mike375]

I Used this code to parse the number for Width, Height, and Depth and place the results in three controls.

Private Sub cmdDoParsing_Click()
    Dim strWDH As String
    Dim T As Integer
    Dim intCharCollected As Integer
    Dim strChar As String
    Dim strNumber As String
 
   'txtSubjectDimensions ctl has the WDH values and other characters in between
    strWDH = Me.txtSubjectDimensions
    For T = 1 To Len(strWDH)
        strChar = Mid(strWDH, T, 1)
        If Asc(strChar) >= 48 And Asc(strChar) <= 57 Then 'Numbers
            strNumber = strNumber & strChar
            intCharCollected = intCharCollected + 1
        Else
            If Right(strNumber, 1) <> "/" Then 'Add a slant between number
                strNumber = strNumber & "/"
            End If
        End If
    Next T
 
    '----------------------------------------------------------------------------
    ' Example: strNumber will now be ww/ddd/h, ww/d/hhh, or whatever combination,
    ' but will have a slant between the three dimensions and all other characters
    ' will be removed
    '----------------------------------------------------------------------------
 
    'Now set the controls
    Me.txtWidth = Left(strNumber, InStr(strNumber, "/") - 1) 'Set width control to width
    T = Len(Left(strNumber, InStr(strNumber, "/"))) 'Locate the first /
    strNumber = Right(strNumber, Len(strNumber) - T) 'Remove width and first /
    Me.txtDepth = Left(strNumber, InStr(strNumber, "/") - 1) 'Set depth control to depth
    T = Len(Left(strNumber, InStr(strNumber, "/"))) 'Locate the second /
    strNumber = Right(strNumber, Len(strNumber) - T) 'Remove depth and second /
    Me.txtHeight = strNumber 'Set Height control
 
 
End Sub

This code will compute the Width, Depth, and Height no matter how many alpha character or spaces are in between the WDH values and the WDH Values can be any number or digits.

Hope this helps


Richard

Richard,

You cheated with Asc I tried yours on a form and all worked.

A bit rough but the 3 of them from 3 calculated fields with no reference to other calculated fields. Needs at least one space between number and X but does not matter how many spaces or how big the numbers

LeftNumber: Left([abc],Len(Left([abc],InStr([abc],"X")))-2)

MiddleNumber: LTrim(Mid((Right([abc],(Len([abc])-Len(Left([abc],InStr([abc],"X"))))-1)),1,InStr(((Right([abc],(Len([abc])-Len(Left([abc],InStr([abc],"X"))))-1))),"X")-1))

RightNumber: LTrim(Mid((Right([abc],(Len([abc])-Len(Left([abc],InStr([abc],"X"))))-1)),InStr((Right([abc],(Len([abc])-Len(Left([abc],InStr([abc],"X"))))-1)),"X")+2,100))

 

[Mike375]

Richard,

How about this and then calc query field below

Public Function XxNum(strOriginalString As String) As String
Dim lngCtr As Long
Dim lngLength As Long
Dim strTheCharacter As String
Dim intAscii As Integer
Dim strFixed As String
lngLength = Len(strOriginalString)
For lngCtr = 1 To lngLength
strTheCharacter = Mid(strOriginalString, lngCtr, 1)
intAscii = Asc(strTheCharacter)
If (intAscii >= 48 And intAscii <= 57) Or intAscii = 88 Or intAscii = 120 Then
strFixed = strFixed & strTheCharacter
End If
Next lngCtr
XxNum = strFixed
End Function

Then in calc query field

Replace((XxNum([abc])),"X"," ")

so

123 dsdasd X 456X rwerr 56

becomes 123 456 56

I am bored this afternoon in Sydney