strSQL statement being truncated

Topic

New Reply

I have the following statement

Dim strSQL As String

strSQL = “SELECT tblAllocate.ID, tblAllocate.JobID, tblAllocate.Dept, tblAllocate.Hours, tblAllocate.Location, tblAllocate.Comments FROM tblAllocate WHERE (((tblAllocate.Emp)=[Forms]![frmJobAllocationPage]![cboEmployees]) AND ((tblAllocate.Completed)=False)) ORDER BY tblAllocate.Priority WITH OWNERACCESS OPTION;”

Set rst = CurrentDb.OpenRecordset(strSQL)

but it throws an error and when I “watch” the strSQL expression it reports the value as:

“SELECT tblAllocate.ID, tblAllocate.JobID, tblAllocate.Dept, tblAllocate.Hours, tblAllocate.Location, tblAllocate.Comments FROM tblAllocate WHERE (((tblAllocate.Emp)=[Forms]![frmJobAllocationPage]![cboEmployees]) AND ((tblAllocate.Completed)=False)) O

It has been truncated! Does anybody have any ideas as to why? I have shut the program down and restarted it and also tried a reboot.

Reply | Quote

Replies

Weyrman,

Interestingly enough the MSDN reference on VBA data types states:

Common language
runtime type
Visual Basic type structure Nominal storage allocation Value range

String
variable-length) String (class) Depends on 0 to approximately characters implementing platform 2 billion Unicode

Your statement is being truncated at 250 chars.

From the Access VBA Help:
A variable-length string can contain up to approximately 2 billion (2^31) characters.
A fixed-length string can contain 1 to approximately 64K (2^16) characters.

You might try a Fixed length string definition to see if it makes a difference:

Dim strSQL as String * 500

True, you will waste some storage space but it should not be a major problem.

You can also try breaking up the statement:

strSQL = “SELECT tblAllocate.ID, tblAllocate.JobID, tblAllocate.Dept, tblAllocate.Hours, tblAllocate.Location, tblAllocate.Comments FROM tblAllocate ”
strSQL = strSQL & “WHERE (((tblAllocate.Emp)=[Forms]![frmJobAllocationPage]![cboEmployees]) AND ((tblAllocate.Completed)=False)) ”
strSQL = strSQL & “ORDER BY tblAllocate.Priority WITH OWNERACCESS OPTION;”

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

The Watch may truncate; so in case it might, try this:

Assuming you had some existing query (maybe something like “qrySelect”), I’d do this prior to opening the recordset:

CurrentDB.QueryDefs(“qrySelect”).SQL = strSQL

If your code fails again, examine this query.

Reply | Quote

In addition to Mark’s suggestion, you could also use the statement

Debug.Print strSQL

to write the variable to the Immediate window and see whether it’s actually being truncated (as opposed to just in the Watch window)

Reply | Quote

Do you have Dim rst As Recordset defined somewhere?

I also like to build SQL string variables like this…

strSQL = “SELECT tblAllocate.ID,
[INDENT][INDENT][INDENT]tblAllocate.JobID,
tblAllocate.Dept,
tblAllocate.Hours,
tblAllocate.Location,
tblAllocate.Comments ” [/INDENT] [/INDENT][/INDENT]
strSQL = strSQL & “FROM tblAllocate ”
strSQL = strSQL & “WHERE(((tblAllocate.Emp)=[Forms]![frmJobAllocationPage]![cboEmployees]) ”
strSQL = strSQL & “AND ((tblAllocate.Completed)=False)) ”
strSQL = strSQL & “ORDER BY tblAllocate.Priority WITH OWNERACCESS OPTION;”

Much easier to read for the person who has to maintain the code.

Access won’t let me do it. It keeps putting quotes after the first line

strSQL = “SELECT tblAllocate.ID,”

You can also try breaking up the statement:

strSQL = “SELECT tblAllocate.ID, tblAllocate.JobID, tblAllocate.Dept, tblAllocate.Hours, tblAllocate.Location, tblAllocate.Comments FROM tblAllocate ”
strSQL = strSQL & “WHERE (((tblAllocate.Emp)=[Forms]![frmJobAllocationPage]![cboEmployees]) AND ((tblAllocate.Completed)=False)) ”
strSQL = strSQL & “ORDER BY tblAllocate.Priority WITH OWNERACCESS OPTION;”

Thanks RG, that is a better way!

Debug.Print strSQL

OK I am getting the full strSQL string (must remember that)

strSQL=CurrentDB.QueryDefs(“qrySelect”).SQL

I tried this. If I copy and paste the sql into a new query the query runs just fine but when I call it using the above code I get an error 3061 (Too few parameters. Expected 1.)

I looked up this error and it may be that I need single quotes somewhere but I have no idea where.

It’s got me stuffed!

the underlying table has the following fields

ID:Autonumber
JobID:Number
Emp:Number
Dept:Text
Hours:Number
Comments:Memo
Priority:Number
Completed:Number
Location:Text

here is the code block in full

Code:

Private Sub cboEmployees_AfterUpdate()
Dim strSQL As String
Dim rst As Recordset

   On Error GoTo cboEmployees_AfterUpdate_Error

‘strSQL = “SELECT tblAllocate.ID, tblAllocate.JobID, tblAllocate.Dept, tblAllocate.Hours, tblAllocate.Location, tblAllocate.Comments FROM tblAllocate ”
‘strSQL = strSQL & “WHERE (((tblAllocate.Emp)= [Forms]![frmJobAllocationPage]![cboEmployees]) AND ((tblAllocate.Completed)=False)) ”
‘strSQL = strSQL & “ORDER BY tblAllocate.Priority WITH OWNERACCESS OPTION;”

Debug.Print strSQL; qryAllocated
strSQL = CurrentDb.QueryDefs(“qryAllocated”).sql

Set rst = CurrentDb.OpenRecordset(strSQL)
    With rst
       .MoveFirst
       Do Until rst.EOF
       lstJobs.AddItem tblAllocate.JobID & “;” & tblAllocate.Dept & “;” & tblAllocate.Hours & “;” & tblAllocate.Location
       .MoveNext
       Loop
    End With

   On Error GoTo 0
   Exit Sub

cboEmployees_AfterUpdate_Error:

    MsgBox “Error ” & Err.Number & ” (” & Err.DESCRIPTION & “) in procedure cboEmployees_AfterUpdate of VBA Document Form_frmJobAllocationPage”

End Sub

Reply | Quote

Weyrman,

You need the concatenation & statement continuation characters “& _” ex:

Code:

strSQL = "SELECT tblAllocate.ID," & _
               tblAllocate.JobID," & _
               tblAllocate.Dept," & _
               tblAllocate.Hours," & _
               tblAllocate.Location +," & _
               tblAllocate.Comments"

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Sadly I fluked this but then went on to another event before saving it, closed everything in a hurry and so lost it and now can’t remember what I did. I know its in the strSQL expression.

I have prepared a sample database to show my problem. the form is to show the jobs assigned to an employee in the order they are to be done. It keeps throwing an error as you will see.

Items in the table are entered from a different job screen.

When you open the form you should be able to select an employee in the dropbox and have the listbox filled with items from the table set for them. I want to do it this way because I want to be able to use the up and down buttons on the side to set the order of the jobs and then click the save button to set it, by writing priority numbers back to the table for when it is loaded next time.

This allows new jobs to be prioritized as needed.

Thanks in advance to anyone willing to have a look

Reply | Quote

I like to build and test queries one step at a time, rather than trying to build the entire query at once.

Get it working with a test suite of variables you provide, before hooking it up to the application.

Refer back to the link in post #8 on using SQL statements in DAO and ADO.

I looked at the link and understand the examples given but I don’t seem to be able to translate that principle into getting what I had. –I am SOOOOO annoyed at myself —

I normally build the sql in a query and then copy and paste without any issue. In fact the sql works perfectly in a query but not as a recordset string. As I do this only sporadically as well as being mainly self taught I have hit the wall on this one. I have wasted too many hours already trying to modify the sql again hoping to fluke it a second time. I’m sure its in the sytnax of the sql somewhere but its just beyond me at the moment to understand what.

Hence my posting this little database. –Insert hair pulling and banging head against a wall emoticons here —

Reply | Quote

FWIW, and I don’t know whether it’s applicable here, Word’s VBA OpenDataSource method takes both a SQLStatement and SQLStatement1 parameter. For example:
SQLStatement:=”SELECT * FROM `Sheet1$`”, SQLStatement1:=””
According to the VBA help file:

If the query string is longer than 255 characters, SQLStatement specifies the first portion of the string, and SQLStatement1 specifies the second portion.

The implication seems to be that, in Word at least, an SQLStatement longer than 255 characters is liable to be truncated, regardless of how you go about building it.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

When I write this sort of code I always include a private procedure called TestIt. In here I include test values of all necessary variables and an instruction to run the procedure in question. There are lots of possible variants like including a binary flag in TestIt and including a command in the main procedure to test the flag and stop before writing to live tables if it is set.

Reply | Quote

Just a quick sleepy-time analysis but it looks like your SQL is good – it appears your assignment to the form is where the error is occurring. Made some test changes to your code and the form populates just fine.

strSQL = “SELECT tblAllocate.ID, tblAllocate.JobID, tblAllocate.Dept, tblAllocate.Hours, tblAllocate.Location, tblAllocate.Comments FROM tblAllocate ”
strSQL = strSQL & “WHERE ((tblAllocate.Emp)=” & [Forms]![frmJobAllocationPage]![cboEmployees] & ” AND ((tblAllocate.Completed)=False)) ”
strSQL = strSQL & “ORDER BY tblAllocate.Priority;”

‘strSQL = CurrentDb.QueryDefs(“qryAllocated”).sql
‘Debug.Print strSQL
Set rst = CurrentDb.OpenRecordset(strSQL)
‘With rst
If rst.RecordCount > 0 Then
rst.MoveFirst
Do Until rst.EOF
lngID = rst![ID]
sngJobID = rst![JobID]
strDept = rst![Dept]
lngHours = rst![Hours]
strLoc = Nz(rst![Location], “”)
Me![lstJobs].AddItem lngID & “;” & sngJobID & “;” & strDept & “;” & lngHours & “;” & strLoc
rst.MoveNext
Loop
End If

I hope this helps!
– Bob

Reply | Quote

Thankyou Bob so much, most appreciated!!!!:clapping: I will study your code to try and understand where I was going wrong, I won’t just copy /paste, promise!

I think the OP may also need to do more reading on the basics of how to use DAO and ADO in Access.

You are right, I do. I just don’t code continuously enough to keep what I know fresh in my head and sometimes, like this it just completely stumps me.:confused: This is part of an extensive application which has been built over many years with lots of commenting. Often I can look back to where I have used a process before and copy, paste, adapt with very little problem.

Reply | Quote

Thankyou Bob so much, most appreciated!!!!:clapping: I will study your code to try and understand where I was going wrong, I won’t just copy /paste, promise! :D.

Hey, the helping part is fun so you are quite welcome. Just realize that I just went right into my down and dirty troubleshooting routine. My steps were as follows…

1) Comment out the “on error” statement to see where the error actually occurs.
2) Move the data into variables to try to see if there are issues with it. The Location was null so caused an error so I inserted the ‘NZ’ statement.
3) Tried to understand why the “With rst” wasn’t working but was still to ‘foggy-eyed’ to figure it out so I bypassed it and used ‘longhand’ assignments to make it work.
4) Threw in the ‘rst.recordcount > 0’ to prevent issues with no records.

If you really want you can use it verbatim if you wish but I’m assuming you’ll make it a bit prettier than I did for this

Best of luck. Remembering the how and why of old code is always a challenge!
– Bob

Reply | Quote

Remembering the how and why of old code is always a challenge!

That’s what comments are for. And why good code always includes them among other things. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote