Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Volunteer is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2018
    Posts
    5

    Help understanding invalid use of null

    Hello,
    I am a volunteer in our neighborhood organization and I have inherited a database built to handle membership issues. When I attempted to run the process to generate mailing labels I received the error message "Run-time error '94' Invalid use of null".

    Here is some of the code in the module that is causing the problem.
    FunctionMaketblPrintLabel(Location)

    Dim MyDb AsDAO.Database
    Dim MySet1As DAO.Recordset 'qryPrintLabel
    Dim MySet2As DAO.Recordset 'tblPrintLabel
    Dim MemoFillAs String



    If IsNull(MySet1![First name]) Then
    MemoFill = MySet1![Last name]
    Else
    MemoFill = MySet1![First name] & "" & MySet1![Last name]
    End If

    When I comment out everything but the statement "MemoFill=MySet1![First Name] & " " & MySet1![Last Name] the process works fine. I know that the definition of null can be tricky, but what's wrong with the code fragment "If IsNull(MySet1![First Name]) Then …." ?

    Is the phrase "IsNull" obsolete ? How about the designation "DAO.Database", is that obsolete?
    Thanks for your help

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    IsNull and DAO.Database aren't obsolete.

    I notice many problems with the code as it's presented but I assume you've edited out some things for us and formatting issues? It's helpful to wrap code in [ code ] [/ code ] tags in your posts (without spaces).

    Is it possible that MySet1![Last name] is null?

    If MySet1![Last name] is null then
    Code:
    MemoFill = MySet1![Last name]
    will throw an error because MemoFill is a string data type and can't be set to Null.
    That said, when you do
    Code:
    MemoFill = MySet1![First name] & " " & MySet1![Last name]

    And both [First name] and [Last name] are null it won't produce an error because you're setting the string equal to " "

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In this code there is no space before As??

    Dim MySet1As DAO.Recordset 'qryPrintLabel
    Dim MySet2As DAO.Recordset 'tblPrintLabel
    Dim MemoFillAs String

    Do you have
    Option Explicit as second line in your module?

    How do you move through the recordset?

    When you post code please use code tags or the octothorpe (#) in the tools area.


  4. #4
    Volunteer is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2018
    Posts
    5
    [QUOTE=orange;407152]In this code there is no space before As??

    Dim MySet1As DAO.Recordset 'qryPrintLabel
    Dim MySet2As DAO.Recordset 'tblPrintLabel
    Dim MemoFillAs String

    Do you have
    Option Explicit as second line in your module?

    How do you move through the recordset?

    When you post code please use code tags or the octothorpe (#) in the tools area.


  5. #5
    Volunteer is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2018
    Posts
    5
    Thanks for your response. Yes, there was a space before the "as"; I don't know why it disappeared when I pasted into the forum. Yes, Option Explicit is the 2nd line.
    The code moves thru the recordset by using the classic "Do Until MySet.EOF", MoveNext, and Loop commands.

    I didn't post in the proper format because I don't know how to do that. I'll check to see if there are any instructions on the website. (Should have done that first, eh?)

    Thanks again

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Basically, you wrap your vba/code within "[co de]" "[/co de]" tags (remove space)
    Code:
    SELECT UserSchema from t_currentschema
    where 
    userInputDate = (select Max(userInputDate) from t_userSchema)
    OR, highlight your code and click on the octothorpe/#/hash tag above the edit window.

  7. #7
    Volunteer is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2018
    Posts
    5
    Got it, thanks

  8. #8
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Did you check to see if MySet1![Last name] being null was the problem?

  9. #9
    HudsonSorlie is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Location
    United States
    Posts
    1

    My impressions

    Cool topic, really interesting

  10. #10
    Volunteer is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2018
    Posts
    5

    Help with null

    Quote Originally Posted by kd2017 View Post
    Did you check to see if MySet1![Last name] being null was the problem?
    Yes, when I substituted a string for MySet1![Last name], the code ran. I understand that a string cannot be made up of nulls, but I don't understand what's happening in my case where there are no records that have a null value for [Last name].

    Thanks everyone for your suggestions.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Provide code for entire procedure as well as sample data. If you want to provide db for analysis follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Robyn_P's Avatar
    Robyn_P is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2017
    Posts
    150
    If you click debug on the error message which line does it highlight?

  13. #13
    Robyn_P's Avatar
    Robyn_P is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2017
    Posts
    150
    I'm thinking that you may need to change If IsNull(MySet1![First name]) Then to If IsNull(MySet1![First name].value) Then

  14. #14
    Parsonshere is offline Novice
    Windows 8 Access 2013
    Join Date
    Aug 2012
    Location
    Rusk
    Posts
    25
    I know you understand strings cannot be made up of null, but first name would be a string and never null. Shouldn't the check be isempty or [if field = "" then...]? I'm not at a computer to experiment.

  15. #15
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As suggested in post7, more of the code might be helpful. The whole thing might be avoided by dim'g as variants instead of strings. Alternatively, more control over data entry is warranted so that neither first or last name can be null or "".

    Empty is only really useful for variants because when initialized, a variant behaves as if it were both 0 and a zero length string, and that is what Empty means. When validating controls, I always check for Null and "" by calling a public function.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Invalid Use of Null
    By Lou_Reed in forum Access
    Replies: 9
    Last Post: 03-30-2017, 07:43 AM
  2. Replies: 3
    Last Post: 11-13-2013, 08:56 AM
  3. Invalid use of null
    By GraeagleBill in forum Programming
    Replies: 3
    Last Post: 03-24-2013, 11:05 PM
  4. Invalid use of Null
    By justauser in forum Forms
    Replies: 2
    Last Post: 11-28-2012, 12:33 PM
  5. Invalid Use of Null!?!
    By Kipster1203 in forum Access
    Replies: 4
    Last Post: 05-13-2010, 06:09 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