Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    strSQL statement being truncated

    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.
    Last edited by weyrman; 2015-06-23 at 01:43.
    "Heading for the deep end"

  2. #2
    jwoods
    Guest
    Do you have Dim rst As Recordset defined somewhere?

    I also like to build SQL string variables like this...

    strSQL = "SELECT tblAllocate.ID,
    tblAllocate.JobID,
    tblAllocate.Dept,
    tblAllocate.Hours,
    tblAllocate.Location,
    tblAllocate.Comments "
    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.
    Last edited by jwoods; 2015-06-23 at 03:17.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    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)

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jwoods View Post
    Do you have Dim rst As Recordset defined somewhere?

    I also like to build SQL string variables like this...

    strSQL = "SELECT tblAllocate.ID,
    tblAllocate.JobID,
    tblAllocate.Dept,
    tblAllocate.Hours,
    tblAllocate.Location,
    tblAllocate.Comments "
    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
    Last edited by weyrman; 2015-06-23 at 18:14. Reason: detail added
    "Heading for the deep end"

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    jwoods
    Guest
    Quote Originally Posted by weyrman View Post
    but it throws an error and when I "watch" the strSQL expression it reports the value as:
    This might help on how to build and use SQL statements in Access using DAO or ADO...

    https://msdn.microsoft.com/en-us/lib...ice.12%29.aspx
    Last edited by jwoods; 2015-06-23 at 18:42.

  9. #9
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files
    "Heading for the deep end"

  10. #10
    jwoods
    Guest
    Isn't that your code in post #6?

    I don't use MS Access anymore so I can't open your DB.

    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.
    Last edited by jwoods; 2015-06-25 at 00:36.

  11. #11
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jwoods View Post
    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 --
    Last edited by weyrman; 2015-06-25 at 01:30.
    "Heading for the deep end"

  12. #12
    jwoods
    Guest
    All you have to do to test the string in steps is do the following...

    CurrentDb.Execute _
    "SELECT tblAllocate.ID, tblAllocate.JobID, ...etc."

    Plug in your own variables...test each step of the query.

    When it works, put it in the strSQL variable.

    Use Debug.Print to show values of variables as you test.

    See the samples in the link in post #8 on how to implement a query in DAO and ADO.

    Hair pulling and head banging is part of programming...no way to avoid it.

    Part of the learning process.

  13. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

  14. #14
    jwoods
    Guest
    That applies to creating and opening a Word document that uses a table to store data for a mail merge...

    https://msdn.microsoft.com/en-us/lib...ice.11%29.aspx

    https://msdn.microsoft.com/en-us/lib.../ff841005.aspx

    So it wouldn't apply in this case.

  15. #15
    jwoods
    Guest
    How you build strings in VBA/Access is important.

    Concatenation works very well (as shown in post #3 and the OP had in the code in post #6) or a vertical format (post #7)

    strSQL = "SELECT tblAllocate.ID, tblAllocate.JobID, tblAllocate.Dept, tblAllocate.Hours, tblAllocate.Location,tblAllocate.Comments "
    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;"

    Notice that appropriate spaces are added at the end of all lines, except the last one.

    A Debug.Print will show the entire query.

    Easier to read and maintain.

    I've built strings in VBA Access much longer than this one with no issues.

    I recommend testing the query in small steps, in isolation from the application, using test variables substituting for the app variables in the query.

    Once it works, then hook it up to the app.

    I think the OP may also need to do more reading on the basics of how to use DAO and ADO in Access.
    Last edited by jwoods; 2015-06-25 at 03:35.

  16. The Following User Says Thank You to jwoods For This Useful Post:

    weyrman (2015-06-25)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •