inStr evaluates to -1 causing export problem

[Carol G]

I am exporting a record from Access to Outlook.
My email fields are hyperlink type in Access. I am transfering only the
display as section of the hyperlink info to Outlook. All works fine unless
the email address is empty.
I have tried using:

Nz(Mid(strEMailAddress2, 1, inStr(1, strEMailAddress2, "#") - 1))

but I get error message. Error Number 5 Invalid procedure or argument. I'm
thinking that I don't fully understand the Nz Function.
I tried assigning the inStr Function to an int variable. If the string is
empty it returns -1.

The following works great if there is an actual email address in the record.

..Email2Address = Mid(strEMailAddress2, 1, inStr(1, strEMailAddress2, "#") -
1)

Thanks for the help.

Carol

 

[Bob Hairgrove]

I am exporting a record from Access to Outlook.
My email fields are hyperlink type in Access. I am transfering only the
display as section of the hyperlink info to Outlook. All works fine unless
the email address is empty.
I have tried using:

Nz(Mid(strEMailAddress2, 1, inStr(1, strEMailAddress2, "#") - 1))

but I get error message. Error Number 5 Invalid procedure or argument. I'm
thinking that I don't fully understand the Nz Function.

A VBA variable declared as type String will never be null. You need to
understand that both the InStr and the Mid function will return Null
ONLY if either string argument is also Null. The parameters to the
InStr and Mid functions seem to be actually expecting arguments of
type Variant with a subtype of string; unfortunately, the help file
isn't very clear about this. However, it seems that you are using a
VBA variable of type String (unless the prefix "str..." is misleading
here). In that case, it will never be Null, but could be a zero-length
string ... in which case InStr will return 0 and you will get a
run-time error in the Mid function because there is no position 1 to
start extracting a substring from a zero-length string.

I tried assigning the inStr Function to an int variable. If the string is
empty it returns -1.

According to the help file, InStr returns 0 if the initial string has
zero length. It seems that you might have assigned the following
expression to your integer variable:

inStr(1, strEMailAddress2, "#") - 1

which will indeed exaluate to -1 if strEMailAddress is empty.

You need to catch the zero-length string in your code before actually
using it in the other functions.

 

[Carol G]

Thanks replying. It is still giving me the same error message if the
strEMailAddress2 is empty so it didn't fix the problem. Any other
suggestions?
Carol

 

[Bob Hairgrove]

Good catch on string variables not being Null. (I was thinking query for
some reason). Anyway, the problem was not so much data typing as it was
incorrect syntax in the formula.

Her syntax was correct (technically speaking) since the last argument
to the Nz() function is optional if it is called from a VBA routine
and not from a query calculation.

However, without it, the Nz function isn't really very useful.

 

[Carol G]

Thanks, That makes sense now.
Carol

I am exporting a record from Access to Outlook.
My email fields are hyperlink type in Access. I am transfering only the
display as section of the hyperlink info to Outlook. All works fine unless
the email address is empty.
I have tried using:

Nz(Mid(strEMailAddress2, 1, inStr(1, strEMailAddress2, "#") - 1))

but I get error message. Error Number 5 Invalid procedure or argument. I'm
thinking that I don't fully understand the Nz Function.

A VBA variable declared as type String will never be null. You need to
understand that both the InStr and the Mid function will return Null
ONLY if either string argument is also Null. The parameters to the
InStr and Mid functions seem to be actually expecting arguments of
type Variant with a subtype of string; unfortunately, the help file
isn't very clear about this. However, it seems that you are using a
VBA variable of type String (unless the prefix "str..." is misleading
here). In that case, it will never be Null, but could be a zero-length
string ... in which case InStr will return 0 and you will get a
run-time error in the Mid function because there is no position 1 to
start extracting a substring from a zero-length string.

I tried assigning the inStr Function to an int variable. If the string is
empty it returns -1.

According to the help file, InStr returns 0 if the initial string has
zero length. It seems that you might have assigned the following
expression to your integer variable:

inStr(1, strEMailAddress2, "#") - 1

which will indeed exaluate to -1 if strEMailAddress is empty.

You need to catch the zero-length string in your code before actually
using it in the other functions.

 

[Carol G]

I didn't post anywhere else.
Carol

Your question has been answered in another group. Note that multi posting
the same question is not looked upon favorably. Please review the info at
this site:
http://www.mvps.org/access/netiquette.htm

 

[Carol G]

Thanks for the IIF code, that worked. I didn't post anywhere else and wonder
how you checked that anyway?
Carol