SQL in VBA

[tkuia]

Hi,
I am updating my data, using SQL from: Data -> "get external data" and it works.
I need to change parameters in the SQL so I decided to program the SQL in VBA.
I record the "get external data" using Macro, and I get the error message:
"Too many line continuations"

What can I do?

Best regards...

[Marcol]

Post your code so we can see the problem!!

[tkuia]

The SQL code:

SELECT gdwh.tbl_MR_Sites.[ConfigID(Site)],
gdwh.tbl_MR_Sites.[ConfigSerialNr(Site)],
gdwh.tbl_MR_Sites.[CountryName(Site)],
gdwh.tbl_MR_Sites.[SSR_Name(Site)],
gdwh.tbl_MR_Sites.[CustomerName(Site)],
gdwh.tbl_MR_Sites.[SiteLocation(Site)],
gdwh.tbl_MR_Sites.[SystemCode(Site)],
gdwh.tbl_MR_Sites.[System(Site)],
gdwh.tbl_MR_Sites.[OperationalStartDate(Site)],
gdwh.tbl_MR_Calls.[CallTypeCode(Call)],
gdwh.tbl_MR_Calls.[NotificationCallType(Call)],
gdwh.tbl_MR_Jobs.[ActivityTypeCode(Job)],
gdwh.tbl_MR_Calls.[CallOpenDate(Call)],
gdwh.tbl_MR_Calls.[CallClosedate(Call)],
gdwh.tbl_MR_Calls.[CustomerComplaint(Call)],
gdwh.tbl_MR_Jobs.[CustomerRepairText(Job)],
gdwh.tbl_MR_Jobs.[RepairText(Job)],
gdwh.tbl_MR_Parts.[QTY(Part)],
gdwh.tbl_MR_Parts.[Description(Part)],
gdwh.tbl_MR_Calls.[CallID(Call)],
gdwh.tbl_MR_Jobs.[Job_ID(Job)],
gdwh.tbl_MR_Jobs.[HoursCM(Job)],
gdwh.tbl_MR_Jobs.[HoursPM(Job)],
gdwh.tbl_MR_Jobs.[HoursFCO(Job)],
gdwh.tbl_MR_Jobs.[HoursStandardInstall(Job)],
gdwh.tbl_MR_Jobs.[HoursLocalInstall(Job)],
gdwh.tbl_MR_Jobs.[HoursRemote(Job)],
gdwh.tbl_MR_Jobs.[HoursApplication(Job)],
gdwh.tbl_MR_Jobs.[HoursTravel(Job)],
gdwh.tbl_MR_Jobs.[TotalHoursThisJob(Job)],
gdwh.tbl_MR_Jobs.[JobStartDate(Job)],
gdwh.tbl_MR_Jobs.[JobStartStatusCode(Job)],
gdwh.tbl_MR_Jobs.[JobStartSystemStatus(Job)],
gdwh.tbl_MR_Jobs.[FieldEngineer(Job)],
gdwh.tbl_MR_Jobs.[ActivityTypeDescription(Job)],
gdwh.tbl_MR_Jobs.[HoursActivity(Job)],
gdwh.tbl_MR_Parts.[12NC(Part)],
gdwh.tbl_MR_Parts.[Comp_Price(Part)],
gdwh.tbl_MR_Parts.[Comp_Cost(Part)],
gdwh.tbl_MR_Calls.[CallType(Call)],
gdwh.tbl_MR_Calls.[CurrencyCode(Call)],
gdwh.tbl_MR_Calls.[NotificationCallTypeDescription(Call)]
FROM gdwh.tbl_MR_Calls INNER JOIN
gdwh.tbl_MR_Sites ON gdwh.tbl_MR_Calls.[ConfigID(Call)] = gdwh.tbl_MR_Sites.[ConfigID(Site)] AND
gdwh.tbl_MR_Calls.[CountryName(Call)] = gdwh.tbl_MR_Sites.[CountryName(Site)] INNER JOIN
gdwh.tbl_MR_Jobs ON gdwh.tbl_MR_Calls.[CallID(Call)] = gdwh.tbl_MR_Jobs.[Call_ID(Job)] AND
gdwh.tbl_MR_Calls.[CountryName(Call)] = gdwh.tbl_MR_Jobs.[CountryName(Job)] LEFT OUTER JOIN
gdwh.tbl_MR_Parts ON gdwh.tbl_MR_Jobs.[Job_ID(Job)] = gdwh.tbl_MR_Parts.[JobID(Part)] AND
gdwh.tbl_MR_Jobs.[CountryName(Job)] = gdwh.tbl_MR_Parts.[CountryName(part)]

WHERE
(gdwh.tbl_MR_Sites.[SystemCode(Site)] = '728231')
AND ((gdwh.tbl_MR_Calls.[Period(Call)] = '201003') OR
(gdwh.tbl_MR_Calls.[Period(Call)] = '201004')
)
AND (

(gdwh.tbl_MR_Sites.[ConfigID(Site)] = '5528') OR
(gdwh.tbl_MR_Sites.[ConfigID(Site)] = '8362') OR
(gdwh.tbl_MR_Sites.[ConfigID(Site)] = '4625'))

[Marcol]

Enclose your code in code tags before the moderators get you!!!....

Try this

Please Login or Register  to view this content.

Break your code into smaller bits and then concatenate them (as you go)

Then call the variable strQry

You should check this code over for missing spaces and additional brackets.

Happy to help.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[romperstomper]

FWIW, if you alias your table, it will make the code shorter and easier to read.

[Marcol]

Agreed romperstomper

Is there also a problem with string length?

[romperstomper]

Not any more, as far as I know. There used to be a limitation with the SQL property (now superseded by CommandText) which I think is why the macro recorder generates those fugly Array(array("SELECT...")) statements.