Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    invisible label in report (Access 2000)

    Can i make a label visible in report oinly when the FirstName and LastName of the custoemr exist?
    My label is called lblAttention and the caption is " To The Attention Of ".This label is before the control in my report
    where the source is the following concatenation:
    =[FirstName] & " " & [LastName]. The control is callled txtClientName


    There are cases when the user omits to enter the personal names of the cutsomer.In these cases the label" To The attention of" should remain useless,
    since there will be no sense in it.

    I have tried the following
    In the OnOpen event of the report i have written

    If Me![TxtClientName] IsNotNull
    Me![lbl].visible = true

    Thuis code however has no effect.

    Can somebody tell me an efficient way to make my label visible only when the second name exists?

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: invisible label in report (Access 2000)

    Testing for value of txtClientName being null won't work because if even if no First Name & Last Name are entered the concatenated value will still equate to " " (space) which does not equate to Null. Assuming label is in detail section of report you can use code like this for Detail_Format event:
    <pre>Me.lblAttention.Visible = Len(Trim(Me.txtClientName))</pre>

    If the concatenated field equates to " " then Trim function will result in zero length string, and Len will = 0, which equates to False. Any length greater than 0 will equate to True.

    Though I think it's flaky that you can have a record for a client with no first or last name, I would think that these should be defined as required fields.

    HTH

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: invisible label in report (Access 2000)

    I tested your code and it gave me a type mismatch. True is stored as -1, and the len function cannot return this value.

    This worked : Me.lblAttention.Visible = (Len([txtclientname]) > 1)
    Regards
    John



  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: invisible label in report (Access 2000)

    I was unable to generate any error in testing this. I'm using Access XP/2002, but that don't think that should make a difference in this case. Far as I know VBA generally will interpret number other than 0 as True even though value of True is -1. But if errors are possible then using your alternative would be good idea.

  5. #5
    Lounger
    Join Date
    Aug 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: invisible label in report (Access 2000)

    thank you very much for you elegant solution! It works perfect and i have solved my problem
    Best regards

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: invisible label in report (Access 2000)

    I tried it in Access97 because that was running it the time. That was where I got the type-mismatch error.
    I have now tried it again in Access2000 and it worked perfectly.
    Regards
    John



  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: invisible label in report (Access 2000)

    In 97 or 2000 try this:

    Me.lblAttention.Visible = Len(Trim(Me.txtClientName & vbNullString))

    If the field txtClientName is truly Null, the other version should error out in 2000 as well ... at least it does in MY 2000.
    Charlotte

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: vbNullString in Expression? (Access 2000)

    What is purpose of vbNullString constant when used in expressions like this example? To avoid error caused by Null? The Help file sez it is equivalent to "String having value 0", and for description states "Not the same as a zero-length string (""); used for calling external procedures." Tested expression with field replaced by Null and it returned 0. I've seen it used in other sample code and was wondering in what circumstances should this constant be used; not sure what "used for calling external procedures" is supposed to mean....

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vbNullString in Expression? (Access 2000)

    It will give you the desired result, but if you're picky, just wrap the control in an Nz function to return a zero-length string when it's null. I can't recall where I picked up the habit. I know what help says, but I've never figured out what it really means, since I have trouble understanding how a string can have a value of zero (not, please note "0"), which is nonsense.
    Charlotte

Posting Permissions

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