Inserting a space into a string

[Andy C Matthews]

Hi there,
I'm building an Access database and using VBA to generate Microsoft Word
mailings for customers. It's all going fine so far. However, a variables
named ParcelID, a ten-digit string such as TQ03409954, needs to be split into
SHEET ID (the first six characters) and PARCEL ID (the last 4) e.g. TQ0340
9954. Can anyone help me? I'm messing around using For loops and the Split()
function, but to no avail.
Many thanks,
Andy
Reading, UK

[Rob Meade]

Re: Inserting a space into a string

"Andy C Matthews" wrote ...

a ten-digit string such as TQ03409954, needs to be split into
SHEET ID (the first six characters) and PARCEL ID (the last 4) e.g. TQ0340
9954. Can anyone help me? I'm messing around using For loops and the
Split()
function, but to no avail.

Dim CombinedID As String
Dim SheetID As String
Dim ParchelID As String

CombinedID = "TQ03409954 "

SheetID = Left(CombinedID , 6)
ParcelID = Right(CombinedI D, 4)


Hope that helps..

Regards

Rob

[Rob Meade]

Re: Inserting a space into a string

I should have added..

Dim NewCombinedID As String

NewCombinedID = SheetID & " " & ParcelID

My apologies..

Rob

[Andy C Matthews]

Re: Inserting a space into a string

Thanks Rob, that's really helpful.
This is the function I've come up with using it:


Public Function splitpid(Combin edID As String)

Dim SheetID As String
Dim ParcelID As String

SheetID = Left(CombinedID , 6)
ParcelID = Right(CombinedI D, 4)

End Function


How can I pass the SheetID and ParcelID back into each stage of the
following loop: (snippet of code follows)

'Filling objWord object with data
With objWord
.Visible = True
.Documents.Open (strDocPath)

'Inserting SBI
.ActiveDocument .Bookmarks("bmS BI").Select
If IsNull(Forms!fr mMainData!SBI) Then SBIno = "" Else: SBIno =
(CStr(Forms!frm MainData!SBI))
.Selection.Text = SBIno

'Navigate to first record (field)
Forms!frmMainDa ta!subFields.Se tFocus
DoCmd.GoToRecor d , , acFirst


'Jump to each bookmark in the word doc and insert corresponding
DB field data
'Checks to make sure fields aren't Null before passing data to
Word document

'Navigate to 1st record (field)
.ActiveDocument .Bookmarks("bmO ldPID").Select
If IsNull(Forms!fr mMainData!subFi elds!NewParcelI D) Then
OldPID1 = "" Else: OldPID1 = (CStr(Forms!frm MainData!subFie lds!NewParcelID ))
OldPID1 = OldPID1 & "Hello"
.Selection.Text = OldPID1

.ActiveDocument .Bookmarks("bmN ewPID").Select
If IsNull(Forms!fr mMainData!subFi elds!OldParcelI D) Then
NewPID1 = "" Else: NewPID1 = (CStr(Forms!frm MainData!subFie lds!OldParcelID ))
.Selection.Text = NewPID1

.ActiveDocument .Bookmarks("bmF ieldSize").Sele ct
If IsNull(Forms!fr mMainData!subFi elds!FieldSize) Then
FieldSize1 = "" Else: FieldSize1 =
(CStr(Forms!frm MainData!subFie lds!FieldSize))
.Selection.Text = FieldSize1

'Navigate to 2nd record (field)
Forms!frmMainDa ta!subFields.Se tFocus
DoCmd.GoToRecor d , , acNext

