Invalid Procedure Call

[nick.terry]

Hi all,

I am a veteran reader, but a first time poster, so please bear with me
and if you need more info or something, just let me know.

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

 

[Klatuu]

This may or may not be the problem you are having, but there is a chance it
could be. You have a field named tag. Tag is a reserved word. It is a name
of a property. Most controls and the form and report have tag properties.
You have done the correct thing bu putting the name in brackets. That is
indented to distinguish it from a reserved word, but I have seen instances
where it still caused a problem.

The specific case I recall was not with tag, but with a field name date.
Even with brackets it was causing an incorrect calculation because the
original developer was trying to use the field named date in an expression
with the Date function.

So, the short of it is, change the field name. Reserved words should not be
used as names.

 

[nick.terry]

This may or may not be the problem you are having, but there is a chance it
could be. You have a field named tag. Tag is a reserved word. It is a name
of a property. Most controls and the form and report have tag properties.
You have done the correct thing bu putting the name in brackets. That is
indented to distinguish it from a reserved word, but I have seen instances
where it still caused a problem.

The specific case I recall was not with tag, but with a field name date.
Even with brackets it was causing an incorrect calculation because the
original developer was trying to use the field named date in an expression
with the Date function.

So, the short of it is, change the field name. Reserved words should not be
used as names.
--
Dave Hargis, Microsoft Access MVP

Hi all,

I am a veteran reader, but a first time poster, so please bear with me
and if you need more info or something, just let me know.

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!

Nick- Hide quoted text -

- Show quoted text -

Thanks for replying Dave!

I was not aware that tag was a reserved word! I will look into this,
although, as I said previously it worked previously. I have been
doing a little bit more trouble shooting and have now narrowed the
problem down to the first InStr() function in the SQL statement.

This code does not work:

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;

This code does work:

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

Any ideas?

Nick

 

[Klatuu]

Okay, I did not notice that previously. It does look okay, but I am
wondering what will happen when when [pipointlocal].[tag] is either Null or
not long enough for the the other functions using the value.
--
Dave Hargis, Microsoft Access MVP

This may or may not be the problem you are having, but there is a chance it
could be. You have a field named tag. Tag is a reserved word. It is a name
of a property. Most controls and the form and report have tag properties.
You have done the correct thing bu putting the name in brackets. That is
indented to distinguish it from a reserved word, but I have seen instances
where it still caused a problem.

The specific case I recall was not with tag, but with a field name date.
Even with brackets it was causing an incorrect calculation because the
original developer was trying to use the field named date in an expression
with the Date function.

So, the short of it is, change the field name. Reserved words should not be
used as names.
--
Dave Hargis, Microsoft Access MVP

Hi all,

I am a veteran reader, but a first time poster, so please bear with me
and if you need more info or something, just let me know.

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!

Nick- Hide quoted text -

- Show quoted text -

Thanks for replying Dave!

I was not aware that tag was a reserved word! I will look into this,
although, as I said previously it worked previously. I have been
doing a little bit more trouble shooting and have now narrowed the
problem down to the first InStr() function in the SQL statement.

This code does not work:

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;

This code does work:

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

Any ideas?

Nick

 

[nick.terry]

Okay, I did not notice that previously. It does look okay, but I am
wondering what will happen when when [pipointlocal].[tag] is either Null or
not long enough for the the other functions using the value.
--
Dave Hargis, Microsoft Access MVP

This may or may not be the problem you are having, but there is a chance it
could be. You have a field named tag. Tag is a reserved word. It is a name
of a property. Most controls and the form and report have tag properties.
You have done the correct thing bu putting the name in brackets. That is
indented to distinguish it from a reserved word, but I have seen instances
where it still caused a problem.
The specific case I recall was not with tag, but with a field name date.
Even with brackets it was causing an incorrect calculation because the
original developer was trying to use the field named date in an expression
with the Date function.
So, the short of it is, change the field name. Reserved words should not be
used as names.
--
Dave Hargis, Microsoft Access MVP
:
Hi all,
I am a veteran reader, but a first time poster, so please bear with me
and if you need more info or something, just let me know.
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- Hide quoted text -
- Show quoted text -

Thanks for replying Dave!

I was not aware that tag was a reserved word! I will look into this,
although, as I said previously it worked previously. I have been
doing a little bit more trouble shooting and have now narrowed the
problem down to the first InStr() function in the SQL statement.

This code does not work:

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;

This code does work:

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

Any ideas?

