Invalid Procedure Call 1

[SQLScholar]

hey all,

I am quite a good SQL coder (in MS SQL) but i am struggling with a query in Access. I have this query.

SELECT right(Task_Detail.Task_Employee_Initials,len(Task_Detail.Task_Employee_Initials)-5) AS Employee, Task_Detail.Task_Booking_Date, Task_Detail.Task_Number, Task_Detail.Task_Hours_Booked, Task_Detail.Task_Month, Task_Detail.Task_Year
FROM Task_Detail, itstaff
WHERE ((right(Task_Detail.Task_Employee_Initials,len(Task_Detail.Task_Employee_Initials)-5)=ITStaff!Name));

However it seems not to like to join using a derived query (useful message of invalid procedure call)

I have tried doing this with a separate query to do the derived col - this doesnt help.

If however i do the same without a derived col (not what i need) it all works.

Unfortunately this is due to a non normalised DB that i cant change.

So.....

Anyone got any ideas?

Many thanks and TIA

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[Remou]

Is this ITStaff!Name from a form? If so:

Forms!ITStaff![Name]

Name is also a problem because it is a reserved word.

 

[PHV]

Seems that Task_Employee_Initials may have null values ?
I'd try this:

SELECT Mid(D.Task_Employee_Initials & '',6) AS Employee, D.Task_Booking_Date, D.Task_Number, D.Task_Hours_Booked, D.Task_Month, D.Task_Year
FROM Task_Detail AS D, ITStaff AS I
WHERE Mid(D.Task_Employee_Initials & '',6)=I.Name

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SQLScholar]

How quick are you guys

Remou,

No its a table.

PHV,

Perfect - thanks for your help. Access error messages arent as clear as they could be Star

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------