Using functions

[MikeB]

I need some help.

First, what is it with MS Access 2007? Everything in there seems to be
so different from Access 2003, I can barely find my way around. I've
had to leave my old computer with Access 2007 and go back to using my
old computer with Access 2003 to just make headway with my project.

Now to my real problem.

I'm importing this huge XML file. I'm relying on Access to create the
fields in the DB when it imports the file. However, in many instances
Access creates a text field (max 255 chars) when the contents of the
data requires a larger Memo field.

To fix this is a 2-step process for me. I import the file, then look
at the table "Import Errors" that Access creates to change the fields
to the correct type and then I import the file again.

So I want to write a query to summarize the tables and fields that
have Import Errors.

This is the definition of the Import Errors table in Access 2003 (In
2007, it is completely different, doesn't have the table or field
names and seems almost useless).

Field Name Data Type Sample Data
---------------- ---------------
-----------------------------------------------------------------
XML Text Memo Table Group, row 776, column Description
Error Message Memo Field truncation
Result Memo -blank-
Timestamp Date/Time 10/16/2007 11:00:35 AM

What I really need is a different table with the following fields:

Table
Column
Error message

So I started trying to parse the" XML Text" field in a query based on
the "Import Errors" table.

Question: What is the difference between the left and left$, Right and
right$ and mid and mid$ functions?

This gets me the required fields:

SELECT Left([XML Text],InStr(1,[XML Text],",")-1) AS

,
Right([XML Text],(Len([XML Text])-InStrRev([XML Text],",")-8)) AS
[Column], ImportErrors.[Error Message]
FROM ImportErrors;

But I can't group the above.

If I try:

SELECT Left([XML Text],InStr(1,[XML Text],",")-1) AS

,
Right([XML Text],(Len([XML Text])-InStrRev([XML Text],",")-8)) AS
[Column], Count(ImportErrors.[Error Message]) AS [CountOfError
Message], Count(ImportErrors.[Error Message]) AS [CountOfError
Message1]
FROM ImportErrors
GROUP BY Left([XML Text],InStr(1,[XML Text],",")-1), Right([XML Text],
(Len([XML Text])-InStrRev([XML Text],",")-8));

I get "Invalid Procedure call" pop-up with only an "OK" button to
dismiss.

I have tried to create a query for the table and then run the grouping
query off that query table, but I get the same error.

Any advice?

Also, I'm so out of my depth with Access 2007, where do I start? I've
spent such a lot of time to be able to use Access 2003 and now it
seems I have to start from scratch again?

Thanks