SQL statement is cutting off!!

[Anishtain4]

I'm tring to get a SQL statement into a recordset but it is cutting when I'm trying to make it!!! I tried many different ways but it does not work my SQL is:

strFLD = "SELECT Contractor, Add_D, Chores.Type, Code, Finished, Reserved, Engineer,C_Prove"
strSQL1 = " FROM (Chores INNER JOIN Projects ON Chores.Project = Projects.Code) INNER JOIN Engineers ON Chores.Engineer = Engineers.Number"
strSQL2 = " WHERE ((((Chores.Finished) Is Null) OR ((Chores.C_Prove) Is Null)) AND ((Chores.Engineer)=" & Me.frm_Engineer.Value & "));"
strSQL = strFLD & strSQL1 & strSQL2

but when I want to use it in the openRecordset, the strSQL only contains:
"SELECT Contractor, Add_D, Chores.Type, Code, Finished, Reserved, Engineer,C_Prove FROM (Chores INNER JOIN Projects ON Chores.Project = Projects.Code) INNER JOIN Engineers ON Chores.Engineer = Engineers.Number WHERE ((((Chores.Finished) Is Null) OR ((

as you see the statement is not complete which result in a lot more recorsets, what should I do?
I have tried & _ and also & vbCrLf & _ don't tell me do that

 

[Galaxiom]

Looks like a 255 character limit. What version are you using?

Use a saved query (with subqueries if necessary) and open that as the recordset.

Otherwise, get rid of the unnecessary bracketing. If the filednames are unambiguous you can drop the tablename from the references. Alias the other tablenames to a single letter.

BTW. Type is a reserved word.

 

[Anishtain4]

I'm using access 2003
I tried to extend the string length by
dim strSQL as String*2000
but it's not working yet
how can I use query for opening recordset? using their name instead of strSQL? and how can I set the parameters of them? I need to get specific engineer number into the query

I also have another problem, to shorten the string I change the string as:
strFLD = "SELECT Contractor, Add_D, Code, Chores.* "
strSQL1 = " FROM (Chores INNER JOIN Projects ON Chores.Project = Projects.Code) INNER JOIN Engineers ON Chores.Engineer = Engineers.Number"
strSQL2 = " WHERE (((Finished Is Null) OR (C_Prove Is Null)) AND Engineer=" & Me.frm_Engineer.Value & ");"
strSQLT = strFLD & strSQL1 & strSQL2

but when I want to use the add_D which is mentioned in strFLD, it says Item not found in this collection, however code and contractor are available!!!!

 

[Galaxiom]

Hold on. I hadn't noticed you said the string variable was truncated. The string length is not the problem as strings lengths are limited only by memory.

I was thinking the problem must have been a limit to the argument in OpenRecordset method. I had not come up against such a limit but a quick Len() of what you reported working returned 250 hence the suspicion.

This needs a closer look.

Otherwise a workaround for the unknown problem assuming it is length:

A tablename, queryname or SQL string can be used to open a recordset.

To do the dynamic part, first use a saved query to for as much as possible.
Then use the VBA to build the OpenRecordset sql string starting with:
SELECT * FROM somequery

 

[Galaxiom]

Ahhh. The Locals window does have a limit to what it can show. Use a Debug.Print to the Immediate window to get the full length. Hopefully that is the problem.

 

[Galaxiom]

but when I want to use the add_D which is mentioned in strFLD, it says Item not found in this collection, however code and contractor are available!!!!

Now that is very odd.

Have you ever changed the name of that field or its table?
I have seen NameAutoCorrect lose track of a name change. Although the query on the table looked perfect in the design view it kept asking for a parameter when I tried to run it.

Who knows what happens in a recordset. You could try pasting the sql into the query designer and see if you get the same error.

 

[Anishtain4]

Thanks, it helped a lot