Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    This setting for this property is too long? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: This setting for this property is too long? (a2k (9.0.6926) SP-3 Jet 4

    I don't get an error message when I try this. How have you specified the SQL string in the code?

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This setting for this property is too long? (a2k (9.0.6926) SP-3 Jet 4

    See attachment

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: This setting for this property is too long? (a2k (9.0.6926) SP-3 Jet 4

    I have attached your database (from a previous thread) with a form containing a list box (for easier visibility) and a command button. The command button sets the row source of the list box using your code. I added a literal value for strAddrName.

    It works for me. What happens if you open the form and click the button?

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This setting for this property is too long? (a2k (9.0.6926) SP-3 Jet 4

    I get:

    Run-time error '2176' This setting for this property is too long

    Highlighted: cboAddrName.RowSource = strSQL

    Thank goodness (I didn't have to prepare strip down db), but not really, it works for your version but not for mine.

    Any clues?

    Thanks, John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: This setting for this property is too long? (a2k (9.0.6926) SP-3 Jet 4

    I'm using Access 2002 while you are using Access 2000.
    You could try using a saved query as Row Source, and set the SQL of the query in code.

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This setting for this property is too long? (a2k (9.0.6926) SP-3 Jet 4

    I removed "OR strLocStreetName = '*Void Permit*' " from the query and it worked OK.

    The size of the query measured 2048, sounds like a size limit issue in a2k.

    I changed to saved query as you suggested and changed:

    LIKE 'elm*'

    To

    LIKE [Forms]![Formulier1]![strAddrName] & "*"

    The above works fine

    Then I

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: This setting for this property is too long? (a2k (9.0.6926) SP-3 Jet 4

    Have you attached the correct file? I don't see a query qry_Example_Sort_Number_Address_tbl_RPS_Owner_3_Te st.

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This setting for this property is too long? (a2k (9.0.6926) SP-3 Jet 4

    Sorry, try this

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: This setting for this property is too long? (a2k (9.0.6926) SP-3 Jet 4

    No need for the Chr(34)'s. Just use

    ... WHERE strLocStreetName LIKE [Forms]![Formulier1]![strAddrName] & "*" ...

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This setting for this property is too long? (a2k (9.0.6926) SP-3 Jet 4

    Thank you for all your help

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This setting for this property is too long? (a2k (9.0.6926) SP-3 Jet 4

    Hello Hans

    Is there any future advantage/disadvantage of not referencing table name for each field in following union query as opposed to previous post attachment?

    Thanks, John

    <pre>SELECT
    lngID as [ID],
    strLocStreetNameNo as [Location Address],
    strLocOwnerName as [Owner Name],
    strPrint_Key as [SBL Number],
    strPropClass as [PC],
    IIF(ysnWaterfront=0,'n','Y') AS [W],
    IIF(ysnHistorical=0,'n','Y') AS [H],
    IIF(ysnFloodZone=0,'n','Y') AS [F],
    strParZoneCode AS [Zone],
    strMailStreetAddress AS [Mailing Address],
    strMailCityStateZip AS [Mailing City, St, Zip],
    dtmCCDateTime AS [Create],
    strLocStreetName as [Street Name],
    IIf(IsNull([strLocStreetNo]),True,IsNumeric([tbl_RPS_Owner.strLocStreetNo])) As Sort1,
    IIf(IsNull([strLocStreetNo]),0,Val([strLocStreetNo]))As Sort2
    FROM tbl_RPS_Owner
    WHERE strLocStreetNameNo
    LIKE [Forms]![Formulier1]![strAddrNameNo] & "*"
    OR strLocStreetName = '*Close Lookup and Retry*'
    OR strLocStreetName = '*Cancel Transaction*'
    OR strLocStreetName = '*Manual Entry*'
    OR strLocStreetName = '*Void Permit*'

    UNION SELECT
    lngID,
    strLocStreetNameNo,
    strLocOwnerName,
    strPrint_Key,
    strPropClass,
    IIF(ysnWaterfront=0,'n','Y'),
    IIF(ysnHistorical=0,'n','Y'),
    IIF(ysnFloodZone=0,'n','Y'),
    strParZoneCode,
    strMailStreetAddress,
    strMailCityStateZip,
    dtmCCDateTime,
    strLocStreetName,
    IIf(IsNull([strLocStreetNo]),True,IsNumeric([strLocStreetNo])),
    IIf(IsNull([strLocStreetNo]),0,Val([strLocStreetNo]))
    FROM tbl_RPS_Owner_Non_RPS
    WHERE strLocStreetNameNo
    LIKE [Forms]![Formulier1]![strAddrNameNo] & "*"
    OR strLocStreetName = '*Close Lookup and Retry*'
    OR strLocStreetName = '*Cancel Transaction*'
    OR strLocStreetName = '*Manual Entry*'
    OR strLocStreetName = '*Void Permit*'
    ORDER BY [Street Name], Sort1, Sort2;
    </pre>


  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: This setting for this property is too long? (a2k (9.0.6926) SP-3 Jet 4

    In SQL, you can always refer to a field as TableName.FieldName. If no confusion is possible, you are allowed to omit the table name. Each of the two parts of your union query is based on only one table, so there can be no confusion about which table a field belongs to. In such a situation, omitting the table name makes the SQL easier to read in my opinion. You already know to which table the fields belong.

    In a query based on two or more tables, you are only required to specify the table name explicitly if the field name occurs in more than one table, and in the JOIN expression. But in such a situation, including the table name even where not obligatory helps to see to which table each field belongs.

  14. #14
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: This setting for this property is too long? (a2k (9.0.6926) SP-3 Jet 4

    Thanks for the info

    Just removing the table names in my original post eliminated the following message when loading form combo box

    Run-time error

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: This setting for this property is too long? (a2k (9.0.6926) SP-3 Jet 4

    Apparently, that is the maximum length for Access 2000, although I cannot find a direct reference on the Microsoft site. Perhaps you'll find it in the online help, search for specifications., Form/Report.

    For Access 2002 and 2003, it is 32,750 characters.

Posting Permissions

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