Invalid Procedure Call

[neo651]

I've got a query that I use to parse out some names and other data. The brought the parsed data into a new query and whenever I set any sort of criteria I get an error saying "Invalid Procedure Call"



I've searched around and found countless theories on what it could be, none of which seem to work for me, but I haven't found just a fundamental explanation of what the error means and what causes it.

Anyone have any idea?

Thank you.

[orange]

Please show us the code/query that is causing the problem.

[neo651]

What I'm doing is part of an extensive accdb file but the table and queries I'm working with don't have any relationships or dependencies with the rest of the database so I exported the table and queries I'm working on. They're attached here.

The real simple version of what I'm trying to do is parse names and values out of some very raw data that gets pasted into a table and then sort out specific names that exceed certain criteria.

I also removed all sensitive information, mainly names, and replaced them with just a single "John Doe" entry with the same syntax as the others had.

Thank you.

[neo651]

Alright, I've been working on this for over a week now and I've changed everything I could think of in my queries to no avail. I managed to get around the "invalid procedure call" but sometimes i get "query too complex" or any number of different errors depending on what work around I attempt. I feel like I'm on the cusp of figuring out how to do what I want to do.

The error I'm running into now seems to be related to the LTrim and RTrim functions. Attached is a sample database demonstrating the problem. The table one sample record of the raw data I need to parse and the query has 2 columns added. One performs an LTrim and the other parses out the user's first name from the trimmed column. This all works fine, but if you add a criteria it won't open. Take a look.

[orange]

I don't have 2010 so can't open an accdb. I am limited to mdb files.

[neo651]

Sorry about that. Here's an .mdb version.

[orange]

You only have 1 record in the sample. Do you have more test records? Is the format consistent? What do the fields mean?
Why don't you move the data into a structured table so you can do queries etc?

[neo651]

The format is fairly consistent except that the number of leading spaces varies from 0-3.

The data is all related to a report regarding voicemail box usage on our company phone system. The fields refer to, respectively: First Name, Last Name, Mailbox Number, User Group, Number of messages Heard, Unheard, Deleted and Allowed, Oldest Unheard message, Oldest Heard Message and Space Used in KB.
(I'm looking to parse out First Name, Last Name, Total Messages, Unheard Messages and Allowed Messages. Which I'm able to do but as soon as I add a criteria I get an error. The sample database is the simplest form of the database that still presents the error)

And for your last question I'll have to answer a question with a question, what do you mean by a structured table?

Thank you.

[orange]

This snippet may give you a start

Sub parseIt()
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim varr() As String
Dim i As Integer
Set db = CurrentDb
Set rs = db.openrecordset("SELECT  data FROM ShoretelVMRaw")
Do While Not rs.EOF
   Debug.Print rs!Data
   varr() = Split(Trim(rs!Data), " ")
   For i = 0 To UBound(varr)
     Debug.Print i; varr(i)
   Next
rs.movenext
Loop
End Sub

[neo651]

While I don't know VBA specifically the code looks like what I need. Unfortunately I'm very new to Access and SQL so I need to ask, how do I implement this code for my purposes?

[orange]

The code was just a sample to extract the pieces of info into an array. However, you mentioned that the data represents

First Name, Last Name, Mailbox Number, User Group, Number of messages Heard, Unheard, Deleted and Allowed, Oldest Unheard message, Oldest Heard Message and Space Used in KB.

Access does accept field names with spaces, but most people will tell you not to use names with spaces or other special characters.

I'm not sure how often you receive data, nor how often you'd have to do analysis. But I'm recommending that you create a new table in Access. Let's call it tblParsedData, for the moment.
In the table you will have the fields you need, with names adjusted to remove spaces:
FirstName
LastName
MailboxNumber
UserGroup
MHeard
MUnheard
MDeleted
MAllowed
OldestUnheard
OldestHeard
SpaceUsedKB

If you get back to me and want to pursue this, we can work on the code to populate the tblParsedData.

Is your User Group called "ABC Executives" --- if so, that space may be an issue.

Here is some revised code:

Sub parseIt()
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim varr() As String
Dim DataRaw As String
Dim i As Integer
Set db = CurrentDb
'Set rsOut = db.OpenRecordset("tblParseData")
Set rs = db.OpenRecordset("SELECT data FROM ShoretelVMRaw")
Do While Not rs.EOF
Debug.Print rs!Data
DataRaw = rs!Data
For i = 0 To 3
DataRaw = Replace(DataRaw, " ", " ")
Next
varr() = Split(Trim(DataRaw), " ")
For i = 0 To UBound(varr)
If i < 5 Then
Debug.Print i; (varr(i))
Else
Debug.Print i; CLng(varr(i))
End If
Next

'add a record to tblParsedData
'With rsOut
' .AddNew
'!FirstName = varr(0)
'!LastName = varr(1)
'!MailboxNumber = varr(2)
'!UserGroup = varr(3)
'!MHeard = varr(4)
'!MUnheard = varr(5)
'!MDeleted = varr(6)
'!MAllowed = varr(7)
'!OldestUnheard = varr(8)
'!OldestHeard = varr(9)
'!SpaceUsedKB = varr(10)
'End With
rs.movenext
Loop
End Sub

which produces this breakdown of the data into your fields.

0 John
1 Doe
2 5358
3 ABC
4 Executives
5 54
6 27
7 0
8 60
9 1045
10 98
11 17035

I'm getting 1 more field broken out than you have described, that's why I'm asking if the UserGroup is "ABC Executives".

[neo651]

Wow. Let me just take a moment to say thank you for being so helpful.

Regarding the "ABC Executives" that is a single field and now that I think of it that is going to be a sticking point. The "ABC" in that example is a stand-in for a location prefix, but sometimes the field is populated with the location name spelled out and there are a few groups that are just descriptive. So here's some example of what the user group field could be:

NH Executive
Boston Staff
Operator

Regarding the code, looks even better than before. But I'm so new to Access that I don't even know where I would have to paste that or how to get it to execute.

Thank you.

[orange]

How much influence do you have on how the data is received?
If the data had a comma (,) separating the fields, you could easily import the data into a table with all the fields identified. That would be the easiest approach.

[neo651]

I have no influence. It's just copied and pasted from a page on the web interface of our VoIP management system.

[orange]

Hmmm.
How many of the user Groups would have an embedded space?