Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Location
    Huntsville, Alabama
    Posts
    11

    Question Unanswered: MS Access 2007, Working with Null Values

    ALCON,

    I am generating an application in Access and I am checking for the presence of data within a field on a form. If no data, flag it as an error. In my trusty Access 2007 Bible, for a String variable, the minimum value is 0 (zero) characters. However, if they try to generate a new record, I get a Run-Time '94' error, "Invalid use of Null".

    The line of code that generates the error is as follows:
    dim secitemdesc as string 'this is the variable declaration

    secitemdesc = Me.SECOND_ITEM_SUPPORT_DESC.Value 'error here

    I guess that 0 characters is not the same as a Null value for VBA, correct?

    Is the best way around this to set the table up so new records give this Control (SECOND_ITEM_SUPPORT_DESC) a default value?

    Or is there a different way to deal with null values and variables in VBA?

    Any assistance would be greatly appreciated, thank you.

    DH

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    secitemdesc = Nz(Me.SECOND_ITEM_SUPPORT_DESC.Value, "")
    Have a nice day!

  3. #3
    Join Date
    Aug 2010
    Location
    Huntsville, Alabama
    Posts
    11

    Thumbs up Yo-Ho-Ho & Bottle of Rum!!

    Hey,

    That worked great, thanks very much. Such a simple fix, too bad I am a financial analyst and not a VBA programmer. But I am learning quite a bit and I cannot thank you enough for your help. It'll keep my boss off of my back for a few more days, so that is a good thing.

    Thanks again.

    DH

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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