help me fix this SQL query!

[kita__n]

Ok, here's the query that isnt working the right way. What I'm intersted to do is to be able to type in the last name of the person or the first name and bring up ONLY that persons work orders! What is happening right now is that it pulls out all the CarftPerson!

SELECT WorkOrder.[WorkOrder#], WorkOrder.Craft, WorkOrder.DateRecieved, WorkOrder.DateCompleted, WorkOrder.FollowUpComments, WorkOrder.CraftPerson

FROM WorkOrder

GROUP BY WorkOrder.[WorkOrder#], WorkOrder.Craft, WorkOrder.DateRecieved, WorkOrder.DateCompleted, WorkOrder.FollowUpComments, WorkOrder.CraftPerson, [Enter the Name of the CraftPerson]

HAVING (((WorkOrder.CraftPerson) Like [Enter Name of the CraftPerson]))

ORDER BY WorkOrder.CraftPerson, WorkOrder.[WorkOrder#], WorkOrder.Craft, WorkOrder.DateRecieved, WorkOrder.DateCompleted, WorkOrder.FollowUpComments;

Thankyou

 

[The_Doc_Man]

You included:

GROUP BY WorkOrder.[WorkOrder#], WorkOrder.Craft, WorkOrder.DateRecieved, WorkOrder.DateCompleted, WorkOrder.FollowUpComments, WorkOrder.CraftPerson, [Enter the Name of the CraftPerson]

followed by


HAVING (((WorkOrder.CraftPerson) Like [Enter Name of the CraftPerson]))

I believe that the [Enter the Name of the CraftPerson] in the GROUP BY clause is not correct. It is confusing the issue. Take it out. Rely on the similar parameter in the HAVING clause. If the HAVING clause works correctly, the query will ALREADY be grouping on .CraftPerson.

Also, I suspect your syntax is hacking you. If this name is a person's name, then you need a text-like syntax to do the LIKE clause correctly:

HAVING WorkOrder.CraftPerson LIKE """*" & [Enter Name of the CraftPerson] & "*"""

I would also remove the .FollowUpComments from the GROUP BY section. Simplifies the grouping. And given the other items in your list, I would venture to say that the other fields will provide a good enough sort.

In fact, if you don't have two folks with the same name as the one that was entered, you can get rid of the GROUP BY .CraftPerson entry, too. The HAVING clause will give you the simplest group possible.

What bothers me is that if you ever have two similar names, you won't even see them in name order. You'll see them in the order of the work order numbers first. Is that what you really wanted?

 

[RV]

Additional to DocMan's reply;

You don't even need a GROUP BY.
Replace your HAVING clause by a WHERE clause:

WHERE WorkOrder.CraftPerson LIKE "*" & [Enter Name of the CraftPerson] & "*"

(DocMan, why so many double quotes??)

It's best never to use HAVING clauses as they are more slowly and require grouping.
Keep it simple and fast..

RV

 

[kita__n]

hi DOC and RV

I tried both of your solutions and both times it gives me an error "syntax error"
These are the two eg:

(DOC'S SUGGESTION)

SELECT WorkOrder.[WorkOrder#], WorkOrder.Craft, WorkOrder.DateRecieved, WorkOrder.DateCompleted, WorkOrder.FollowUpComments, WorkOrder.CraftPerson
FROM WorkOrder
GROUP By WorkOrder.CraftPerson, WorkOrder.[WorkOrder#], WorkOrder.Craft, WorkOrder.DateRecieved, WorkOrder.DateCompleted
HAVING WorkOrder.CraftPerson LIKE """*" [Enter Name of the CraftPerson] & "*"""
ORDER BY WorkOrder.CraftPerson, WorkOrder.[WorkOrder#], WorkOrder.Craft, WorkOrder.DateRecieved, WorkOrder.DateCompleted;

(RV'S SUGGESTION)

SELECT WorkOrder.[WorkOrder#], WorkOrder.Craft, WorkOrder.DateRecieved, WorkOrder.DateCompleted, WorkOrder.FollowUpComments, WorkOrder.CraftPerson
FROM WorkOrder
WHERE WorkOrder.CraftPerson LIKE "*" [Enter Name of the CraftPerson] & "*"
ORDER BY WorkOrder.CraftPerson, WorkOrder.[WorkOrder#], WorkOrder.Craft, WorkOrder.DateRecieved, WorkOrder.DateCompleted, WorkOrder.FollowUpComments;

Thankyou for you help

 

[Rich]

Why not use a combo box which contains the craftspeoples names, use the selected name as the criteria and you don't need to bother with Like

 

[RV]

You made a typo:

WHERE WorkOrder.CraftPerson LIKE "*" [Enter Name of the CraftPerson] & "*"

should be:

WHERE WorkOrder.CraftPerson LIKE "*" & [Enter Name of the CraftPerson] & "*"

Same for DocMan's clause.

RV

 

[raskew]

Have to agree with Rich--a combo box is the way to go with something like this. With it, the user will be able to view similar names and choose the correct one.

 

[kita__n]

ThankYou All

Thanks a bunch got my problem fixed....and will make sure that i check the typo's little more carefully next time