Invalid Argument for Function (1 Viewer)

[ErinL]

Hi Everyone!

I have the following in a query to assign a sequence number to each record:

(SELECT Count(*) FROM
[qryPTLActivity] AS T WHERE T.EmployeeID=[qryPTLActivity].[EmployeeID] AND T.PickDateTime <= [qryPTLActivity].[PickDateTime] AND T.PickAisle=[qryPTLActivity].[PickAisle])

I have used this in other databases without issue but for some reason this time I keep getting an "Invalid Argument for Function" error.

Does anyone see a problem with the SELECT statement above? To me it looks just like the one I have that is working.

Thank you in advance.

 

[Rx_]

Notice the ( in front and end, by chance is this a sub-query in a larger query?
Sometimes the SQL view window exceeds the text limit and adds a carrage return.

Also, if the Count is removed, does it work stand-alone?

 

[CJ_London]

Is you main query a GROUP BY? if so then this subquery won't work - you'll need to remove the GROUP BY.

If you have other aggregations then you need to use another subquery

 

[ErinL]

Thank you for the quick response.

Yes to both of your questions. It is a sub-query in a larger query and without the statement everything runs fine.

I continued to try different things after I posted and found that when I set a sort order on the fields in the grid (EmployeeID, PickDateTime) the query ran and did what it is supposed to.

However, another problem has surfaced...

Many times a selector will selector in an aisle, move to another aisle and then move back to the original aisle all in the same shift. The way I have it written now, the second time the selector goes to the original aisle the counter continues when I actually want it to reset.

Here is an example of how it looks now:

EmployeeID PickDateTime Aisle Sequence
39 1/6/2014 5:32:00PM 71 1
39 1/6/2014 6:45:47PM 71 2
39 1/6/2014 7:12:54PM 72 1
39 1/6/2014 8:15:56PM 71 3 <---Continues sequence

This is what I would like it to do:

EmployeeID PickDateTime Aisle Sequence
39 1/6/2014 5:32:00PM 71 1
39 1/6/2014 6:45:47PM 71 2
39 1/6/2014 7:12:54PM 72 1
39 1/6/2014 8:15:56PM 71 1 <---Want it to reset

Thank you again!!

 

[CJ_London]

With regards the sort order - Sounds like that is a presentation thing - if the dates are not in order, nor will the sequence appear to be(which is in date order)

To solve your next problem - needs some thinking about

 

[CJ_London]

OK here we go, this should do the trick

(SELECT count(*) FROM qryPTLActivity as T where EmployeeID=qryPTLActivity.EmployeeID and PickAisle=qryPTLActivity.PickAisle and PickDateTime<=qryPTLActivity.PickDateTime and PickDateTime>=nz((SELECT max(PickDateTime) from qryPTLActivity as t2 where EmployeeID=qryPTLActivity.EmployeeID and PickAisle<>qryPTLActivity.PickAisle and PickDateTime<qryPTLActivity.PickDateTime),(SELECT min(PickDateTime) from qryPTLActivity as t3 where EmployeeID=qryPTLActivity.EmployeeID and PickAisle=qryPTLActivity.PickAisle)))

 

[ErinL]

Hey! That works perfect!!

Thank you so much for your help! I really appreciate it!

 

[CJ_London]

Happy to help!