asked on

Excel 2007-VBA sql

Hello,
I'm trying to get a big select statement inside a VBA code,
Can you please help, I'm having some hard time.
Not sure IF it's ok to use this kind of Select inside !!

Option Explicit

Dim sh As Worksheet
Dim cnn As adodb.Connection

Public Sub GetSQLData()
Set sh = Sheets("Orders")
Dim rs As New adodb.Recordset
Dim sql As String
Set cnn = New adodb.Connection

cnn.Open "Provider=SQLOLEDB.1;User ID=sa; password=XXXXX!;Initial Catalog=CC;Data Source=sql-Laptop;"

sql = "SELECT Replace (Reference, ',', '') AS[Reference], Caller AS [Requester], CONVERT(varchar,SubTotalAmount) as [Shipping Charges], "
case Tax1Name when 'GST' then Tax1Amount else 0 end as GST , case Tax1Name when 'HST_ON' then Tax1Amount else 0 end as HST_ON , case Tax1Name when 'HST_NS'then Tax1Amount else 0 end as HST_NS , case Tax1Name when 'HST_BC' then Tax1Amount else 0 end as HST_BC , case Tax2Name when 'TVQ' then Tax2Amount else 0 end as TVQ,
CONVERT(varchar,TotalAmount) AS [Total], CONVERT(varchar, FO.OrderDate, 1) AS [OrderDate], CONVERT(varchar,FO.OrderNo) AS [OrderNumber], PickupCompanyName AS [Shipper Name], PickupContact AS [PU Contact], Replace (PickupStreet, ',', '') AS [Shipper Street],PickupCity AS [Shipper CITY],PickupProvince AS [Shipper Province],
PickupPostalCode AS [Shipper Postal Code],DeliveryCompanyName AS [Shipper Name],DeliveryContact AS [DEL Contact],Replace (PickupStreet, ',', '') AS [Delivery Street], DeliveryCity AS [Delivery City],DeliveryProvince AS [Delivery Province],DeliveryPostalCode AS [Delivery Postal Code] From (select InvoiceNumber, orderno
From finalizedordersinvoices where invoicenumber = (select top 1 invoicenumber from finalizedordersinvoices i inner join finalizedorders o on o.orderno= i.orderno where Accountnumber in (45,7428,7429,7432,7433,7865,9500,9600,9601,9602,9603,10012,10013,10015,30001) order by invoicenumber desc)) foi left join finalizedorders fo on fo.orderno = foi.orderno
WHERE AccountNumber in (45,7428,7429,7432,7433,7865,9500,9600,9601,9602,9603,10012,10013,10015,30001) "
'Delete old data
sh.Range("A2:N65000").ClearContents

'Get new data
rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
sh.Range("A2").CopyFromRecordset rs
rs.Close
cnn.Close
End Sub

nutsch

What error do you get?

Do you get the same error if you do a simpler select? Something like
sql="select orderno from finalizedorders"

if you run your sql string in a new query window in SQL Tools, do you get the right result?

Thomas

W.E.B

ASKER

Hello,
I tried a simple Select top 10 * from FinalizedOrders where accountnumber in ..

I get the results, NO ERRORS.

If I run the String in SQL, i get the correct results, NO ERRORS.

thanks

nutsch

Looks like you have an excess double quote at the end of the second line

sql = "SELECT Replace (Reference, ',', '') AS[Reference], Caller AS [Requester], CONVERT(varchar,SubTotalAmount) as [Shipping Charges], "

Can you insert a debug line after the definition of your select, go to the immediate window in the VBA Editor (Ctrl+G), and copy the result into a new sql query in sql? That's usually how I spot issues in the string.

debug line code:

debug.print sql

Open in new window

Thomas

W.E.B

ASKER

Hi Thomas,
thanks for taking the time trying to help,

I tried to take the double quotes out, but , they keep popping up again.

when I try to run the debug, it says Compile error: syntax error.

case Tax1Name when 'GST' then Tax1Amount else 0 end as GST , case Tax1Name when 'HST_ON' then Tax1Amount else 0 end as HST_ON , case Tax1Name when 'HST_NS' then Tax1Amount else 0 end as HST_NS , case Tax1Name when 'HST_BC' then Tax1Amount else 0 end as HST_BC , case Tax2Name when 'TVQ' then Tax2Amount else 0 end as TVQ,

nutsch

Try deleting the line feeds if you have any.

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

W.E.B

ASKER

Perfect,
thank you.for your time and help.

W.E.B

ASKER

thank you very much

nutsch

Glad to help,

Thomas