Instr function will not detect a space chracter

[GLT]

Hi,

I have a varible called StrBracketPhrase that contains the following value:

[JobID:439 Tue - Wed Full]

I then have the following code:

strOpenBracket1 = InStr(1, strBracketPhrase, " ")
intOpenBracket1 = strOpenBracket1 + 1

In the above string, I am trying to get the position of the first space + 1
character (which should equal 12), but intOpenBracket1 only returns the value
of 2.

I have also tried:

strOpenBracket1 = InStr(1, strBracketPhrase, chr(32))

Same deal, it only returns a value of 2.

Can anyone advise why this code is not working?

Any help is always greatly appreciated...

Cheers,
GLT.

 

[Allen Browne]

How did you declare strOpenBracket1?

The str prefix is commony used for string, so was it:
Dim strOpenBracket1 As String

If that's not the issue, ask Access what the first character is:
Debug.Print Asc(strBracketPhrase)

 

[Stefan Hoffmann]

hi,

strOpenBracket1 = InStr(1, strBracketPhrase, " ")
intOpenBracket1 = strOpenBracket1 + 1
Any help is always greatly appreciated...

You are running into an string-number conversion using once a string
variable adding a number.

Use

intOpenBracket1 = InStr(1, strBracketPhrase, " ") + 1

instead.


mfG
--> stefan <--

 

[GLT]

Hi Allan and Stefan,

Thanks for taking the time to look at this... here is the code that I am
using:

' Set up DAO objects (uses existing "tblContacts" table)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Daily Backup Results")


Dim strMemo As String
Dim strOpenBracket As Integer
Dim strClosedBracket As Integer
Dim strOpenBracket1 As Integer
Dim strClosedBracket1 As Integer
Dim strBracketPhrase As String
Dim strBracketPhrase1 As String




Do Until rst.EOF


strMemo = ""
intOpenBracket = 0
intClosedBracket = 0
strOpenBracket = 0
strClosedBracket = 0
strBracketPhrase = ""
strBracketPhrase1 = ""
intOpenBracket1 = 0
intClosedBracket1 = 0
strOpenBracket1 = 0
strClosedBracket1 = 0

strMemo = rst!Body

' Extract information between [] square brackets

strOpenBracket = InStr(1, strMemo, "[") - 1


strClosedBracket = InStr(strOpenBracket, strMemo, "]") + 1


strBracketPhrase = Mid(strMemo, strOpenBracket, strClosedBracket -
strOpenBracket)

MsgBox strBracketPhrase

' Extract information between first space and last square bracket

strOpenBracket1 = InStr(1, strBracketPhrase, " ") + 1

strClosedBracket1 = InStr(strOpenBracket1, strBracketPhrase, "]")

strBracketPhrase1 = Mid(strBracketPhrase, strOpenBracket1, strClosedBracket1
- strOpenBracket1)

MsgBox strBracketPhrase1


rst.MoveNext
Loop


strBracketPhrase works perfectly but strBracketPhrase1 still starts at
position # 1

Cheers GLT

 

[david]

i am having trouble with logone and password box keeps pooping up wheni try
and send email it is already populated with my info any suggestions

 

[Agneta Sjödin]

Hi,

I have a varible called StrBracketPhrase that contains the following
value:

[JobID:439 Tue - Wed Full]

I then have the following code:

strOpenBracket1 = InStr(1, strBracketPhrase, " ")
intOpenBracket1 = strOpenBracket1 + 1

In the above string, I am trying to get the position of the first space +
1
character (which should equal 12), but intOpenBracket1 only returns the
value
of 2.

I have also tried:

strOpenBracket1 = InStr(1, strBracketPhrase, chr(32))

Same deal, it only returns a value of 2.

Can anyone advise why this code is not working?

Any help is always greatly appreciated...

Cheers,
GLT.

 

[Agneta Sjödin]

Hi,

I have a varible called StrBracketPhrase that contains the following
value:

[JobID:439 Tue - Wed Full]

I then have the following code:

strOpenBracket1 = InStr(1, strBracketPhrase, " ")
intOpenBracket1 = strOpenBracket1 + 1

In the above string, I am trying to get the position of the first space +
1
character (which should equal 12), but intOpenBracket1 only returns the
value
of 2.

I have also tried:

strOpenBracket1 = InStr(1, strBracketPhrase, chr(32))

Same deal, it only returns a value of 2.

Can anyone advise why this code is not working?

Any help is always greatly appreciated...

Cheers,
GLT.

 

