Invalid Procedure Call (1 Viewer)

[kholm]

I have a table with date, employee name and hours. Then I have a query that separates last name and first name. It is now, date, lastname, firstname and hours. For some reason it is giving me an Invalid Procedure Call when I try to alphabatize the people by last name
LastName: Left$([Employee],InStr(1,[Employee],",")-1)
FirstName: Right$([Employee],Len([Employee])-InStr(1,[Employee],",")-1)

Kristin

 

[RV]

Post your SQL statement (query)

RV

 

[kholm]

SELECT ExportQB.Date, Left$([Employee],InStr(1,[Employee],",")-1) AS LastName, Right$([Employee],Len([Employee])-InStr(1,[Employee],",")-1) AS FirstName, CDbl([hours])*24 AS [Clock Hours]
FROM ExportQB
WHERE (((ExportQB.Type)="Hourly Regular Rate" Or (ExportQB.Type)="Overtime Regular Rate" Or (ExportQB.Type)="Overtime Hourly Rate"));

 

[RV]

Kristin,

>For some reason it is giving me an Invalid Procedure Call when I try to alphabatize the people by last name<

so I guessed your SQL statement isn't complete and most likely is:

SELECT ExportQB.Date, Left(Employee,InStr(1,Employee,",")-1) AS LastName, Right(Employee,Len(Employee)-InStr(1,Employee,",")-1) AS FirstName, CDbl(hours)*24 A
S [Clock Hours]
FROM ExportQB
WHERE (((ExportQB.Type)="Hourly Regular Rate" Or (ExportQB.Type)="Overtime Regular Rate" Or (ExportQB.Type)="Overtime Hourly Rate"))
ORDER BY Left(Employee,InStr(1,Employee,",")-1);

First, don't use Date for a column name as it is a reserved word in Access.
Second, why do you not split up first names and last names (it's a matter of normalizing your database structure)

Third, the "Invalid procedure call" is most likely caused by an Employee name which doesn't have a "," (commasign) in it.....
If so, you've got a real challenge.

Why? Cause it means that:

a) there are rows where Employee is null
b) there are rows which do contain a contamination of lastname and firstname whereas Employee doesn't have a commasign in it
c) there are rows where Employee only contains the firstname part, no commasign
d) there are rows where Employee only contains the firstname part, ended by a commasign
e) there are rows where Employee only contains the lastname part, no commasign
f) there are rows where Employee only contains the lastname part, proceeded by a commasign

Each of these possible occurences have to be taken in account.

You could avoid these chsllenges by splitting up the Employee column.

HTH,

RV

 

[kholm]

That query is supposed to separate the first and last name. It is not done in the orginal importation because that is how quickbooks exports the data.

I am going to try and find the needle in the haystack.

Kristin

 

[RV]

>That query is supposed to separate the first and last name<

Well, it doesn't.
The query only retrieves rows under certain condition in another way the data is stored.
Again, DO Split Employee in sepatare Firstname and Lastname columns, otherwise you could encounter even more sincere problems then you have right now

RV