Invalid Procedure Call

[neo651]

I just looked it up and found out that, apparently, we have a few user groups with three words and thus two spaces. In total there are 33 user groups

5 Single Word (0 Spaces)
24 Two Word (1 Space)
4 Three Word (2 Spaces)

[orange]

Ok. That's not the end of the world.
My thinking is this.

You have 11 fields in the data you receive. But, at the moment the only punctuation is spaces. (And I checked and saw that in your sample you have 2 or 3 spaces together in places).

If you get rid of all the 2 and 3 spaces together and make those a single space, then you should have 10 spaces in the data you receive. That is if the UserGroup does not have a space.

So if you count the spaces (after getting rid of the 2 and 3 together) and you have
11, you have a space in the user group name.
If you have 12 spaces then you have 2 spaces in the userGroup name.

So, if you get rid of all the 2 and 3 spaces together and make them a single space.
Then count the number of spaces,
if 10, just put the data into the fields in the table ( I mentioned earlier).

if more than 10, then change all the space to commas.
if 11 , then find the 4th comma and make it a space.
if 12, the find the 4 and 5th comma and make them spaces.

At this point the fields you want are separated by commas.

This can be processed.

Are you following this part of working thru the data?

NOTE: I will rework the code and have updated the approach from what is written here.

[orange]

I've reworked the vba code and am returning your data base with a working example.
I added 2 records to your table.
One has 2 spaces in the usergroup name and one has no spaces in the usergroup name.

There is a form with some info and a button.
Click the button to Parse the data into tblParsedData.
There is code behind the form. It simply calls a procedure
named ParseIt.

ParseIt source code is in module1. Also in module1 is a function called FindTheSpace.

There are comments within the vba code in module1.

I am attaching the revised version of your database in mdb format
SampleDB_V1.mdb

Good luck.

Post if you have any issues or need some clarification.

[neo651]

Wow, that's fantastic. I was testing it with some of today's data though and I found 4 issues. One is an issue I'd overlooked because I was initially solving with a criteria in a query. When you initally paste the raw data into a table every record with data in it has a blank record with a single space in it. So you see something like:

--------------------------------------------------------------------------------------
John Doe 5358 ABC Executives 54 27 0 60 1045 98 17035

Jack Jones 5000 Test Two Spaces 45 28 0 61 1046 89 18000

Jerimiah Humperdink 4789 NoSpaceUserGroupName 35 21 0 68 1047 80 18256

------------------------------------------------------------------------------------

The second and third issues relate to names. I seem to have the occasional user who's name includes a middle initial in this format, one space, no period:

John Q Doe

The third issue is the occasional user with a two word last name. Such as:

John Del Mar

Lastly, the workflow of this database is that everyday I need to delete the existing data and paste new data into the table. I realize deleting the old data isn't a lot of steps to do manually but if it's something that also be put on a button in a form or added to the button you've already setup it would be pretty cool. How would I do that?

Thank you.

[orange]

Did you get a chance to look at the function. It's all vba. I put in comments to help understanding the code.

About your questions:

The name with spaces and initial will be a problem. Not that it can't be solved but it will require more code and some testing.

As for the extra record with little or no data, when you populate your raw table, you could look for records that are less than xx characters, and simply delete them.

If you can put say 20 records with some of the issues in the database I sent you, and send it back, I can take a look.

I'm at the cottage at the moment in Ontario.

Are you in same time zone? Eastern Daylight.

[orange]

Here's a new version. It will delete short records automatically.
It also handles names with initials, compound names and UserGroup names
that have 0, 1 or 2 spaces.
There are 4 procedures
ParseIt, FindTheSpace , RemoveShortRecords and PostParseCleanUp.
Each proc has comments and an error handler.
There is a query that shows the records that need to be fixed-- they have initials or compound names.

[neo651]

Sorry I haven't replied. Been busy a work and I had to reformat my HDD. That last code worked great. I just wanted to say thank you. You're the most helpful person I've ever encountered on any forum, anywhere, ever and I really appreciate your help. Thank you again.