[Stefan Hoffmann]

hi,

' Set up DAO objects (uses existing "tblContacts" table)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Daily Backup Results")

Dim strMemo As String
Dim strOpenBracket As Integer
Dim strClosedBracket As Integer
Dim strOpenBracket1 As Integer
Dim strClosedBracket1 As Integer
Dim strBracketPhrase As String
Dim strBracketPhrase1 As String

As Allen wrote, name your variables according to their datatype.

' Extract information between [] square brackets
strOpenBracket = InStr(1, strMemo, "[") - 1
strClosedBracket = InStr(strOpenBracket, strMemo, "]") + 1

intOpenBracket = InStr(1, strMemo, "[")
intClosedBracket = InStr(strOpenBracket, strMemo, "]")

strBracketPhrase = Mid(strMemo, strOpenBracket, strClosedBracket -
strOpenBracket)

strBracketPhrase = Mid(strMemo, _
intOpenBracket + 1, _
intClosedBracket - intOpenBracket - 1)

MsgBox strBracketPhrase

' Extract information between first space and last square bracket
strOpenBracket1 = InStr(1, strBracketPhrase, " ") + 1
strClosedBracket1 = InStr(strOpenBracket1, strBracketPhrase, "]")

intOpenBracket1 = InStr(1, strBracketPhrase, " ")

strBracketPhrase1 = Mid(strBracketPhrase, strOpenBracket1, strClosedBracket1
- strOpenBracket1)

strBracketPhrase1 = Mid(strBracketPhrase, _
intOpenBracket1 + 1, _
Len(strBracketPhrase) - intOpenBracket1 - 1)

MsgBox strBracketPhrase1

mfG
--> stefan <--

 

[GLT]

Hi Stefan,

I have updated the datatypes on the varible - still the same thing.

Whereever I use a space in the instr it returns an incorrect value - here is
a copy of the latest vba...

Is there some special handling for spaces regarding Instr?

---------------------------------

Dim strBracketPhrase As String
Dim strServerName As String
Dim strJobID As String
Dim strBackupName As String


Do Until rst.EOF

strMemo = rst!Body
strFrom = rst!From

' Extract Clinet Name from the 'From' feild

intStartPoint = InStr(1, strFrom, "@") + 1
intEndPoint = InStr(1, strFrom, ".")
strJobID = Mid(strFrom, intStartPoint, intEndPoint - intStartPoint)

' Extract Server Name

strServerName = Left([strMemo], InStr([strMemo], ":") - 1)

' Extract information between [] square brackets - contains JobID & Backup
Name

intOpenBracket = InStr(1, strMemo, "[") - 1
intClosedBracket = InStr(intOpenBracket, strMemo, "]") + 1

strBracketPhrase = Mid(strMemo, intOpenBracket, intClosedBracket -
intOpenBracket)
MsgBox strBracketPhrase

' Extract JobID number from strBracketPhrase (appears between the 1st : and
space)

intStartPoint = InStr(1, strBracketPhrase, ":") + 1
intEndPoint = InStr(intStartPoint, strBracketPhrase, " ") - 1

JobID = Mid(strBracketPhrase, intStartPoint, (intEndPoint - intStartPoint))

' Extract Backup Name from strBracketPhrase

intStartPoint = InStr(1, strBracketPhrase, " ") + 1
intEndPoint = InStr(1, strBracketPhrase, "]") - 1

backupName = Mid(strBracketPhrase, intStartPoint, (intEndPoint -
intStartPoint))
MsgBox backupName


rst.MoveNext
Loop

End Sub

---------------------------------------

hi,

' Set up DAO objects (uses existing "tblContacts" table)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Daily Backup Results")

Dim strMemo As String
Dim strOpenBracket As Integer
Dim strClosedBracket As Integer
Dim strOpenBracket1 As Integer
Dim strClosedBracket1 As Integer
Dim strBracketPhrase As String
Dim strBracketPhrase1 As String

As Allen wrote, name your variables according to their datatype.

' Extract information between [] square brackets
strOpenBracket = InStr(1, strMemo, "[") - 1
strClosedBracket = InStr(strOpenBracket, strMemo, "]") + 1

intOpenBracket = InStr(1, strMemo, "[")
intClosedBracket = InStr(strOpenBracket, strMemo, "]")

strBracketPhrase = Mid(strMemo, strOpenBracket, strClosedBracket -
strOpenBracket)

strBracketPhrase = Mid(strMemo, _
intOpenBracket + 1, _
intClosedBracket - intOpenBracket - 1)

MsgBox strBracketPhrase

' Extract information between first space and last square bracket
strOpenBracket1 = InStr(1, strBracketPhrase, " ") + 1
strClosedBracket1 = InStr(strOpenBracket1, strBracketPhrase, "]")

intOpenBracket1 = InStr(1, strBracketPhrase, " ")

strBracketPhrase1 = Mid(strBracketPhrase, strOpenBracket1, strClosedBracket1
- strOpenBracket1)

strBracketPhrase1 = Mid(strBracketPhrase, _
intOpenBracket1 + 1, _
Len(strBracketPhrase) - intOpenBracket1 - 1)

MsgBox strBracketPhrase1

mfG
--> stefan <--

 

[GLT]

Sorry, missed some code:
------------------------------------------

Sub SomeButton_Click()

' Set up DAO objects (uses existing "Daily Backup Results" table)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Daily Backup Results")


Dim strMemo As String
Dim strFrom As String
Dim intOpenBracket As Integer
Dim intClosedBracket As Integer
Dim intStartPoint As Integer
Dim intEndPoint As Integer

Dim strBracketPhrase As String
Dim strServerName As String
Dim strJobID As String
Dim strBackupName As String


Do Until rst.EOF

strMemo = rst!Body
strFrom = rst!From

' Extract Clinet Name from the 'From' feild

intStartPoint = InStr(1, strFrom, "@") + 1
intEndPoint = InStr(1, strFrom, ".")
strJobID = Mid(strFrom, intStartPoint, intEndPoint - intStartPoint)

' Extract Server Name

strServerName = Left([strMemo], InStr([strMemo], ":") - 1)

' Extract information between [] square brackets - contains JobID & Backup
Name

intOpenBracket = InStr(1, strMemo, "[") - 1
intClosedBracket = InStr(intOpenBracket, strMemo, "]") + 1

strBracketPhrase = Mid(strMemo, intOpenBracket, intClosedBracket -
intOpenBracket)
MsgBox strBracketPhrase

' Extract JobID number from strBracketPhrase (appears between the 1st : and
space)

intStartPoint = InStr(1, strBracketPhrase, ":") + 1
intEndPoint = InStr(intStartPoint, strBracketPhrase, " ") - 1

JobID = Mid(strBracketPhrase, intStartPoint, (intEndPoint - intStartPoint))

' Extract Backup Name from strBracketPhrase

intStartPoint = InStr(1, strBracketPhrase, " ") + 1
intEndPoint = InStr(1, strBracketPhrase, "]") - 1

backupName = Mid(strBracketPhrase, intStartPoint, (intEndPoint -
intStartPoint))
MsgBox backupName


rst.MoveNext
Loop

End Sub

---------------------------------------

Hi Stefan,

I have updated the datatypes on the varible - still the same thing.

Whereever I use a space in the instr it returns an incorrect value - here is
a copy of the latest vba...

Is there some special handling for spaces regarding Instr?

---------------------------------

Dim strBracketPhrase As String
Dim strServerName As String
Dim strJobID As String
Dim strBackupName As String


Do Until rst.EOF

strMemo = rst!Body
strFrom = rst!From

' Extract Clinet Name from the 'From' feild

intStartPoint = InStr(1, strFrom, "@") + 1
intEndPoint = InStr(1, strFrom, ".")
strJobID = Mid(strFrom, intStartPoint, intEndPoint - intStartPoint)

' Extract Server Name

strServerName = Left([strMemo], InStr([strMemo], ":") - 1)

' Extract information between [] square brackets - contains JobID & Backup
Name

intOpenBracket = InStr(1, strMemo, "[") - 1
intClosedBracket = InStr(intOpenBracket, strMemo, "]") + 1

strBracketPhrase = Mid(strMemo, intOpenBracket, intClosedBracket -
intOpenBracket)
MsgBox strBracketPhrase

' Extract JobID number from strBracketPhrase (appears between the 1st : and
space)

intStartPoint = InStr(1, strBracketPhrase, ":") + 1
intEndPoint = InStr(intStartPoint, strBracketPhrase, " ") - 1

JobID = Mid(strBracketPhrase, intStartPoint, (intEndPoint - intStartPoint))

' Extract Backup Name from strBracketPhrase

intStartPoint = InStr(1, strBracketPhrase, " ") + 1
intEndPoint = InStr(1, strBracketPhrase, "]") - 1

backupName = Mid(strBracketPhrase, intStartPoint, (intEndPoint -
intStartPoint))
MsgBox backupName


rst.MoveNext
Loop

End Sub

---------------------------------------

hi,

' Set up DAO objects (uses existing "tblContacts" table)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Daily Backup Results")

Dim strMemo As String
Dim strOpenBracket As Integer
Dim strClosedBracket As Integer
Dim strOpenBracket1 As Integer
Dim strClosedBracket1 As Integer
Dim strBracketPhrase As String
Dim strBracketPhrase1 As String

As Allen wrote, name your variables according to their datatype.

' Extract information between [] square brackets
strOpenBracket = InStr(1, strMemo, "[") - 1
strClosedBracket = InStr(strOpenBracket, strMemo, "]") + 1

intOpenBracket = InStr(1, strMemo, "[")
intClosedBracket = InStr(strOpenBracket, strMemo, "]")

strBracketPhrase = Mid(strMemo, strOpenBracket, strClosedBracket -
strOpenBracket)

strBracketPhrase = Mid(strMemo, _
intOpenBracket + 1, _
intClosedBracket - intOpenBracket - 1)

MsgBox strBracketPhrase

' Extract information between first space and last square bracket
strOpenBracket1 = InStr(1, strBracketPhrase, " ") + 1
strClosedBracket1 = InStr(strOpenBracket1, strBracketPhrase, "]")

intOpenBracket1 = InStr(1, strBracketPhrase, " ")

strBracketPhrase1 = Mid(strBracketPhrase, strOpenBracket1, strClosedBracket1
- strOpenBracket1)

strBracketPhrase1 = Mid(strBracketPhrase, _
intOpenBracket1 + 1, _
Len(strBracketPhrase) - intOpenBracket1 - 1)

MsgBox strBracketPhrase1

mfG
--> stefan <--

 

[GLT]

Hi,

I finally figured out what was going wrong here (wish I had realised it at
02:00 am this morning), would not be so tired now

The strBracketPhrase string had a leading space at the beginning of the
string.

I changed the following line:

intStartPoint = InStr(1, strBracketPhrase, " ") + 1

to start from column 2:

intStartPoint = InStr(2, strBracketPhrase, " ") + 1

and now all works ok...

Cheers,
GLT

Sorry, missed some code:
------------------------------------------

Sub SomeButton_Click()

' Set up DAO objects (uses existing "Daily Backup Results" table)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Daily Backup Results")


Dim strMemo As String
Dim strFrom As String
Dim intOpenBracket As Integer
Dim intClosedBracket As Integer
Dim intStartPoint As Integer
Dim intEndPoint As Integer

Dim strBracketPhrase As String
Dim strServerName As String
Dim strJobID As String
Dim strBackupName As String


Do Until rst.EOF

strMemo = rst!Body
strFrom = rst!From

' Extract Clinet Name from the 'From' feild

intStartPoint = InStr(1, strFrom, "@") + 1
intEndPoint = InStr(1, strFrom, ".")
strJobID = Mid(strFrom, intStartPoint, intEndPoint - intStartPoint)

' Extract Server Name

strServerName = Left([strMemo], InStr([strMemo], ":") - 1)

' Extract information between [] square brackets - contains JobID & Backup
Name

intOpenBracket = InStr(1, strMemo, "[") - 1
intClosedBracket = InStr(intOpenBracket, strMemo, "]") + 1

strBracketPhrase = Mid(strMemo, intOpenBracket, intClosedBracket -
intOpenBracket)
MsgBox strBracketPhrase

' Extract JobID number from strBracketPhrase (appears between the 1st : and
space)

intStartPoint = InStr(1, strBracketPhrase, ":") + 1
intEndPoint = InStr(intStartPoint, strBracketPhrase, " ") - 1

JobID = Mid(strBracketPhrase, intStartPoint, (intEndPoint - intStartPoint))

' Extract Backup Name from strBracketPhrase

intStartPoint = InStr(1, strBracketPhrase, " ") + 1
intEndPoint = InStr(1, strBracketPhrase, "]") - 1

backupName = Mid(strBracketPhrase, intStartPoint, (intEndPoint -
intStartPoint))
MsgBox backupName


rst.MoveNext
Loop

End Sub

---------------------------------------

Hi Stefan,

I have updated the datatypes on the varible - still the same thing.

Whereever I use a space in the instr it returns an incorrect value - here is
a copy of the latest vba...

Is there some special handling for spaces regarding Instr?

---------------------------------

Dim strBracketPhrase As String
Dim strServerName As String
Dim strJobID As String
Dim strBackupName As String


