Joining a SQL String

[aziz rasul]

I create a SQL string, given below, which I have copied from the immediate window:-

SELECT ProjectData.`Project Name`, ProjectData.`Resource Name`, ProjectData.`Resource Name Initials`, ProjectData.`Resource Type`, ProjectData.`Resource Position`, ProjectData.Feb1, ProjectData.Feb2, ProjectData.Feb3, ProjectData.Feb4, ProjectData.Feb5, ProjectData.Mar1, ProjectData.Mar2, ProjectData.Mar3, ProjectData.Mar4, ProjectData.Apr1, ProjectData.Apr2, ProjectData.Apr3, ProjectData.Apr4, ProjectData.May1, ProjectData.May2, ProjectData.May3, ProjectData.May4, ProjectData.May5, ProjectData.Jun1, ProjectData.Jun2, ProjectData.Jun3, ProjectData.Jun4, ProjectData.Jul1, ProjectData.Jul2, ProjectData.Jul3, ProjectData.Jul4, ProjectData.Aug1, ProjectData.Aug2, ProjectData.Aug3, ProjectData.Aug4, ProjectData.Aug5, ProjectData.Sep1, ProjectData.Sep2, ProjectData.Sep3, ProjectData.Sep4, ProjectData.Oct1, ProjectData.Oct2, ProjectData.Oct3, ProjectData.Oct4, ProjectData.Oct5, ProjectData.Nov1, ProjectData.Nov2, ProjectData.Nov3, ProjectData.Nov4, ProjectData.Dec1, ProjectData.Dec2, ProjectData.Dec3, ProjectData.D
ec4, ProjectData.Jan1, ProjectData.Jan2, ProjectData.Jan3, ProjectData.Jan4, ProjectData.Jan5 FROM ProjectData WHERE

How do I ensure that I don't have Dec4 split as it is above. The effect of the above is that I get blank data when data should appear. If I place the SQL in the SQL window and "join" "D" with "ec4" the missing data appears.

 

[Minty]

The immediate window has a line limit which is I think 1024 characters, it won't effect the code operation but you can't use it in the immediate window.
What might be better would be to normalize your data so you aren't creating such a large string?

Fields with names like ProjectData.May4, ProjectData.May5, ProjectData.Jun1, ProjectData.Jun2, ProjectData.Jun3, ProjectData.Jun4, ProjectData.Jul1 etc generally mean you are not storing your data correctly.

 

[aziz rasul]

Unfortunately I am stuck with the table as they are.
I have tried to replace the Chr(13) or Chr(10) characters with "" but it doesn't work as the 1024 line limit obviously is getting in the way. I will try to break the SQL string into smaller chunks so that the new line only begins when a comma appears.

 

[Minty]

If you put your query into a string in VBA it would work, I believe the character restriction is something like 65,000 ? I'm puzzled why you would be trying to run this in the immediate window?

And if the tables are imported or linked do some data manipulation on them and organise them as you need. In their current state they will be a nightmare to work with. (As you are discovering)

 

[aziz rasul]

I was only looking at the SQL in the immediate window to see what was causing the problem.

Minty as you have pointed out the 1024 line length is the issue.

Can you clarify what you mean by "into a string in VBA"?

 

[Minty]

Sorry by re-reading I'm guessing you are creating the Sql String in VBA already. What isn't working with it ?

 

[aziz rasul]

In the resulting data, one of the values which should be 07/02/16 is blank.
Apparently the connection string (ADODB.Connection) was wrong i.e. the Provider and version number.

Many thanks for your help.