query parameters - EXCEL 2010

[clayp56]

I have a workbook that contains two sheets with two seperate queries. On the first sheet I have two cells desiginated to accept the values for the parameters. The first sheet accpets the values and populates the table correctly. The second sheet doesn't seem to accept the values and just returns blank rows.

Need help on this one.

Thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

To get help you need to give more precise information.

 

[clayp56]

Ok, sheet1 and sheet2 have different parameterized SQL select statements that populate tables. In sheet1 cells A2 and B2 have been defined for parameter entry for both queries. When I enter dates in the cells sheet1 table is populated with data. Sheet2 does not. It displays blank rows.

 

[Andrew Poulsom]

Sorry, that's still not precise. Perhaps you query on sheet2 is incorrect.

 

[clayp56]

Sorry. the query works fine in SSMS and when I remove the '?'s and replace with dates it retreives and displays the data in EXCEL.

What specific information would you like?

 

[Andrew Poulsom]

It's impossible to provide a solution without seeing the queries and what's in the parameter cells.

 

[clayp56]

Here is the query that works in SSMS and in EXCEL without the parameters. But when I put the '?' in and then put values (01/01/2010) and (01/31/2010) in the parameter cells it doesn't work.

SELECT o.revenue_code_id AS [Revenue Code],
o.id AS [Order #],
m.id AS [Movement id],
m.loaded,
m.move_distance,
so.location_id AS [Org Loc Id],
so.location_name AS [Org Loc Name],
so.city_name AS [Org City],
so.state AS [Org State],
sd.location_id AS [Dest Loc Id],
sd.location_name AS [Dest Loc Name],
sd.city_name AS [Dest City],
sd.state AS [Dest State],
s.actual_arrival AS [Actual Ship Date]
FROM orders o
LEFT OUTER JOIN movement_order mo
ON o.company_id = mo.company_id
AND o.id = mo.order_id
LEFT OUTER JOIN movement m
ON m.company_id = mo.company_id
AND m.id = mo.movement_id
LEFT OUTER JOIN stop s
ON s.company_id = o.company_id
AND s.id = o.shipper_stop_id
LEFT OUTER JOIN stop sd
ON m.company_id = sd.company_id
AND m.dest_stop_id = sd.id
LEFT OUTER JOIN stop so
ON mo.company_id = so.company_id
AND mo.movement_id = so.movement_id
--and so.stop_type = 'PU'
WHERE ( s.actual_arrival BETWEEN ? AND ? )
AND m.move_distance > 150
AND m.status <> 'V'
AND so.stop_type = 'PU'
ORDER BY 1,
2,
3

 

[Andrew Poulsom]

What does the one that does work look like and what do you have in the parameter cells when it works.

 

[clayp56]

I have the same data in the parameter cells. Here is the query

SELECT o.revenue_code_id AS [Revenue Code],
m.loaded,
Sum (m.move_distance) AS [Distance]
FROM orders o
JOIN movement_order mo
ON o.company_id = mo.company_id
AND o.id = mo.order_id
JOIN movement m
ON m.company_id = mo.company_id
AND m.id = mo.movement_id
LEFT OUTER JOIN stop s
ON s.company_id = o.company_id
AND s.id = o.shipper_stop_id
WHERE ( s.actual_arrival BETWEEN '01/01/2010' AND '01/31/2010' )
GROUP BY o.company_id,
o.revenue_code_id,
m.loaded
ORDER BY 1,
2

 

[Andrew Poulsom]

There aren't any question marks in the query you just posted and the one with question marks doesn't have any single quotes.