Do Until rst.EOF

strMemo = rst!Body
strFrom = rst!From

' Extract Clinet Name from the 'From' feild

intStartPoint = InStr(1, strFrom, "@") + 1
intEndPoint = InStr(1, strFrom, ".")
strJobID = Mid(strFrom, intStartPoint, intEndPoint - intStartPoint)

' Extract Server Name

strServerName = Left([strMemo], InStr([strMemo], ":") - 1)

' Extract information between [] square brackets - contains JobID & Backup
Name

intOpenBracket = InStr(1, strMemo, "[") - 1
intClosedBracket = InStr(intOpenBracket, strMemo, "]") + 1

strBracketPhrase = Mid(strMemo, intOpenBracket, intClosedBracket -
intOpenBracket)
MsgBox strBracketPhrase

' Extract JobID number from strBracketPhrase (appears between the 1st : and
space)

intStartPoint = InStr(1, strBracketPhrase, ":") + 1
intEndPoint = InStr(intStartPoint, strBracketPhrase, " ") - 1

JobID = Mid(strBracketPhrase, intStartPoint, (intEndPoint - intStartPoint))

' Extract Backup Name from strBracketPhrase

intStartPoint = InStr(1, strBracketPhrase, " ") + 1
intEndPoint = InStr(1, strBracketPhrase, "]") - 1

backupName = Mid(strBracketPhrase, intStartPoint, (intEndPoint -
intStartPoint))
MsgBox backupName


rst.MoveNext
Loop

End Sub

---------------------------------------

hi,

GLT wrote:
' Set up DAO objects (uses existing "tblContacts" table)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Daily Backup Results")

Dim strMemo As String
Dim strOpenBracket As Integer
Dim strClosedBracket As Integer
Dim strOpenBracket1 As Integer
Dim strClosedBracket1 As Integer
Dim strBracketPhrase As String
Dim strBracketPhrase1 As String
As Allen wrote, name your variables according to their datatype.

' Extract information between [] square brackets
strOpenBracket = InStr(1, strMemo, "[") - 1
strClosedBracket = InStr(strOpenBracket, strMemo, "]") + 1
intOpenBracket = InStr(1, strMemo, "[")
intClosedBracket = InStr(strOpenBracket, strMemo, "]")

strBracketPhrase = Mid(strMemo, strOpenBracket, strClosedBracket -
strOpenBracket)
strBracketPhrase = Mid(strMemo, _
intOpenBracket + 1, _
intClosedBracket - intOpenBracket - 1)
MsgBox strBracketPhrase

' Extract information between first space and last square bracket
strOpenBracket1 = InStr(1, strBracketPhrase, " ") + 1
strClosedBracket1 = InStr(strOpenBracket1, strBracketPhrase, "]")
intOpenBracket1 = InStr(1, strBracketPhrase, " ")

strBracketPhrase1 = Mid(strBracketPhrase, strOpenBracket1, strClosedBracket1
- strOpenBracket1)
strBracketPhrase1 = Mid(strBracketPhrase, _
intOpenBracket1 + 1, _
Len(strBracketPhrase) - intOpenBracket1 - 1)

MsgBox strBracketPhrase1


mfG
--> stefan <--

 

[Allen Browne]

Same you missed this reply earlier:

If that's not the issue, ask Access what the first character is:
Debug.Print Asc(strBracketPhrase)

 

[Larry Linson]

This newsgroup is for Microsoft Access database software. There are
Microsoft and USENET newsgroups devoted to Outlook Express, where you would
vastly increase the probability of getting a useful answer to the question
you are asking.

Larry Linson
Microsoft Office Access MVP

i am having trouble with logone and password box keeps pooping up wheni try
and send email it is already populated with my info any suggestions
--
WHen i try and type email the logone and password box keeps popping up
already populated with my info and will not let me send email it keeps
poping up over and over again

Hi,

I have a varible called StrBracketPhrase that contains the following
value:

[JobID:439 Tue - Wed Full]

I then have the following code:

strOpenBracket1 = InStr(1, strBracketPhrase, " ")
intOpenBracket1 = strOpenBracket1 + 1

In the above string, I am trying to get the position of the first space +
1
character (which should equal 12), but intOpenBracket1 only returns the
value
of 2.

I have also tried:

strOpenBracket1 = InStr(1, strBracketPhrase, chr(32))

Same deal, it only returns a value of 2.

Can anyone advise why this code is not working?

Any help is always greatly appreciated...

Cheers,
GLT.