.ActiveDocument .Bookmarks("bmO ldPID2").Select
If IsNull(Forms!fr mMainData!subFi elds!New

"Rob Meade" wrote:

"Andy C Matthews" wrote ...
>

a ten-digit string such as TQ03409954, needs to be split into
SHEET ID (the first six characters) and PARCEL ID (the last 4) e.g. TQ0340
9954. Can anyone help me? I'm messing around using For loops and the
Split()
function, but to no avail.

>
Dim CombinedID As String
Dim SheetID As String
Dim ParchelID As String
>
CombinedID = "TQ03409954 "
>
SheetID = Left(CombinedID , 6)
ParcelID = Right(CombinedI D, 4)
>
>
Hope that helps..
>
Regards
>
Rob
>
>
>
>

[Rob Meade]

Re: Inserting a space into a string

"Andy C Matthews" wrote ...

Thanks Rob, that's really helpful.

You're welcome.

How can I pass the SheetID and ParcelID back into each stage

Change your function a little...

Public Function SplitPID(ByVal PID As String, ByRef SheetID As String, ByRef
ParcelID As String)

sheetID = Left(pid, 6)
parcelID = Right(pid, 4)

End Function


The above function will populate 2 variables DIM'd in the class calling this
function (they need to be called sheetID and parcelID in this example)

It might go something like this...

Dim PID As String
Dim SheetID As String
Dim ParcelID As String

' I clear these here just to point out that you might want to do this as you
are going to be looping through stuff...
SheetID = ""
ParcelID = ""

' call the function to populate the variables
SplitPID(PID, SheetID, ParcelID)


All you need to do is place the variables being cleared code, and the
function call in your loop and then use the variables (SheetID and ParcelID)
appropriately in the rest of your code..

Hope this helps...

Rob

[Chris Dunaway]

Re: Inserting a space into a string

Rob Meade wrote:

Public Function SplitPID(ByVal PID As String, ByRef SheetID As String, ByRef
ParcelID As String)
>
sheetID = Left(pid, 6)
parcelID = Right(pid, 4)
>
End Function

Just a little nit picking: if your method is not going to return a
value, you should use a Sub instead of a Function.

[_AnonCoward]

Re: Inserting a space into a string


"Andy C Matthews" <AndyCMatthews@ discussions.mic rosoft.comwrote in message
news:52055E8A-9EF9-4162-AF40-8E73C098B4E4@mi crosoft.com...
:
: "Rob Meade" wrote:
:
: "Andy C Matthews" wrote ...
: >
: a ten-digit string such as TQ03409954, needs to be split into
: SHEET ID (the first six characters) and PARCEL ID (the last 4) e.g.
: TQ0340 9954. Can anyone help me? I'm messing around using For loops
: and the Split() function, but to no avail.
: >
: Dim CombinedID As String
: Dim SheetID As String
: Dim ParchelID As String
: >
: CombinedID = "TQ03409954 "
: >
: SheetID = Left(CombinedID , 6)
: ParcelID = Right(CombinedI D, 4)
: >
: >
: Hope that helps..
: >
: Regards
: >
: Rob
: >
: Thanks Rob, that's really helpful.
: This is the function I've come up with using it:
:
:
: Public Function splitpid(Combin edID As String)
:
: Dim SheetID As String
: Dim ParcelID As String
:
: SheetID = Left(CombinedID , 6)
: ParcelID = Right(CombinedI D, 4)
:
: End Function



Based on the title of your post, I gather you want string "SSSSSSPPPP " to be
converted into "SSSSSS PPPP". Correct? If so, you can the mondify the
function as shown here:


Public Function splitid(Combine dID As String) As String
splitID = Left(ComninedID , 6) & " " & Right(CombinedI D, 4)
End Function



: How can I pass the SheetID and ParcelID back into each stage of the
: following loop: (snippet of code follows)
:
:
: 'Filling objWord object with data
: With objWord
: .Visible = True
: .Documents.Open (strDocPath)
:
: 'Inserting SBI
: .ActiveDocument .Bookmarks("bmS BI").Select
: If IsNull(Forms!fr mMainData!SBI) Then SBIno = ""
: Else: SBIno =
: (CStr(Forms!frm MainData!SBI))
: .Selection.Text = SBIno
:
: 'Navigate to first record (field)
: Forms!frmMainDa ta!subFields.Se tFocus
: DoCmd.GoToRecor d , , acFirst
:
:
: 'Jump to each bookmark in the word doc and insert corresponding
: DB field data
: 'Checks to make sure fields aren't Null before passing data to
: Word document
:
: 'Navigate to 1st record (field)
: .ActiveDocument .Bookmarks("bmO ldPID").Select
: If IsNull(Forms!fr mMainData!subFi elds!NewParcelI D) Then
: OldPID1 = "" Else: OldPID1 =
: (CStr(Forms!frm MainData!subFie lds!NewParcelID ))
: OldPID1 = OldPID1 & "Hello"
: .Selection.Text = OldPID1
:
: .ActiveDocument .Bookmarks("bmN ewPID").Select
: If IsNull(Forms!fr mMainData!subFi elds!OldParcelI D) Then
: NewPID1 = "" Else: NewPID1 =
: (CStr(Forms!frm MainData!subFie lds!OldParcelID ))
: .Selection.Text = NewPID1
:
: .ActiveDocument .Bookmarks("bmF ieldSize").Sele ct
: If IsNull(Forms!fr mMainData!subFi elds!FieldSize) Then
: FieldSize1 = "" Else: FieldSize1 =
: (CStr(Forms!frm MainData!subFie lds!FieldSize))
: .Selection.Text = FieldSize1
:
: 'Navigate to 2nd record (field)
: Forms!frmMainDa ta!subFields.Se tFocus
: DoCmd.GoToRecor d , , acNext
:
: .ActiveDocument .Bookmarks("bmO ldPID2").Select
: If IsNull(Forms!fr mMainData!subFi elds!New
:


Where precisely in this code snippet do you want this conversion to occur?


Ralf

[Rob Meade]

Re: Inserting a space into a string

"Chris Dunaway" wrote ...

Just a little nit picking: if your method is not going to return a
value, you should use a Sub instead of a Function.

I thought about that as I was posting, but had a mental block and couldn't
remember if I could use the ByRef's with a sub or not etc...too hot hear to
open up Visual Studio and try it! Plus, my second reply to the OP's post
was aimed at returning a new combined string..

Either way - my bad...

Rob