Nick- Hide quoted text -

- Show quoted text -

Dave,

No tags are null, i have checked, as well as filtered when importing
from the raw data, to only include one type.

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.

Nick

 

[Klatuu]

Sorry, Nick, I am not seeing the problem. This can also be caused by an
incorrect name reference, so even though that seems obvious, I would ensure
the names are all spelled correctly.
--
Dave Hargis, Microsoft Access MVP

Okay, I did not notice that previously. It does look okay, but I am
wondering what will happen when when [pipointlocal].[tag] is either Null or
not long enough for the the other functions using the value.
--
Dave Hargis, Microsoft Access MVP

This may or may not be the problem you are having, but there is a chance it
could be. You have a field named tag. Tag is a reserved word. It is a name
of a property. Most controls and the form and report have tag properties.
You have done the correct thing bu putting the name in brackets. That is
indented to distinguish it from a reserved word, but I have seen instances
where it still caused a problem.

The specific case I recall was not with tag, but with a field name date.
Even with brackets it was causing an incorrect calculation because the
original developer was trying to use the field named date in an expression
with the Date function.

So, the short of it is, change the field name. Reserved words should not be
used as names.

:
Hi all,

I am a veteran reader, but a first time poster, so please bear with me
and if you need more info or something, just let me know.

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!

Nick- Hide quoted text -

- Show quoted text -

Thanks for replying Dave!

I was not aware that tag was a reserved word! I will look into this,
although, as I said previously it worked previously. I have been
doing a little bit more trouble shooting and have now narrowed the
problem down to the first InStr() function in the SQL statement.

This code does not work:

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;

This code does work:

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

Any ideas?

Nick- Hide quoted text -

- Show quoted text -

Dave,

No tags are null, i have checked, as well as filtered when importing
from the raw data, to only include one type.

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.

Nick

 

[nick.terry]

Sorry, Nick, I am not seeing the problem. This can also be caused by an
incorrect name reference, so even though that seems obvious, I would ensure
the names are all spelled correctly.
--
Dave Hargis, Microsoft Access MVP

Okay, I did not notice that previously. It does look okay, but I am
wondering what will happen when when [pipointlocal].[tag] is either Null or
not long enough for the the other functions using the value.
--
Dave Hargis, Microsoft Access MVP
:
This may or may not be the problem you are having, but there is a chance it
could be. You have a field named tag. Tag is a reserved word. It is a name
of a property. Most controls and the form and report have tag properties.
You have done the correct thing bu putting the name in brackets. That is
indented to distinguish it from a reserved word, but I have seen instances
where it still caused a problem.
The specific case I recall was not with tag, but with a field name date.
Even with brackets it was causing an incorrect calculation because the
original developer was trying to use the field named date in an expression
with the Date function.
So, the short of it is, change the field name. Reserved words should not be
used as names.
--
Dave Hargis, Microsoft Access MVP
:
Hi all,
I am a veteran reader, but a first time poster, so please bear with me
and if you need more info or something, just let me know.
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- Hide quoted text -
- Show quoted text -
Thanks for replying Dave!
I was not aware that tag was a reserved word! I will look into this,
although, as I said previously it worked previously. I have been
doing a little bit more trouble shooting and have now narrowed the
problem down to the first InStr() function in the SQL statement.
This code does not work:
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;
This code does work:
INSERT INTO
TagTable ( PointTag, Station, TagName , PointID )
SELECT
[pipointlocal].[tag], Mid([pipointlocal].[tag],4,6) AS Station,
Mid([pipointlocal].[tag],InStr(4,[pipointlocal].[tag],'_')+1) AS
TagName,
[pipointlocal].[PointID]
FROM
pipointlocal;
Any ideas?
Nick- Hide quoted text -
- Show quoted text -

Dave,

No tags are null, i have checked, as well as filtered when importing
from the raw data, to only include one type.

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.

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

 

[Klatuu]

Well, at least you found it. Without seeing some data examples, I would not
not be able to venture a guess, but I think we are back to the code vs the
data situation.
--
Dave Hargis, Microsoft Access MVP

Sorry, Nick, I am not seeing the problem. This can also be caused by an
incorrect name reference, so even though that seems obvious, I would ensure
the names are all spelled correctly.
--
Dave Hargis, Microsoft Access MVP

Okay, I did not notice that previously. It does look okay, but I am
wondering what will happen when when [pipointlocal].[tag] is either Null or
not long enough for the the other functions using the value.

:
This may or may not be the problem you are having, but there is a chance it
could be. You have a field named tag. Tag is a reserved word. It is a name
of a property. Most controls and the form and report have tag properties.
You have done the correct thing bu putting the name in brackets. That is
indented to distinguish it from a reserved word, but I have seen instances
where it still caused a problem.

The specific case I recall was not with tag, but with a field name date.
Even with brackets it was causing an incorrect calculation because the
original developer was trying to use the field named date in an expression
with the Date function.

So, the short of it is, change the field name. Reserved words should not be
used as names.

:
Hi all,

I am a veteran reader, but a first time poster, so please bear with me
and if you need more info or something, just let me know.

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!

Nick- Hide quoted text -

- Show quoted text -

Thanks for replying Dave!

I was not aware that tag was a reserved word! I will look into this,
although, as I said previously it worked previously. I have been
doing a little bit more trouble shooting and have now narrowed the
problem down to the first InStr() function in the SQL statement.

This code does not work:

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;

This code does work:

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

Any ideas?

Nick- Hide quoted text -

- Show quoted text -

No tags are null, i have checked, as well as filtered when importing
from the raw data, to only include one type.

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.

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

 

[nick.terry]

Well, at least you found it. Without seeing some data examples, I would not
not be able to venture a guess, but I think we are back to the code vs the
data situation.
--
Dave Hargis, Microsoft Access MVP

Sorry, Nick, I am not seeing the problem. This can also be caused by an
incorrect name reference, so even though that seems obvious, I would ensure
the names are all spelled correctly.
--
Dave Hargis, Microsoft Access MVP
:
Okay, I did not notice that previously. It does look okay, but I am
wondering what will happen when when [pipointlocal].[tag] is either Null or
not long enough for the the other functions using the value.
--
Dave Hargis, Microsoft Access MVP
:
This may or may not be the problem you are having, but there is a chance it
could be. You have a field named tag. Tag is a reserved word. It is a name
of a property. Most controls and the form and report have tag properties.
You have done the correct thing bu putting the name in brackets. That is
indented to distinguish it from a reserved word, but I have seen instances
where it still caused a problem.
The specific case I recall was not with tag, but with a field name date.
Even with brackets it was causing an incorrect calculation because the
original developer was trying to use the field named date in an expression
with the Date function.
So, the short of it is, change the field name. Reserved words should not be
used as names.
--
Dave Hargis, Microsoft Access MVP
:
Hi all,
I am a veteran reader, but a first time poster, so please bear with me
and if you need more info or something, just let me know.
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- Hide quoted text -
- Show quoted text -
Thanks for replying Dave!
I was not aware that tag was a reserved word! I will look into this,
although, as I said previously it worked previously. I have been
doing a little bit more trouble shooting and have now narrowed the
problem down to the first InStr() function in the SQL statement.
This code does not work:
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;
This code does work:
INSERT INTO
TagTable ( PointTag, Station, TagName , PointID )
SELECT
[pipointlocal].[tag], Mid([pipointlocal].[tag],4,6) AS Station,
Mid([pipointlocal].[tag],InStr(4,[pipointlocal].[tag],'_')+1) AS
TagName,
[pipointlocal].[PointID]
FROM
pipointlocal;
Any ideas?
Nick- Hide quoted text -
- Show quoted text -
Dave,
No tags are null, i have checked, as well as filtered when importing
from the raw data, to only include one type.
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.
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 -

Thanks anyways dave, I am just going to find a nonelegant workaround
probably using Left(Mid(...,InStr()),InStr(...,Mid(...,InStr()),...)).

Ouch that hurts to even look at ... but hey if it works im no critic

nick

 

[nick.terry]

Well, at least you found it. Without seeing some data examples, I would not
not be able to venture a guess, but I think we are back to the code vs the
data situation.

Sorry, Nick, I am not seeing the problem. This can also be caused by an
incorrect name reference, so even though that seems obvious, I would ensure
the names are all spelled correctly.
--
Dave Hargis, Microsoft Access MVP
:
Okay, I did not notice that previously. It does look okay, but I am
wondering what will happen when when [pipointlocal].[tag] is either Null or
not long enough for the the other functions using the value.
--
Dave Hargis, Microsoft Access MVP
:
This may or may not be the problem you are having, but there is a chance it
could be. You have a field named tag. Tag is a reserved word. It is a name
of a property. Most controls and the form and report have tag properties.
You have done the correct thing bu putting the name in brackets. That is
indented to distinguish it from a reserved word, but I have seen instances
where it still caused a problem.
The specific case I recall was not with tag, but with a field name date.
Even with brackets it was causing an incorrect calculation because the
original developer was trying to use the field named date in an expression
with the Date function.
So, the short of it is, change the field name. Reserved words should not be
used as names.
--
Dave Hargis, Microsoft Access MVP
:
Hi all,
I am a veteran reader, but a first time poster, so please bear with me
and if you need more info or something, just let me know.
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- Hide quoted text -
- Show quoted text -
Thanks for replying Dave!
I was not aware that tag was a reserved word! I will look into this,
although, as I said previously it worked previously. I have been
doing a little bit more trouble shooting and have now narrowed the
problem down to the first InStr() function in the SQL statement.
This code does not work:
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;
This code does work:
INSERT INTO
TagTable ( PointTag, Station, TagName , PointID )
SELECT
[pipointlocal].[tag], Mid([pipointlocal].[tag],4,6) AS Station,
Mid([pipointlocal].[tag],InStr(4,[pipointlocal].[tag],'_')+1) AS
TagName,
[pipointlocal].[PointID]
FROM
pipointlocal;
Any ideas?
Nick- Hide quoted text -
- Show quoted text -
Dave,
No tags are null, i have checked, as well as filtered when importing
from the raw data, to only include one type.
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.
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 -

Thanks anyways dave, I am just going to find a nonelegant workaround
probably using Left(Mid(...,InStr()),InStr(...,Mid(...,InStr()),...)).

Ouch that hurts to even look at ... but hey if it works im no critic

nick- Hide quoted text -

- Show quoted text -

Ok that didn't work ... gave me the same original error as before ...
must be something about what its doing, not how its doing it ... ill
take a look at the data again.

nick

 

[nick.terry]

Well, at least you found it. Without seeing some data examples, I would not
not be able to venture a guess, but I think we are back to the code vs the
data situation.
--
Dave Hargis, Microsoft Access MVP
:
Sorry, Nick, I am not seeing the problem. This can also be caused by an
incorrect name reference, so even though that seems obvious, I would ensure
the names are all spelled correctly.
--
Dave Hargis, Microsoft Access MVP
:
Okay, I did not notice that previously. It does look okay, but I am
wondering what will happen when when [pipointlocal].[tag] is either Null or
not long enough for the the other functions using the value.
--
Dave Hargis, Microsoft Access MVP
:
This may or may not be the problem you are having, but there is a chance it
could be. You have a field named tag. Tag is a reserved word. It is a name
of a property. Most controls and the form and report have tag properties.
You have done the correct thing bu putting the name in brackets. That is
indented to distinguish it from a reserved word, but I have seen instances
where it still caused a problem.
The specific case I recall was not with tag, but with a field name date.
Even with brackets it was causing an incorrect calculation because the
original developer was trying to use the field named date in an expression
with the Date function.
So, the short of it is, change the field name. Reserved words should not be
used as names.
--
Dave Hargis, Microsoft Access MVP
:
Hi all,
I am a veteran reader, but a first time poster, so please bear with me
and if you need more info or something, just let me know.
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- Hide quoted text -
- Show quoted text -
Thanks for replying Dave!
I was not aware that tag was a reserved word! I will look into this,
although, as I said previously it worked previously. I have been
doing a little bit more trouble shooting and have now narrowed the
problem down to the first InStr() function in the SQL statement.
This code does not work:
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;
This code does work:
INSERT INTO
TagTable ( PointTag, Station, TagName , PointID )
SELECT
[pipointlocal].[tag], Mid([pipointlocal].[tag],4,6) AS Station,
Mid([pipointlocal].[tag],InStr(4,[pipointlocal].[tag],'_')+1) AS
TagName,
[pipointlocal].[PointID]
FROM
pipointlocal;
Any ideas?
Nick- Hide quoted text -
- Show quoted text -
Dave,
No tags are null, i have checked, as well as filtered when importing
from the raw data, to only include one type.
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.
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 -

Thanks anyways dave, I am just going to find a nonelegant workaround
probably using Left(Mid(...,InStr()),InStr(...,Mid(...,InStr()),...)).

Ouch that hurts to even look at ... but hey if it works im no critic

nick- Hide quoted text -

- Show quoted text -

Ok that didn't work ... gave me the same original error as before ...
must be something about what its doing, not how its doing it ... ill
take a look at the data again.

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