How to use this Query in Excel? Is the query wrong or just Excel not powerful enough?

SELECT Orders.PO_No, Orders.Order_No
FROM DBase.dbo.Orders Orders
Where Orders.PO_No = (Select Top 1 Orders.PO_No From Orders Where Order_No = ?)

if i specifically use

SELECT Orders.PO_No, Orders.Order_No
FROM DBase.dbo.Orders Orders
Where Orders.PO_No = (Select Top 1 Orders.PO_No From Orders Where Order_No = '555555')

It returns the value how can i write this query using a user entered value in Cell $A$1 on Sheet1 I've tried the ? in both the Command Text Under Definition AND inside MSQuery under the SQL Statement box.

When using the Command Text Field and the ? I do get a new Parameter Called Parameter1 but when i set the value of Parameter1 to = the cell i get the follow errors:

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

And

[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index

even though I make sure the cell value is 555555

Solution

I came up with the following VBA that works super fast and is easy to change.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        With Range("Table_Query_from_DBase_1[[#Headers],[PO_No]]").ListObject.QueryTable
        .CommandText = Array( _
        "SELECT Orders.PO_No, Orders.Order_No" & Chr(13) & "" & Chr(10) & "FROM DBase.dbo.Orders Orders" & Chr(13) & "" & Chr(10) & "Where Orders.PO_No =(Select Top 1 Orders.PO_No From Orders Where Order_No = '" & Range("A1").Value & "')" _
                   )
        End With
    End If
End Sub

Anytime a new value is entered into Cell A1 the code will automatically change the CommandText (Select Statement).

Also by using the With command you could include many more things such as having it automatically refresh itself after changing parameter by adding

.Refresh BackgroundQuery:=False

before the End With.

You can change when the CommandText is changed by placing this into a macro assigned to a button or on worksheet refresh or almost an endless amount of variables