Excel VBA and ADODB connection to SQL server

[glyn2w]

I have managed to create some code that takes my SQL (in a cell on my spreadsheet) and it run the SQL on my server. But the SQL I have isn't working. I'm wondering if it's a problem with the difference in languages (JET) etc. I ahve tried both the following but both seem to fail. Cany anyone suggest other options?

This one works:

SELECT Inpatient_Ex.HospitalProviderSpellNumber, Inpatient_Ex.SpellStartDateTime, Inpatient_Ex.SpellEndDateTime, LuCustomisableDate.StartDate, LuCustomisableDate.EndDate
FROM Inpatient_Ex INNER JOIN LuCustomisableDate ON (Inpatient_Ex.SpellStartDateTime<LUCUSTOMISABLEDATE.ENDDATE) (Inpatient_Ex.SpellEndDateTime AND>=LuCustomisableDate.StartDate)
WHERE ((Inpatient_Ex.EpisodeNumber)='1')

This one fails:

SELECT Inpatient_Ex.HospitalProviderSpellNumber, Inpatient_Ex.SpellStartDateTime, Inpatient_Ex.SpellEndDateTime, LuCustomisableDate.StartDate, LuCustomisableDate.EndDate, Iif(LuCustomisableDate.StartDate<INPATIENT_EX.SPELLSTARTDATETIME,INPATIENT_EX.SPELLSTARTDATETIME,LUCUSTOMISABLEDATE.STARTDATE) AS adjStart
 FROM Inpatient_Ex INNER JOIN LuCustomisableDate ON (Inpatient_Ex.SpellStartDateTime<LUCUSTOMISABLEDATE.ENDDATE) (Inpatient_Ex.SpellEndDateTime AND>=LuCustomisableDate.StartDate)
WHERE ((Inpatient_Ex.EpisodeNumber)='1')

This fails too:

SELECT Inpatient_Ex.HospitalProviderSpellNumber, Inpatient_Ex.SpellStartDateTime, Inpatient_Ex.SpellEndDateTime, LuCustomisableDate.StartDate, LuCustomisableDate.EndDate, CASE WHEN LuCustomisableDate.StartDate<Inpatient_Ex.SpellStartDateTime THEN Inpatient_Ex.SpellStartDateTime ELSE LuCustomisableDate.StartDate As adjStart
FROM Inpatient_Ex INNER JOIN LuCustomisableDate ON (Inpatient_Ex.SpellStartDateTime<LuCustomisableDate.EndDate) AND (Inpatient_Ex.SpellEndDateTime>=LuCustomisableDate.StartDate)
WHERE ((Inpatient_Ex.EpisodeNumber)='1')

My Connection String is:

myConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=InformationDB;" & _
    "Data Source=PURPLE2"

 

[Kyle123]

You don't have a FROM clause

The syntax on your second failure is waaay out and it doesn't have a from clause either

 

[glyn2w]

Sorry the thread seems to be cutting it off!

SELECT Inpatient_Ex.HospitalProviderSpellNumber, Inpatient_Ex.SpellStartDateTime, Inpatient_Ex.SpellEndDateTime, LuCustomisableDate.StartDate, LuCustomisableDate.EndDate, CASE WHEN LuCustomisableDate.StartDate<Inpatient_Ex.SpellStartDateTime THEN Inpatient_Ex.SpellStartDateTime ELSE LuCustomisableDate.StartDate As adjStart </SPAN>
FROM Inpatient_Ex INNER JOIN LuCustomisableDate ON (Inpatient_Ex.SpellStartDateTime<LuCustomisableDate.EndDate) AND (Inpatient_Ex.SpellEndDateTime>=LuCustomisableDate.StartDate)</SPAN>
WHERE ((Inpatient_Ex.EpisodeNumber)='1')</SPAN>

 

[Kyle123]

Does it actually execute in ssms?

 

[glyn2w]

Yes when using the CASE WHEN but not when using the IIF function

 

[Kyle123]

What error are you getting?

p.s the board doesn't like your < signs, it thinks they're html tags

 

[glyn2w]

Aaaah - can you tell I'm a new poster!

Managed to get this to work actually. I read somewhere that the ADODB connection wouldn't let me use CASE WHEN so I swtiched to IIF. But the IIF didn't like the 'less than' sign. So back to CASE WHEN and (when I ENDed it properly) it seems to be OK.

Thanks for your help Kyle123

 

[Colin Legg]

IIF() isn't a valid native function in MS SQL Server. I can't see your complete CASE statement because it's been cut off - why are you trying to change it to an IIF()?

 

[Kyle123]

That's why I asked if your SQL ran in ssms, I wouldn't have thought it would have without END. Glad you got it sorted