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]

"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(CombinedID, 4)
Hope that helps..

Regards

Rob

[Rob Meade]

I should have added..

Dim NewCombinedID As String

NewCombinedID = SheetID & " " & ParcelID

My apologies..

Rob

[Andy C Matthews]

Thanks Rob, that's really helpful.
This is the function I've come up with using it:
Public Function splitpid(CombinedID As String)

Dim SheetID As String
Dim ParcelID As String

SheetID = Left(CombinedID, 6)
ParcelID = Right(CombinedID, 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("bmSBI").Select
If IsNull(Forms!frmMainData!SBI) Then SBIno = "" Else: SBIno =
(CStr(Forms!frmMainData!SBI))
.Selection.Text = SBIno

'Navigate to first record (field)
Forms!frmMainData!subFields.SetFocus
DoCmd.GoToRecord , , 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("bmOldPID").Select
If IsNull(Forms!frmMainData!subFields!NewParcelID) Then
OldPID1 = "" Else: OldPID1 = (CStr(Forms!frmMainData!subFields!NewParcelID))
OldPID1 = OldPID1 & "Hello"
.Selection.Text = OldPID1

.ActiveDocument.Bookmarks("bmNewPID").Select
If IsNull(Forms!frmMainData!subFields!OldParcelID) Then
NewPID1 = "" Else: NewPID1 = (CStr(Forms!frmMainData!subFields!OldParcelID))
.Selection.Text = NewPID1

.ActiveDocument.Bookmarks("bmFieldSize").Select
If IsNull(Forms!frmMainData!subFields!FieldSize) Then
FieldSize1 = "" Else: FieldSize1 =
(CStr(Forms!frmMainData!subFields!FieldSize))
.Selection.Text = FieldSize1

'Navigate to 2nd record (field)
Forms!frmMainData!subFields.SetFocus
DoCmd.GoToRecord , , acNext

.ActiveDocument.Bookmarks("bmOldPID2").Select
If IsNull(Forms!frmMainData!subFields!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(CombinedID, 4)
Hope that helps..

Regards

Rob

[Rob Meade]

"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]

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]

"Andy C Matthews" <An***********@discussions.microsoft.comwrote in message
news:52**********************************@microsof t.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(CombinedID, 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(CombinedID As String)
:
: Dim SheetID As String
: Dim ParcelID As String
:
: SheetID = Left(CombinedID, 6)
: ParcelID = Right(CombinedID, 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(CombinedID As String) As String
splitID = Left(ComninedID, 6) & " " & Right(CombinedID, 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("bmSBI").Select
: If IsNull(Forms!frmMainData!SBI) Then SBIno = ""
: Else: SBIno =
: (CStr(Forms!frmMainData!SBI))
: .Selection.Text = SBIno
:
: 'Navigate to first record (field)
: Forms!frmMainData!subFields.SetFocus
: DoCmd.GoToRecord , , 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("bmOldPID").Select
: If IsNull(Forms!frmMainData!subFields!NewParcelID) Then
: OldPID1 = "" Else: OldPID1 =
: (CStr(Forms!frmMainData!subFields!NewParcelID))
: OldPID1 = OldPID1 & "Hello"
: .Selection.Text = OldPID1
:
: .ActiveDocument.Bookmarks("bmNewPID").Select
: If IsNull(Forms!frmMainData!subFields!OldParcelID) Then
: NewPID1 = "" Else: NewPID1 =
: (CStr(Forms!frmMainData!subFields!OldParcelID))
: .Selection.Text = NewPID1
:
: .ActiveDocument.Bookmarks("bmFieldSize").Select
: If IsNull(Forms!frmMainData!subFields!FieldSize) Then
: FieldSize1 = "" Else: FieldSize1 =
: (CStr(Forms!frmMainData!subFields!FieldSize))
: .Selection.Text = FieldSize1
:
: 'Navigate to 2nd record (field)
: Forms!frmMainData!subFields.SetFocus
: DoCmd.GoToRecord , , acNext
:
: .ActiveDocument.Bookmarks("bmOldPID2").Select
: If IsNull(Forms!frmMainData!subFields!New
:
Where precisely in this code snippet do you want this conversion to occur?
Ralf

[Rob Meade]

"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