• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • Last Modified:

InstrRev function not defined...

If I input the query below directly into Access, it works - however if I try to run it inside my vb/vbs app, it fails with the error in the subject...(using ado)


 .Execute "CREATE PROC qryServerMemoryTotals AS SELECT ServerName AS [Server Name], Trim(Mid(MemID,InstrRev(MemID,'e') +1)) AS Slot, cInt(MemSize) AS [Size] FROM tblServer_RAMData WHERE MemSize <> '0' ORDER BY ServerName UNION ALL SELECT Trim(ServerName) & ' Total:' AS [Server Name], '', SUM(cInt(MemSize)) AS [Size] FROM tblServer_RAMData WHERE MemSize <> '0' GROUP BY Trim(ServerName) & ' Total:' ORDER BY [Server Name]"
0
sirbounty
Asked:
sirbounty
  • 8
  • 6
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
Does this work?

.Execute "CREATE PROC qryServerMemoryTotals AS SELECT ServerName AS [Server Name], Trim(Mid(MemID,Len(MemId)-Instr(MemID,'e')-1)) AS Slot, cInt(MemSize) AS [Size] FROM tblServer_RAMData WHERE MemSize <> '0' ORDER BY ServerName UNION ALL SELECT Trim(ServerName) & ' Total:' AS [Server Name], '', SUM(cInt(MemSize)) AS [Size] FROM tblServer_RAMData WHERE MemSize <> '0' GROUP BY Trim(ServerName) & ' Total:' ORDER BY [Server Name]"

BFN,

fp.
0
 
[ fanpages ]IT Services ConsultantCommented:
PS. Which version of MS-Access are you using?
0
 
[ fanpages ]IT Services ConsultantCommented:
"InstrRev" was only introduced in MS-Office 2000 (and Visual Basic 6).
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
sirbountyAuthor Commented:
Using Access 2003 - I believe it's an ADO problem more than likely (as I mentioned it works within Access).
Will try out your post and let you know...
0
 
dancebertCommented:
Access string functions are available to SQL executed in Access.  When you run in VB, the Access string functions don't exist.
0
 
sirbountyAuthor Commented:
dancebert - missed the notif for your post - but could you elaborate why this is not available in ADO?
0
 
sirbountyAuthor Commented:
fp - sorry for the delay.
I've input your first post and the process runs tomorrow morning at 5am EDT.  I'll let you know soon after.
Thanx!
0
 
[ fanpages ]IT Services ConsultantCommented:
OK, Thanks for your update.

However, I would advise running any SQL statement in a test environment prior to deployment into a live production environment...
0
 
sirbountyAuthor Commented:
I don't see any way this could cause problems...
The database is created dynamically each week.  The query either works (or has been in the past - not).
It's not an update query - so what would be the reason for caution?
0
 
[ fanpages ]IT Services ConsultantCommented:
Because I'm a cautious person!

Why would you run any query in a live environment that had not been tested previously?

What if it took 4 hours to complete?

I wasn't aware the database was created dynamically, so it is not a huge problem if the query failed to be created, or to execute, but surely it wouldn't take long to run now to stop a failure before it happened.
0
 
sirbountyAuthor Commented:
Bah!  Forgot to compile it... (don't laugh!).
I'm rerunning it now though - even though it's outside of the normal weekly run, I should still know something within the hour...
0
 
[ fanpages ]IT Services ConsultantCommented:
Is a smile allowed aloud? :)
0
 
sirbountyAuthor Commented:
Sure - you can smile... heh heh...it was kind of funny...

Well, it runs - but it's not producing the output as expected.
I get

[Server Name]   |  [Slot]    |  [Size]

All slots are empty - everything else is populated correctly.
0
 
sirbountyAuthor Commented:
Sample table:

ServerName  |  MemID  |  MemSize  |
<MyServer>  |  Memory Device 0  |  1,024
<MyServer>  |  Memory Device 1  |  1,024
<MyServer>  |  Memory Device 2  |  1,024

Since I'm creating these fields dynamically, the field length is a bit long, thus I 'do' have some white space on the right - perhaps that's the problem here.  I count 10 'blanks' - I'll retry with a (-11) instead...
Give me another half hour or so...
0
 
sirbountyAuthor Commented:
Now getting
ce 4

Shaving to 8... : |
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now