Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

(IsBlank, IsNull, " ") question

Discussion in 'Information Technology' started by default105, Jul 28, 2006.

  1. default105

    default105
    Expand Collapse
    Guest

    I have certain labels on a report that I need to hide to when a report is
    generated if the textbox in a form is blank. I tried to use the Can Shrink
    property but does not seem to allows work. So I tried the IsNull() and found
    that it worked only on certain ones. So I tried including the Or comparison
    and used forms!frmName.txtbox="" and it solved the problem. Question is
    whether there is an IsBlank function in access or some other function that
    would recognize that the field is blank but not null (as in someone erased
    the data or there is a space in the field but no characters.

    Hope you can make sense of this.

    Thanks much in advance.
     
  2. I'm not sure if IsEmpy would work for you. I know it won't if there is a
    space but you could do a IsEmpty(Trim(<string>))

    Gary Townsend
    Spatial Mapping Ltd.
    "default105" <default105@discussions.microsoft.com> wrote in message
    news:32F21558-45DD-4533-BD88-DD8DF9A9193D@microsoft.com...
    > I have certain labels on a report that I need to hide to when a report is
    > generated if the textbox in a form is blank. I tried to use the Can

    Shrink
    > property but does not seem to allows work. So I tried the IsNull() and

    found
    > that it worked only on certain ones. So I tried including the Or

    comparison
    > and used forms!frmName.txtbox="" and it solved the problem. Question is
    > whether there is an IsBlank function in access or some other function that
    > would recognize that the field is blank but not null (as in someone erased
    > the data or there is a space in the field but no characters.
    >
    > Hope you can make sense of this.
    >
    > Thanks much in advance.
     
  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    It sounds like the underlying table may allow zero-length strings in the
    text field. The problem with this is that users really can tell the
    different between a blank (zls) and a null (nothing there). Access can, as
    you've found out.

    Another approach might be to convert all zero-length strings to nulls, then
    disallow zls's.

    Regards

    Jeff Boyce
    Microsoft Office/Access MVP

    "default105" <default105@discussions.microsoft.com> wrote in message
    news:32F21558-45DD-4533-BD88-DD8DF9A9193D@microsoft.com...
    >I have certain labels on a report that I need to hide to when a report is
    > generated if the textbox in a form is blank. I tried to use the Can
    > Shrink
    > property but does not seem to allows work. So I tried the IsNull() and
    > found
    > that it worked only on certain ones. So I tried including the Or
    > comparison
    > and used forms!frmName.txtbox="" and it solved the problem. Question is
    > whether there is an IsBlank function in access or some other function that
    > would recognize that the field is blank but not null (as in someone erased
    > the data or there is a space in the field but no characters.
    >
    > Hope you can make sense of this.
    >
    > Thanks much in advance.
     
  4. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    To get both Null and zero-length strings (""), you can use If Len([Field] &
    "") = 0.

    If you also want to eliminate any number of blanks, you can use If
    Len(Trim([Field] & "")) = 0

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "default105" <default105@discussions.microsoft.com> wrote in message
    news:32F21558-45DD-4533-BD88-DD8DF9A9193D@microsoft.com...
    >I have certain labels on a report that I need to hide to when a report is
    > generated if the textbox in a form is blank. I tried to use the Can
    > Shrink
    > property but does not seem to allows work. So I tried the IsNull() and
    > found
    > that it worked only on certain ones. So I tried including the Or
    > comparison
    > and used forms!frmName.txtbox="" and it solved the problem. Question is
    > whether there is an IsBlank function in access or some other function that
    > would recognize that the field is blank but not null (as in someone erased
    > the data or there is a space in the field but no characters.
    >
    > Hope you can make sense of this.
    >
    > Thanks much in advance.
     
  5. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    Three important points here to explain what is happening:

    1. An empty string ("") is not the same as Null.

    2. Any boolean expression where one of the operands is Null will give a
    result of Null.

    3. If Null Then <statement 1> Else <statement 2>
    will execute <statement 2>

    So, if txtbox contains Null then:
    txtBox = "" will be Null (not False)
    and:
    IsNull(txtBox) Or (txtBox = "") will give (True Or Null) -> Null (not
    True)
    so:
    If IsNull(txtBox) Or (txtBox = "") Then
    will not necessarily work as desired.

    However, if you concatenate an empty string with Null, you will get an empty
    string, so the most common workaround is:

    If txtBox & "" = "" Then

    or

    If Len(txtBox & "") = 0 Then

    If txtBox may possibly contain blanks, then use Trim as well:

    If Len(Trim(txtBox & "")) = 0 Then

    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand

    "default105" <default105@discussions.microsoft.com> wrote in message
    news:32F21558-45DD-4533-BD88-DD8DF9A9193D@microsoft.com...
    >I have certain labels on a report that I need to hide to when a report is
    > generated if the textbox in a form is blank. I tried to use the Can
    > Shrink
    > property but does not seem to allows work. So I tried the IsNull() and
    > found
    > that it worked only on certain ones. So I tried including the Or
    > comparison
    > and used forms!frmName.txtbox="" and it solved the problem. Question is
    > whether there is an IsBlank function in access or some other function that
    > would recognize that the field is blank but not null (as in someone erased
    > the data or there is a space in the field but no characters.
    >
    > Hope you can make sense of this.
    >
    > Thanks much in advance.
     

Share This Page