Results 1 to 6 of 6
  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496

    dealing with zero length strings

    I've been told that the savvy programmer never allows a zero length string; well, I'm not savvy, or schooled for that matter; and the truth is there are lots of times that I don't need the user to enter data and zero length string is totally acceptable.


    ex: on an UNBOUND form I have: Boolean field: blnAlternateMfr_YN; followed by: short text field: cboAlternateMfr_Name (entered in a combo box control)
    txtAlternateMfr_Name could be null, or ZLS (it doesn't matter and is not required); the user could have entered a text of # length, but then the changed their mind and backspaced to a blank entry (ZLS) -it's OK

    The problem is that when I assign the values in the form to the relevant tables, if the entry is a ZLS, a line of code such as:
    rstTarget![txtAlternateMfr_Name] = Me.cboAlternateMfr_Name
    causes ACCESS to throw an error at me (understandably)

    (This is part of a constantly evolving project, and because MS in their wonder has flip-flopped on the default value for allow ZLS, depending on when the user's back-end table was created (by code), the default may be No, or it maybe YES ...further aggravating me ... I digress)

    NTL,
    There must be a standard approach for dealing with this condition (Going to each of the back-ends (each user has their own), and manually changing the default is not really an option, although I suspect that could do it by code, but that wouldn't be too savvy, would it?)
    Any suggestion, direction, or even a stern, but productive lecture will be greatly appreciated with many thanks in advance.

    Mark

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Have you tried incorporating Nz() into your code?

  3. #3
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    yes, as in:
    rstTarget![txtAlternateMfr_Name] = nz(Me.cboAlternateMfr_Name,""); and of course, "" is a ZLS

    even the variation nz(Me.cboAlternateMfr_Name, " "), with a single blank space, truncates to also be a ZLS
    currently, I'd been using:

    If Len(Nz(Me.cboAlternateMfr_Name, "")) > 0 Then
    rstTarget![txtAlternateMfr_Name] = Me.cboAlternateMfr_Name
    End If

    which seems excessive, and, much more of a problem, in the case of a record update, where the user changed an existing entry to be "nothing" (blank), an existing entry is not updated

  4. #4
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    it seems the (obvious in hindsight) appropriate code is simply:

    Code:
                
    If Len(Nz(.cboAlternateMfr_Name, "")) = 0 Then
             rstTarget![txtAlternateMfr_Name] = Null
    Else
             rstTarget![txtAlternateMfr_Name] = .cboAlternateMfr_Name
    End If
    sometimes you just need to hang your laundry on the line to see the stain (is what my grandma would say)
    thanks all..

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You can also use:
    If Len(.cboAlternateMfr_Name & "") = 0 Then

  6. #6
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    got it ...thnx!
    kiss

    (Keep It Simple Stupid))

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dealing with the # character
    By frankivalli in forum Access
    Replies: 2
    Last Post: 06-12-2014, 10:57 AM
  2. Dealing with Old Balance
    By Altaful Haq in forum Forms
    Replies: 2
    Last Post: 02-16-2014, 06:29 AM
  3. Replies: 1
    Last Post: 09-16-2013, 04:35 PM
  4. Replies: 8
    Last Post: 02-22-2012, 11:58 AM
  5. Help dealing with duplicate records
    By jswenson3 in forum Access
    Replies: 12
    Last Post: 02-01-2012, 10:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums