SELECT within a SELECT query

[marcyjj]

I am using the WHERE IN within my query but for some reason it is not giving me the results as expected. Here is my code...

strSQL = "SELECT dbo_workitem.id, dbo_workitem.version, dbo_workitem.number, dbo_workitem.action, dbo_workitem.title, dbo_workitem.created_on, dbo_workitem.modified_on, dbo_worker.name, dbo_workitem.type_lookup_id, dbo_workitem.urgency_lookup_id, dbo_workitem.category_tree_value " _
        & "FROM dbo_worker INNER JOIN dbo_workitem ON dbo_worker.id = dbo_workitem.assigned_to_worker_id " _
        & "WHERE dbo_workitem.number IN (SELECT dbo_workitem.number FROM dbo_workitem WHERE modified_on BETWEEN #" & strStart & "# AND #" & strEnd & "# AND dbo_workitem.category_tree_value LIKE 'SCFH/ITU*') " _
        & "ORDER BY dbo_workitem.number, dbo_workitem.version;"

When I run the select query from within brackets separately then I get a full list of results but using it with the IN statement leaves me sort of results. For example this result should be included but is not, It contains the right criteria...

number - modified_on - category_tree_value
92911 - 02/01/2015 11:36:59 - SCFH/ITU\Operations\PC Support
92911 - 05/01/2015 12:52:53 - SCFH/ITU\Operations\PC Support
92911 - 13/01/2015 02:00:56 - SCFH/ITU\Operations\PC Support

I also end up getting dates not within the range requested. So modified_on between #01/01/2015# and #31/01/2015# brings up dates in 2014.

Do you have any suggestions? Many thanks...

 

[Andrzejek]

Did you try it without the inner Select:

strSQL = "SELECT dbo_workitem.id, dbo_workitem.version, dbo_workitem.number, dbo_workitem.action, dbo_workitem.title, dbo_workitem.created_on, dbo_workitem.modified_on, dbo_worker.name, dbo_workitem.type_lookup_id, dbo_workitem.urgency_lookup_id, dbo_workitem.category_tree_value " _
        & "FROM dbo_worker INNER JOIN dbo_workitem ON dbo_worker.id = dbo_workitem.assigned_to_worker_id " _
        & "WHERE [s]dbo_workitem.number IN (SELECT dbo_workitem.number FROM dbo_workitem WHERE[/s] modified_on BETWEEN #" & strStart & "# AND #" & strEnd & "# AND dbo_workitem.category_tree_value LIKE 'SCFH/ITU*'[s])[/s] " _
        & "ORDER BY dbo_workitem.number, dbo_workitem.version;"

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[marcyjj]

I have thanks but record 92911 still does not appear in the result.

 

[kray4660]

Be sure to check the regional settings on your computer dd/mm/yyyy and not mm/dd/yyyy.

 

[marcyjj]

That is set as DD/MM/YYYY

 

[Andrzejek]

The record 92911 - does the [tt]dbo_worker.id = dbo_workitem.assigned_to_worker_id [/tt]?
And what are the values for [tt]strStart[/tt] and [tt]strEnd[/tt] ?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.