Invalid Procedure Call

[nick.terry]

Hi all,

Ok, this is a bit hard to classify, but it uses SQL and references
queries, so here goes...

I completed a database last thursday, and went to back it up and make
several copies. Unfortunatly in my rush to backup, I backed up the
wrong file and deleted the finished one. Luckily, I was able to
restore from a backup the night before, and only lost 1 days work.

Unfortunatly, now one of my modules will not work, giving me a generic
"Invalid Procedure Call" error. After extensive testing and
debugging, I have isolated the problem.

My module is designed to import a HUGE amount of data over ODBC from a
linked table, copy it to a local table for quick access, then format
the raw data (which involves the use of Mid and InStr) to insert into
a working table.

This is done with about 9 SQL strings total, including error
handling. My problem is occuring in the SQL string that takes the raw
data from the local table, formats it, and appends it to the final
table.

Raw Data Table: pipointlocal
Fields: tag, pointid, date

Finished Table: TagTable
Fields: PointTag, Station, TagName, PointID

SQL:

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT [pipointlocal].[tag], Mid([pipointlocal].[tag],4,InStr(4,
[pipointlocal].[tag],'_')-4) AS Station, Mid([pipointlocal].
[tag],InStr(4,[pipointlocal].[tag],'_')+1) AS TagName, [pipointlocal].
[PointID]
FROM pipointlocal;

'tag is a concatenated string which contains a station and a
'tag name, so the left and mid funtions slice it up into its
'respective parts

This SQL statement is put into a string and executed in VBA.

If I use the SQL in a query, it works fine. It only creates a problem
when executed using VBA.

Please help, I've already spent over 8 hours trying to troubleshoot
this problem and have gotten nowhere!

Thanks,

Nick

 

[Gary Walter]

<nick.terry wrote :

Ok, this is a bit hard to classify, but it uses SQL and references
queries, so here goes...

I completed a database last thursday, and went to back it up and make
several copies. Unfortunatly in my rush to backup, I backed up the
wrong file and deleted the finished one. Luckily, I was able to
restore from a backup the night before, and only lost 1 days work.

Unfortunatly, now one of my modules will not work, giving me a generic
"Invalid Procedure Call" error. After extensive testing and
debugging, I have isolated the problem.

My module is designed to import a HUGE amount of data over ODBC from a
linked table, copy it to a local table for quick access, then format
the raw data (which involves the use of Mid and InStr) to insert into
a working table.

This is done with about 9 SQL strings total, including error
handling. My problem is occuring in the SQL string that takes the raw
data from the local table, formats it, and appends it to the final
table.

Raw Data Table: pipointlocal
Fields: tag, pointid, date

Finished Table: TagTable
Fields: PointTag, Station, TagName, PointID

SQL:

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT [pipointlocal].[tag], Mid([pipointlocal].[tag],4,InStr(4,
[pipointlocal].[tag],'_')-4) AS Station, Mid([pipointlocal].
[tag],InStr(4,[pipointlocal].[tag],'_')+1) AS TagName, [pipointlocal].
[PointID]
FROM pipointlocal;

'tag is a concatenated string which contains a station and a
'tag name, so the left and mid funtions slice it up into its
'respective parts

This SQL statement is put into a string and executed in VBA.

If I use the SQL in a query, it works fine. It only creates a problem
when executed using VBA.

<snip>

Hi Nick,

You don't mention Access nor Windows version,
but a WAG might involve the difference of "compare"
between QBE and VBA for your InStr function
(for which you have chosen to assume "default").

Again as a real WAG, I might first try adding text compare (=1)
because it "works" in QBE but not in VBA

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT
P.[tag],
Mid(P.[tag],4,InStr(4,P.[tag],'_',1)-4) AS Station,
Mid(P.[tag],InStr(4,P.[tag],'_',1)+1) AS TagName,
P.[PointID]
FROM
pipointlocal AS P;

