Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IsNull on memo/text field (Access 2003)

    I use a piece of code to append text to a text field. To separate each "note", I add 5 spaces after each note which makes it easier to read, and separate each set of notes. As some of the notes, are getting bigger, I decided to change the text field to a memo field.

    My code (below) stopped working however.

    I believe I have narrowed the problem down to the fact that I cannot use IsNull with a memo field, although I can't see this in the help file. Is there an equivalent to IsNull for a memo field.


    Private Sub cmdSave_Click()
    On Error GoTo Err_cmdSave_Click
    '************************************************* *************************
    'Add notes
    '************************************************* *************************
    If IsNull([PeepNotes]) Then
    PeepNotes = "Renewed - " & RenewedMethod & " on " & Date & " wef " & cmbRenewDate & ":"
    Else
    PeepNotes = PeepNotes & " Renewed - " & RenewedMethod & " on " & Date & " wef " & cmbRenewDate & ":"
    End If

    PS I know I can turn round the example by adding space at the end of each note, rather than at the beginning of the subsequent one as I'm doing here, but I wanted to solve this problem firstif possible.

    Thanks


    Colin

  2. #2
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull on memo/text field (Access 2003)

    I've just tried removing the IsNull, and adding spaces after the note, and it doesn't work this way either.



    PeepNotes = PeepNotes & "Renewed - " & RenewedMethod & " on " & Date & " wef " & cmbRenewDate & ": "

    I have this line (below) as well which works whether the field is a text or memo field, but the line above works if a text field is used, but not if a memo field is used.


    PeepNotes = PeepNotes & " " & [Datediff] & " Months payments missed - " & Date & ":"


    Colin

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

    Re: IsNull on memo/text field (Access 2003)

    IsNull works on memo fields too. You mention that "My code (below) stopped working". Do you get an error message, or does the code do something different from what you intended it to do?

    By the way, I'm not enthusiastic about using memo field this way, since the text can keep on growing. Instead, I would create a table for the notes, with a separate record for each note.

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull on memo/text field (Access 2003)

    You could try testing for an empty field with:

    If Len([PeepNotes])=0 Then...

    It should have the same effect I would have thought.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull on memo/text field (Access 2003)

    The line of code just doesn't apply the text addition. No error message appears. The code executes, but the field [PeepNotes] is not appended with the new text.

    I take your point about the memo field, but the maximum no of characters that can be entered in 12 months is 90 characters, so the field cannot grow too large too quickly. As a total beginner, I have to learn these lessons as I go. Next time I hope I will remember to do it differently.

    I'm changing all the code around it to see what I cam improve anyway

    Colin

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

    Re: IsNull on memo/text field (Access 2003)

    The code you posted should either do something (whether PeepNotes is null or not, the code should put something in the field), or jump to the error handler. You do show a message in the error handling section below Err_cmdSave_Click:, don't you?

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull on memo/text field (Access 2003)

    Sorry

    I've been away since you posted. I've just had to to check mail, then away for the weekend.

    I'll try & respond later

    Colin

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull on memo/text field (Access 2003)

    Unfortunately, I don't have a message in my err code.

    Colin

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

    Re: IsNull on memo/text field (Access 2003)

    Do you mean that you have something like

    MsgBox Err.Description

    in your error handling code, but you don't get an error message, or that you don't have such a line in your error handling code?

  10. #10
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull on memo/text field (Access 2003)

    I've changed the code totally, and managed to get it to work, when I was away, so I don't have the original code to check, other than deep in my back-ups

    Anyway I have solved the problem, thanks anyway.


    Colin

Posting Permissions

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