PDA

View Full Version : Record Macro won't complete query lenth



debauch
01-04-2008, 02:53 PM
I want to make my query dynamic and use cells as the date parameter. When I go to record the macro though, the query seems to be too long to record. I had this working in another file, and Im not quite sure how to write the rest out?? Is there an easier way to do this?

debauch
01-07-2008, 08:46 AM
what do the chr(10) an chr(13) do ?


..."SELECT"&chr(13)&""&chr(10)&...


I think if I can figure this part out, I should be able to finish writing the query myself in vba.

malik641
01-07-2008, 09:15 AM
Chr(10) = Linefeed
Chr(13) = Carriage Return

But you don't have to use that in a SQL query, just so you know. Also, the combo of those 2 character codes is equivalent to using vbCrLf like
..."SELECT" & vbCrLf & "From"...

How long is the query? How many characters is it?

debauch
01-07-2008, 09:35 AM
1355 with spaces, 1,294 without.

debauch
01-07-2008, 09:40 AM
Here is an example of how I am trying to do it ...

.CommandText = Array( _
"SELECT" & vbCrLf & _
"a.itemA,a.itemB,a.itemC,a.itemA,a.ItemD,a.ItemE,a.ItemF," &vbCrLf& _ a.ItemG,a.ItemH,a.ItemI," & vbCrLf & _
"a.itemJ,a.itemK,a.itemL,a.itemM..." & vbCrLf & _
etc.



The table has many fields I require (about 30, then about 20 + in the group by clause, but the table just has too many fields (200 +) to pull the whole thing back. That is why it is so long.