Above assumes that SQL has been properly
formed in VBA string (no " within quotes,etc)
which I usually test by adding

Debug.Print strSQL

after assembling strSQL in VBA.

And I assume you have removed 2 parsing
lines to verify they are the problem (maybe
remove one then the other to limit if can).

Next place I might look is what values "tag"
might have (and question assumptions I am
making about "tag").

SELECT DISTINCT
P.[tag],
Len(P.[tag]) As TagLen,
InStr(1, P.[tag],'_',1) As FirstPos
FROM
pipointlocal AS P;

Once I fully understood the variants
of "tag" values, then I might look to
see where values outside my previous
assumptions would cause me to change
my parsing.

I really don't understand your parsing
for "Station" and "TagName."

It looks like you already
assume the "_" will be at pos 4?

Or will always occur on or after pos 4?

Can there be more than one "_" in string?

Or possibly a "tag" with no "_" char?

So following won't work?

Left(P.[tag], InStr(P.[tag],'_') -1) As Station,
Mid(P.[tag],InStr(P.[tag],'_')+1) AS TagName,

or

Left(P.[tag], InStr(1,P.[tag],'_',1) -1) As Station,
Mid(P.[tag],InStr(1,P.[tag],'_',1)+1) AS TagName,


in Immediate Window...

tag=null
?Mid(tag,4,InStr(4,tag,'_',1)-4) <--compile error
?Mid(tag,4,InStr(4,tag,'_',1)+1) <--compile error

What happens if you use an IIF test before your parsing?
(or in WHERE clause to weed out "bad tags")

Of course a problem with References can cause
your error message, but you said it "works"
as a query alone.

If above WAGS do not help, please provide
a list of all the various types of values "tag" can
be.

Good Luck,

gary

 

[nick.terry]

<nick.terry wrote :

Ok, this is a bit hard to classify, but it uses SQL and references
queries, so here goes...

I completed a database last thursday, and went to back it up and make
several copies. Unfortunatly in my rush to backup, I backed up the
wrong file and deleted the finished one. Luckily, I was able to
restore from a backup the night before, and only lost 1 days work.

Unfortunatly, now one of my modules will not work, giving me a generic
"Invalid Procedure Call" error. After extensive testing and
debugging, I have isolated the problem.

My module is designed to import a HUGE amount of data over ODBC from a
linked table, copy it to a local table for quick access, then format
the raw data (which involves the use of Mid and InStr) to insert into
a working table.

This is done with about 9 SQL strings total, including error
handling. My problem is occuring in the SQL string that takes the raw
data from the local table, formats it, and appends it to the final
table.

Raw Data Table: pipointlocal
Fields: tag, pointid, date

Finished Table: TagTable
Fields: PointTag, Station, TagName, PointID

SQL:

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT [pipointlocal].[tag], Mid([pipointlocal].[tag],4,InStr(4,
[pipointlocal].[tag],'_')-4) AS Station, Mid([pipointlocal].
[tag],InStr(4,[pipointlocal].[tag],'_')+1) AS TagName, [pipointlocal].
[PointID]
FROM pipointlocal;

'tag is a concatenated string which contains a station and a
'tag name, so the left and mid funtions slice it up into its
'respective parts

This SQL statement is put into a string and executed in VBA.

If I use the SQL in a query, it works fine. It only creates a problem
when executed using VBA.

<snip>

Hi Nick,

You don't mention Access nor Windows version,
but a WAG might involve the difference of "compare"
between QBE and VBA for your InStr function
(for which you have chosen to assume "default").

Again as a real WAG, I might first try adding text compare (=1)
because it "works" in QBE but not in VBA

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT
P.[tag],
Mid(P.[tag],4,InStr(4,P.[tag],'_',1)-4) AS Station,
Mid(P.[tag],InStr(4,P.[tag],'_',1)+1) AS TagName,
P.[PointID]
FROM
pipointlocal AS P;

Above assumes that SQL has been properly
formed in VBA string (no " within quotes,etc)
which I usually test by adding

Debug.Print strSQL

after assembling strSQL in VBA.

And I assume you have removed 2 parsing
lines to verify they are the problem (maybe
remove one then the other to limit if can).

Next place I might look is what values "tag"
might have (and question assumptions I am
making about "tag").

SELECT DISTINCT
P.[tag],
Len(P.[tag]) As TagLen,
InStr(1, P.[tag],'_',1) As FirstPos
FROM
pipointlocal AS P;

Once I fully understood the variants
of "tag" values, then I might look to
see where values outside my previous
assumptions would cause me to change
my parsing.

I really don't understand your parsing
for "Station" and "TagName."

It looks like you already
assume the "_" will be at pos 4?

Or will always occur on or after pos 4?

Can there be more than one "_" in string?

Or possibly a "tag" with no "_" char?

So following won't work?

Left(P.[tag], InStr(P.[tag],'_') -1) As Station,
Mid(P.[tag],InStr(P.[tag],'_')+1) AS TagName,

or

Left(P.[tag], InStr(1,P.[tag],'_',1) -1) As Station,
Mid(P.[tag],InStr(1,P.[tag],'_',1)+1) AS TagName,

in Immediate Window...

tag=null
?Mid(tag,4,InStr(4,tag,'_',1)-4) <--compile error
?Mid(tag,4,InStr(4,tag,'_',1)+1) <--compile error

What happens if you use an IIF test before your parsing?
(or in WHERE clause to weed out "bad tags")

Of course a problem with References can cause
your error message, but you said it "works"
as a query alone.

If above WAGS do not help, please provide
a list of all the various types of values "tag" can
be.

Good Luck,

gary- Hide quoted text -

- Show quoted text -

Gary,

Thank you so much for taking the time to help me with this, and for
all of your suggestions.

Firstly, I apologize for not providing versions. I am running Access
2003 on Windows XP.

Your first advice was to try adding the text compare option. I just
tried that, but unfortunatly saw no results. So much for the easy fix
right?

Secondly, you assumed I had tested to make sure that the parsing
statements where in fact the paroblem. Well, I hadn't unfortunatly,
but as you mentioned, I tried them and found that the InStr function
is the problem. Specifically, the first InStr() function. Now the
SQL statement reads:

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT
P.[tag],
Mid(P.[tag],4,6) AS Station,
Mid(P.[tag],InStr(4,P.[tag],'_',1)+1) AS TagName,
P.[PointID]
FROM
pipointlocal AS P;

Somehow, this works, while adding the InStr() function back in:

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT
P.[tag],
Mid(P.[tag],4,InStr(4,P.[tag],'_',1)-4) AS Station,
Mid(P.[tag],InStr(4,P.[tag],'_',1)+1) AS TagName,
P.[PointID]
FROM
pipointlocal AS P;

Prevents it from working. Any ideas?

The way most tags work is that they are a concatenation of information
in a string. The information is as follows:

tagtype + "_" + station + "_" + tagname

Now when the tags are imported on ODBC, they are filtered to only
include tags that begin with the type "XA" using a WHERE statement.

Therefore, the station will ALWAYS start in position four of the
string.

The station can be either 5 or 6 characters long, and will always be
followed by an underscore. This is the reason for the InStr()
function, which won't work.

Would there be another way to accomplish this?

Next, you mentioned References. I have checked the list, and none are
missing. I have also checked an extra one, quit, reopened Access and
unchecked it to try and get Access to reload these references.
Neither of these options has worked either.


Since none of your WAGS have helped, you requested I include a list of
all types of tags. Here goes:

Tags Following the Previously Described Format:
(I have only included the types since the station and name have been
described previously)

XA
C
BA
CA

Tags Not Following Format:

Hexadecimal strings
Various strings of letters, colons, and numbers


Again, thank you for your help, and hopefully now you may have a
better idea of what might be wrong.

Nick

 

[nick.terry]

<nick.terry wrote :

Ok, this is a bit hard to classify, but it uses SQL and references
queries, so here goes...
I completed a database last thursday, and went to back it up and make
several copies. Unfortunatly in my rush to backup, I backed up the
wrong file and deleted the finished one. Luckily, I was able to
restore from a backup the night before, and only lost 1 days work.
Unfortunatly, now one of my modules will not work, giving me a generic
"Invalid Procedure Call" error. After extensive testing and
debugging, I have isolated the problem.
My module is designed to import a HUGE amount of data over ODBC from a
linked table, copy it to a local table for quick access, then format
the raw data (which involves the use of Mid and InStr) to insert into
a working table.
This is done with about 9 SQL strings total, including error
handling. My problem is occuring in the SQL string that takes the raw
data from the local table, formats it, and appends it to the final
table.
Raw Data Table: pipointlocal
Fields: tag, pointid, date
Finished Table: TagTable
Fields: PointTag, Station, TagName, PointID
SQL:
INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT [pipointlocal].[tag], Mid([pipointlocal].[tag],4,InStr(4,
[pipointlocal].[tag],'_')-4) AS Station, Mid([pipointlocal].
[tag],InStr(4,[pipointlocal].[tag],'_')+1) AS TagName, [pipointlocal].
[PointID]
FROM pipointlocal;
'tag is a concatenated string which contains a station and a
'tag name, so the left and mid funtions slice it up into its
'respective parts
This SQL statement is put into a string and executed in VBA.
If I use the SQL in a query, it works fine. It only creates a problem
when executed using VBA.

<snip>

Hi Nick,

You don't mention Access nor Windows version,
but a WAG might involve the difference of "compare"
between QBE and VBA for your InStr function
(for which you have chosen to assume "default").

Again as a real WAG, I might first try adding text compare (=1)
because it "works" in QBE but not in VBA

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT
P.[tag],
Mid(P.[tag],4,InStr(4,P.[tag],'_',1)-4) AS Station,
Mid(P.[tag],InStr(4,P.[tag],'_',1)+1) AS TagName,
P.[PointID]
FROM
pipointlocal AS P;

Above assumes that SQL has been properly
formed in VBA string (no " within quotes,etc)
which I usually test by adding

Debug.Print strSQL

after assembling strSQL in VBA.

And I assume you have removed 2 parsing
lines to verify they are the problem (maybe
remove one then the other to limit if can).

Next place I might look is what values "tag"
might have (and question assumptions I am
making about "tag").

SELECT DISTINCT
P.[tag],
Len(P.[tag]) As TagLen,
InStr(1, P.[tag],'_',1) As FirstPos
FROM
pipointlocal AS P;

Once I fully understood the variants
of "tag" values, then I might look to
see where values outside my previous
assumptions would cause me to change
my parsing.

I really don't understand your parsing
for "Station" and "TagName."

It looks like you already
assume the "_" will be at pos 4?

Or will always occur on or after pos 4?

Can there be more than one "_" in string?

Or possibly a "tag" with no "_" char?

So following won't work?

Left(P.[tag], InStr(P.[tag],'_') -1) As Station,
Mid(P.[tag],InStr(P.[tag],'_')+1) AS TagName,

or

Left(P.[tag], InStr(1,P.[tag],'_',1) -1) As Station,
Mid(P.[tag],InStr(1,P.[tag],'_',1)+1) AS TagName,

in Immediate Window...

tag=null
?Mid(tag,4,InStr(4,tag,'_',1)-4) <--compile error
?Mid(tag,4,InStr(4,tag,'_',1)+1) <--compile error

What happens if you use an IIF test before your parsing?
(or in WHERE clause to weed out "bad tags")

Of course a problem with References can cause
your error message, but you said it "works"
as a query alone.

If above WAGS do not help, please provide
a list of all the various types of values "tag" can
be.

Good Luck,

gary- Hide quoted text -

- Show quoted text -

Gary,

Thank you so much for taking the time to help me with this, and for
all of your suggestions.

Firstly, I apologize for not providing versions. I am running Access
2003 on Windows XP.

Your first advice was to try adding the text compare option. I just
tried that, but unfortunatly saw no results. So much for the easy fix
right?

Secondly, you assumed I had tested to make sure that the parsing
statements where in fact the paroblem. Well, I hadn't unfortunatly,
but as you mentioned, I tried them and found that the InStr function
is the problem. Specifically, the first InStr() function. Now the
SQL statement reads:

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT
P.[tag],
Mid(P.[tag],4,6) AS Station,
Mid(P.[tag],InStr(4,P.[tag],'_',1)+1) AS TagName,
P.[PointID]
FROM
pipointlocal AS P;

Somehow, this works, while adding the InStr() function back in:

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT
P.[tag],
Mid(P.[tag],4,InStr(4,P.[tag],'_',1)-4) AS Station,
Mid(P.[tag],InStr(4,P.[tag],'_',1)+1) AS TagName,
P.[PointID]
FROM
pipointlocal AS P;

Prevents it from working. Any ideas?

The way most tags work is that they are a concatenation of information
in a string. The information is as follows:

tagtype + "_" + station + "_" + tagname

Now when the tags are imported on ODBC, they are filtered to only
include tags that begin with the type "XA" using a WHERE statement.

Therefore, the station will ALWAYS start in position four of the
string.

The station can be either 5 or 6 characters long, and will always be
followed by an underscore. This is the reason for the InStr()
function, which won't work.

Would there be another way to accomplish this?

Next, you mentioned References. I have checked the list, and none are
missing. I have also checked an extra one, quit, reopened Access and
unchecked it to try and get Access to reload these references.
Neither of these options has worked either.

Since none of your WAGS have helped, you requested I include a list of
all types of tags. Here goes:

Tags Following the Previously Described Format:
(I have only included the types since the station and name have been
described previously)

XA
C
BA
CA

Tags Not Following Format:

Hexadecimal strings
Various strings of letters, colons, and numbers

Again, thank you for your help, and hopefully now you may have a
better idea of what might be wrong.

Nick- Hide quoted text -

- Show quoted text -

After further debugging, I have found that it is not the InStr()
function, it is the subtraction following it that causes the error.

This SQL works:

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT
P.[tag],
Mid(P.[tag],4,InStr(4,P.[tag],'_',1)) AS Station,
Mid(P.[tag],InStr(4,P.[tag],'_',1)+1) AS TagName,
P.[PointID]
FROM
pipointlocal AS P;

While this doesn't:

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT
P.[tag],
Mid(P.[tag],4,InStr(4,P.[tag],'_',1)-4) AS Station,
Mid(P.[tag],InStr(4,P.[tag],'_',1)+1) AS TagName,
P.[PointID]
FROM
pipointlocal AS P;

I am thoroughly confused!!!!

Nick

 

[John Spencer]

If Tag has a length that is less than 4 or there is no "_" then you are
passing an invalid value of zero or less than zero to the mid argument. You
might try the following, where you specifically check for a minimum 4
characters and an underscore in TAG before you attempt to manipulate it

IIF(P.Tag Like "????*_*", Mid(P.[tag],4,InStr(4,P.[tag],'_',1)-4), Null) as
Station


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[nick.terry]

<nick.terry wrote :
Ok, this is a bit hard to classify, but it uses SQL and references
queries, so here goes...
I completed a database last thursday, and went to back it up and make
several copies. Unfortunatly in my rush to backup, I backed up the
wrong file and deleted the finished one. Luckily, I was able to
restore from a backup the night before, and only lost 1 days work.
Unfortunatly, now one of my modules will not work, giving me a generic
"Invalid Procedure Call" error. After extensive testing and
debugging, I have isolated the problem.
My module is designed to import a HUGE amount of data over ODBC from a
linked table, copy it to a local table for quick access, then format
the raw data (which involves the use of Mid and InStr) to insert into
a working table.
This is done with about 9 SQL strings total, including error
handling. My problem is occuring in the SQL string that takes the raw
data from the local table, formats it, and appends it to the final
table.
Raw Data Table: pipointlocal
Fields: tag, pointid, date
Finished Table: TagTable
Fields: PointTag, Station, TagName, PointID
SQL:
INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT [pipointlocal].[tag], Mid([pipointlocal].[tag],4,InStr(4,
[pipointlocal].[tag],'_')-4) AS Station, Mid([pipointlocal].
[tag],InStr(4,[pipointlocal].[tag],'_')+1) AS TagName, [pipointlocal].
[PointID]
FROM pipointlocal;
'tag is a concatenated string which contains a station and a
'tag name, so the left and mid funtions slice it up into its
'respective parts
This SQL statement is put into a string and executed in VBA.
If I use the SQL in a query, it works fine. It only creates a problem
when executed using VBA.
<snip>
Hi Nick,
You don't mention Access nor Windows version,
but a WAG might involve the difference of "compare"
between QBE and VBA for your InStr function
(for which you have chosen to assume "default").
Again as a real WAG, I might first try adding text compare (=1)
because it "works" in QBE but not in VBA
INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT
P.[tag],
Mid(P.[tag],4,InStr(4,P.[tag],'_',1)-4) AS Station,
Mid(P.[tag],InStr(4,P.[tag],'_',1)+1) AS TagName,
P.[PointID]
FROM
pipointlocal AS P;
Above assumes that SQL has been properly
formed in VBA string (no " within quotes,etc)
which I usually test by adding
Debug.Print strSQL
after assembling strSQL in VBA.
And I assume you have removed 2 parsing
lines to verify they are the problem (maybe
remove one then the other to limit if can).
Next place I might look is what values "tag"
might have (and question assumptions I am
making about "tag").
SELECT DISTINCT
P.[tag],
Len(P.[tag]) As TagLen,
InStr(1, P.[tag],'_',1) As FirstPos
FROM
pipointlocal AS P;
Once I fully understood the variants
of "tag" values, then I might look to
see where values outside my previous
assumptions would cause me to change
my parsing.
I really don't understand your parsing
for "Station" and "TagName."
It looks like you already
assume the "_" will be at pos 4?
Or will always occur on or after pos 4?
Can there be more than one "_" in string?
Or possibly a "tag" with no "_" char?
So following won't work?
Left(P.[tag], InStr(P.[tag],'_') -1) As Station,
Mid(P.[tag],InStr(P.[tag],'_')+1) AS TagName,
or
Left(P.[tag], InStr(1,P.[tag],'_',1) -1) As Station,
Mid(P.[tag],InStr(1,P.[tag],'_',1)+1) AS TagName,
in Immediate Window...
tag=null
?Mid(tag,4,InStr(4,tag,'_',1)-4) <--compile error
?Mid(tag,4,InStr(4,tag,'_',1)+1) <--compile error
What happens if you use an IIF test before your parsing?
(or in WHERE clause to weed out "bad tags")
Of course a problem with References can cause
your error message, but you said it "works"
as a query alone.
If above WAGS do not help, please provide
a list of all the various types of values "tag" can
be.
Good Luck,
gary- Hide quoted text -
- Show quoted text -

Gary,

Thank you so much for taking the time to help me with this, and for
all of your suggestions.

Firstly, I apologize for not providing versions. I am running Access
2003 on Windows XP.

Your first advice was to try adding the text compare option. I just
tried that, but unfortunatly saw no results. So much for the easy fix
right?

Secondly, you assumed I had tested to make sure that the parsing
statements where in fact the paroblem. Well, I hadn't unfortunatly,
but as you mentioned, I tried them and found that the InStr function
is the problem. Specifically, the first InStr() function. Now the
SQL statement reads:

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT
P.[tag],
Mid(P.[tag],4,6) AS Station,
Mid(P.[tag],InStr(4,P.[tag],'_',1)+1) AS TagName,
P.[PointID]
FROM
pipointlocal AS P;

Somehow, this works, while adding the InStr() function back in:

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT
P.[tag],
Mid(P.[tag],4,InStr(4,P.[tag],'_',1)-4) AS Station,
Mid(P.[tag],InStr(4,P.[tag],'_',1)+1) AS TagName,
P.[PointID]
FROM
pipointlocal AS P;

Prevents it from working. Any ideas?

The way most tags work is that they are a concatenation of information
in a string. The information is as follows:

tagtype + "_" + station + "_" + tagname

Now when the tags are imported on ODBC, they are filtered to only
include tags that begin with the type "XA" using a WHERE statement.

Therefore, the station will ALWAYS start in position four of the
string.

The station can be either 5 or 6 characters long, and will always be
followed by an underscore. This is the reason for the InStr()
function, which won't work.

Would there be another way to accomplish this?

Next, you mentioned References. I have checked the list, and none are
missing. I have also checked an extra one, quit, reopened Access and
unchecked it to try and get Access to reload these references.
Neither of these options has worked either.

Since none of your WAGS have helped, you requested I include a list of
all types of tags. Here goes:

Tags Following the Previously Described Format:
(I have only included the types since the station and name have been
described previously)

Tags Not Following Format:

Hexadecimal strings
Various strings of letters, colons, and numbers

Again, thank you for your help, and hopefully now you may have a
better idea of what might be wrong.

Nick- Hide quoted text -

- Show quoted text -

After further debugging, I have found that it is not the InStr()
function, it is the subtraction following it that causes the error.

This SQL works:

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT
P.[tag],
Mid(P.[tag],4,InStr(4,P.[tag],'_',1)) AS Station,
Mid(P.[tag],InStr(4,P.[tag],'_',1)+1) AS TagName,
P.[PointID]
FROM
pipointlocal AS P;

While this doesn't:

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT
P.[tag],
Mid(P.[tag],4,InStr(4,P.[tag],'_',1)-4) AS Station,
Mid(P.[tag],InStr(4,P.[tag],'_',1)+1) AS TagName,
P.[PointID]
FROM
pipointlocal AS P;

I am thoroughly confused!!!!

Nick- Hide quoted text -

- Show quoted text -

Ok I found the problem...

1 tag in more than 150000 was missing a second underscore

i will rewrite the code to check for this and check to see if this
fixes it tomorrow

nick